Interesting ready-made vba excel macros. Effective work in MS Office. Create a Macro in Excel Using Macro Recorder

Next simple examples Excel macros illustrate some of the features and techniques described in the Excel VBA tutorial.

Excel Macro: Example 1

Initially this procedure Sub was given as an example of using comments in VBA code. However, here you can also see how variables are declared, how Excel cell references work, and the use of a loop For, conditional operator If and displaying a message window.

"The Sub procedure searches for a cell containing the specified string "in the range of cells A1:A100 of the active sheet Sub Find_String(sFindText As String) Dim i As Integer "An integer of type Integer, used in a For loop Dim iRowNumber As Integer "An integer of type Integer for storing the result iRowNumber = 0 "Looks through cells A1:A100 one by one until the string is found sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then "If a match to the specified string is found " save the number current line and we leave from For loop iRowNumber = i Exit For End If Next i "We inform the user in a pop-up window whether the required row has been found "If given string found, indicate in which cell the match was found If iRowNumber = 0 Then MsgBox "The row " & sFindText & " was not found" Else MsgBox "The row " & sFindText & " was found in cell A" & iRowNumber End If End Sub

Excel Macro: Example 2

Next procedure Sub– example of using a loop Do While. You can also see how variables are declared, working with Excel cell references, and using a conditional statement. If.

"The Sub procedure outputs Fibonacci numbers not exceeding 1000 Sub Fibonacci() Dim i As Integer "A counter to indicate the position of an element in the sequence Dim iFib As Integer "Stores the current value of the sequence Dim iFib_Next As Integer "Stores the next value of the sequence Dim iStep As Integer "Stores size of the next increment "Initialize the variables i and iFib_Next i = 1 iFib_Next = 0 "The Do While loop will be executed until the value of the "current Fibonacci number exceeds 1000 Do While iFib_Next< 1000 If i = 1 Then "A special case for the first element of the sequence iStep = 1 iFib = 0 Else "Save the size of the next increment before overwriting "the current value of the sequence iStep = iFib iFib = iFib_Next End If "Output the current Fibonacci number in column A of the active worksheet "in the row with index i Cells(i, 1).Value = iFib "Calculate the next Fibonacci number and increase the element's position index by 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Excel Macro: Example 3

This procedure Sub scans the cells of a column A active sheet until it encounters an empty cell. The values ​​are written to an array. This simple Excel macro shows how to work with dynamic arrays and also using a loop Do Until. IN in this example we will not perform any actions with the array, although in real programming practice, after the data is written to the array, such actions are usually performed on them.

"The Sub procedure stores the cell values ​​of column A of the active sheet in the array Sub GetCellValues() Dim iRow As Integer "Stores the number of the current row Dim dCellValues() As Double "An array for storing the cell values ​​iRow = 1 ReDim dCellValues(1 To 10) "Do Loop Until iterates sequentially through the cells of column A of the active sheet "and extracts their values ​​into an array until an empty cell is encountered Do Until IsEmpty(Cells(iRow, 1)) "Check that the dCellValues ​​array is of sufficient size "If not, increase the size array by 10 using ReDim If UBound(dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Excel Macro: Example 4

In this example, the procedure Sub reads values ​​from a column A worksheet Sheet2 and does it with them arithmetic operations. The results are entered into the column cells A on the active worksheet. This macro demonstrates the use of Excel objects. In particular, the appeal is carried out by the procedure Sub to the object Columns, and shows how this object is accessed through the object Worksheet. It is also shown that when accessing a cell or range of cells on the active sheet, it is not necessary to specify the name of this sheet when writing the link.

"The Sub procedure, using a loop, reads the values ​​in column A of the worksheet Sheet2, "performs arithmetic operations with each value and writes the result to "column A of the active worksheet (Sheet1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Assign to variable Col column A of worksheet Sheet 2 Set Col = Sheets("Sheet2").Columns("A") i = 1 "Using a loop, we read the values ​​of the cells of column Col until "until an empty cell is encountered Do Until IsEmpty(Col.Cells(i)) "Perform arithmetic operations on the value of the current cell dVal = Col.Cells(i).Value * 3 - 1" Next command writes the result in column A of the active worksheet "There is no need to indicate the sheet name in the link, since this is the active sheet. Cells(i, 1) = dVal i = i + 1 Loop End Sub

Excel Macro: Example 5

This macro shows an example of VBA code that monitors an Excel event. The event to which the macro is attached occurs each time a cell or range of cells is selected on the worksheet. In our case, when selecting a cell B1, a message window appears on the screen.

"This code shows a message box if cell B1 is selected on the current worksheet. Private Sub Worksheet_SelectionChange(ByVal Target As Range) "Check whether cell B1 is selected If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then "If cell B1 is selected, perform the required action MsgBox "You have selected cell B1" End If End Sub

Excel Macro: Example 6

This procedure illustrates the use of operators On Error And Resume for error handling. This code also shows an example of opening and reading data from a file.

"The Sub procedure assigns the arguments Val1 and Val2 the values ​​of cells A1 and B1" from the workbook Data.xlsx located in the folder C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling " Opening workbook with data Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Assign the variables Val1 and Val2 values ​​from the given workbook Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets( "Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: "If the file is not found, the user will be prompted to place the file they are looking for" in the desired folder and after that continue executing the macro MsgBox "The file Data.xlsx was not found! " & _ "Please add the workbook to the C:\Documents and Settings folder and click OK" Resume End Sub

The manual contains macros on the following topics:
Running a macro to search for a cell.
Run a macro when opening a workbook.
Run a macro when you enter “2” in cell.
Run a macro when you press Enter.
Add your own “Add-Ins” (Cell Format) tab to the panel.
Working with files (i.e. data exchange with TXT, RTF, XLS, etc.).
Checking the presence of a file at the specified path.
Search for the required file.
Automation of file deletion.
Free text in the status bar.
Restoring the status bar.
Creeping line in the status bar.

Quickly change the window title.
Changing the window title (hiding the file name).
Return to original title.
What is open at the moment.
Working with text files.
Write and read text file.
Processing multiple text files.
Determining the end of a line in a text file.
Copying from a text file to Excel.

Copy content to a text file.
Export data to txt.
Export data to html.
Import data that requires more than 256 columns.
Creation backup copies valuable files.
Counting the number of times a file has been opened.
Output the file path to the active cell.
Copying content RTF file in Excel.
Copying data from a closed workbook.
Extracting data from a closed file.
Search for a word in files.
Create a text file and enter text into the file.
Create a text file and enter text (end of file detection).
Creation Word documents based Excel tables.
Commands for creating and deleting directories.
Get the current directory.
Change directory.

View all files in a directory.
View all files in a directory.
Working Microsoft area Excel.
Workbook.
Number of workbook names.
Workbook protection.
Banning the printing of books.
Opening a book (or text files).
Open the book and add text to cell A1.
How many books are open?
Closing all books.
Closing a workbook only when a condition is met.
Saves the workbook with a name that represents the current date.
Is the workbook saved?
Create a workbook with one sheet.
Create a book.
Removing unnecessary names.
Quick reproduction of a workbook.
Sorting sheets.
Finding the maximum value on all sheets of the workbook.
Worksheet.
Checking whether the worksheet is protected.
List of sorted sheets.
Create new leaf.
Create a new sheet.
Removing sheets based on date.
Copying a sheet in a book.
Copying a sheet to new book(created).
Moving a sheet in a book.
Move multiple sheets to a new workbook.
Replace an existing file.
"Flipping" the book.
Insert a header and footer with the name of the workbook, sheet and current date.
Does the sheet exist?
Does the sheet exist?
Displays the number of sheets in the active workbook.
Displays the number of sheets in the active workbook in the form of hyperlinks.
Displays the names of active sheets one by one.
Displays the name and sheet numbers of the current book.
Make the sheet invisible.
How many pages are there on all sheets?
Cell and range (columns and rows).
Copy rows to another sheet.
Copy columns to another sheet.
Counts the number of cells that contain the specified values.
Counts the number of cells in a range that contain the specified values.
Counts the number of visible cells in a range.
Determining the number of cells in a range and the sum of their values.
Counting the number of cells.
Automatic recalculation of table data when its values ​​change.
Entering data into cells.
Entering data using formulas.
Serial data input.
Entering text data into cells.
Displays the book name, sheet and number of sheets in the cells.
Removing empty lines.
Removing empty lines.
Removing empty lines.
Deleting a row by condition.
Removal hidden rows.
Remove used hidden or zero-height rows.
Removing duplicates using a mask.

Selects a range above the current cell.
Select a cell and place a number there.
Highlighting negative values.
Selecting a range and using absolute addresses.

Selecting cells at intervals.
Selecting multiple ranges.
Movement through cells.
Finds the nearest empty cell in a column.
Finding the maximum value.
Search and replace by pattern.
Search for a value and display the result in a separate window.

Search with highlighting of found data.
Search by condition in a range.
Finds the last non-empty cell in a range.
Finds the last non-blank cell in a column.
Finds the last non-blank cell in a row.
Cell search of blue color in the range.
Finding a negative value in a range and highlighting it in blue.
Finding the presence of a value in a column.
Finding matches in a range.
Search for a cell in a range.
Search for a cell in a range.
Finding an approximate value in a range.
Finds the beginning and end of a range containing data.
Finding the beginning of the data.
Automatic replacement of values.
Fast filling of a range (array).
Filling through interval(array).
Fills the specified range (array).
Fill the range(array).
Calculation of the sum of the first values ​​of the range.
Placement in a cell of an electronic watch.
"Alarm".
Design of the top and lower limits range.
Active cell address.
Coordinates of the active cell.
Active cell formula.
Getting a formula from a cell.
Cell data type.
Output the address of the end of the range.
Obtaining information about the selected range.
Take the word with the 13th character in the cell.
Creating a editable list (table).
Check for empty value.
Intersection of cells.
Multiplying the selected range by.
Simultaneously multiply all data in a range.
Divide the range into.
Squaring each cell in the range.
Summarizes data from visible cells only.
The sum of cells with numeric values.
When summing, the cursor is inside the range.

Interest accrual depending on the amount.
Interest accrual depending on the amount.
Summary example of commission calculation.
Movement along the range.
Offset from the selected cell.
Iterate through cells down the column.
Creating a range fill.
Selecting a cell parameter.
Range splitting.
Merge range data.
Merge range data.
Find out the maximum column or row.
Limiting the possible range values.
Testing the speed of reading and writing ranges.
Open MsgBox when a cell is selected.
Hiding a row.
Hiding multiple rows.
Hiding a column.
Hiding multiple columns.
Hiding a row by cell name.
Hiding multiple rows by cell addresses.
Hiding a column by cell name.
Hiding multiple columns by cell addresses.
Cell flashing.
Working with notes.
Displays all the notes in the worksheet.
Comment extraction function.
List of notes for protected sheets.

List of notes in a separate list.
List of notes in a separate list.

Counting the number of comments.
Counting notes.
Select cells with comments.
Display all notes.
Change the color of notes.
Adding notes.
Adding notes to a range based on condition.
Move a comment to a cell and back.

Transferring values ​​from a cell to a comment.
Custom tabs on the ribbon.
Toolbar addition.
Adding a button to the toolbar.
One button panel.
Panel with two buttons.
Creating a panel on the right.

Creation custom menu(option 1).
Creating a custom menu (option 2).
Creating a custom menu (option 3).
Creating a custom menu (option 4).
Creating a custom menu (option 5).
Creating a custom menu (option 6).
Creating a list of Excel main menu items.
Creating a list of context menu items.
Display the toolbar under a certain condition.
Hiding and showing toolbars.
Create a tooltip for my buttons.
Create a menu based on worksheet data.
Creating a context menu.
Blocking the context menu.
Adding a command to the Tools menu.
Adding a command to the View menu.
Creating a list panel.
A cartoon starring an assistant.
Add text, title, button, and icon to the assistant.
New assistant options.
Using the assistant to select a fill color.
DIALOG WINDOWS.
INPUTBOX function (via value input).
Call preview.
Setting up data entry in the dialog box.

Opens the (Open File) dialog box.
Opens the (Print) dialog box.
Other dialog boxes.
Calling the browser from Excel.
Data entry dialog box.
Font settings dialog box.
Default values.
Text formatting. Tables. BORDERS AND FILL.
Displays a list of available fonts.
Selecting all numbers from the text.
Capitalize only at the beginning of the text.
Counting the number of repetitions of the search text.
Selecting an arbitrary element from the text.
Display text backwards.
English text is in capital letters.
Launching a symbol table from Excel.
information about the user, computer, printer, etc.
Get username.
Monitor resolution output.
Get information about the printer you are using.
View information about your computer's drives.
USERFORMS.
DIAGRAMS.
Building a chart using a macro.
Saving the diagram in a separate file.
Build and delete a chart with the click of a button.
Display a list of diagrams in a separate window.
Applying a random color palette.
Chart transparency effect.
Construct a chart based on data from multiple worksheets.
Create captions for chart data.
DIFFERENT PROGRAMS.
Program for composing crossword puzzles.
Create a DVD cover.
Game "Minefield".
Game "Guess the animal."
Calculation based on cells of a certain color.
OTHER FUNCTIONS AND MACROS.
Calling function keys.
Calculation of the arithmetic mean.
Converting numbers into “money”.
Search for the nearest Monday.
Counting the number of complete years.
Calculation of the weighted average.
Converting the month number to its name.
Using relative links.
Converting an Excel table to HTML format.
Random number generator.
Random numbers - based on range.
Applying a function without entering it in a cell.
Counting named objects.
Enabling an autofilter using a macro.
Creating a creeping line.
Creating a running picture.
Rotating autoshapes.
Calling the color table.
Creating a calculator.
Declension of last name, first name and patronymic.
DATE AND TIME.
Output date and time.
Output date and time.
Getting the system date.
Retrieving date and hours.
Function DateFull. The version of MS Office used is not specified.

VBA is considered the standard scripting language for Microsoft applications and is now included in all Office applications and even applications from other companies. Therefore, once you master VBA for Excel, you can immediately move on to creating macros for other software. Microsoft products. Moreover, you will be able to create full-fledged software products, simultaneously using the functions of a variety of applications.

How to enable macros in Excel

By default, the tab responsible for managing and navigating macros in Excel is hidden. To activate this option go to the tab File to the group Options. In the dialog box that appears OptionsExcel go to the tab Ribbon customization, in the right combo box, place a marker opposite the tab Developer. These actions are relevant for Excel versions 2010 and older.

Appear on the tape new inset Developer with Excel automation controls.

Writing macros in Excel

In the tab Developer in Group Code, click the button Record a macro. A dialog box will appear Record a macro, which requests some information about the future code being written. If this is your first time creating a macro, you can simply click the button OK. WITH at this moment Excel will record every user action in a VBA module, be it data entry, formatting, or creating charts. To stop recording a macro, click the button Stop recording which is in the same group Code.

You can also take advantage of an alternative option for recording macros by using the button Record a macro, which is located in the lower left corner of the Excel workbook (to the right of the status Ready).

Now you can view a list of all created macros by clicking on the button Macro, located in the group Code. In the dialog box that appears, you can give more descriptive names to your codes or set keyboard shortcuts that would run a particular macro. Alternative option to launch this window is to press Alt keys+ F8.

Editing Macros

Congratulations! You've written your first macro. It would be logical to check now what code Excel generated for us. The generated code is written in VBA language (Visual Basic for Applications). You have to open it to see it. EditorVB(VBE), which is launched by pressing Alt + F11 or the button VisualBasic on the tab Developer.

To avoid confusion in the editor, you can work with only one tab in a workbook, sheet, or module. This is what the editor looks like in real life.

I offer on at this stage Learn more about the various windows and menus of the VBA editor. This will help you save a lot of time in the future.

To view the code, click on the thread Modules in the projects window and double-click on the branch that appears Module1 . The editor will open a window with the code, as shown in the picture.

Here you can edit the generated code that was written while working in Excel. For example, you need to fill a certain column with values ​​from 1 to 10. You already have the first three steps, which enter the values ​​1, 2 and 3 in the first three cells of column A. We need to complete the remaining seven steps.

If you look at the above code you will see that the macro in a certain way structured. The application first moves the cursor to the cell using the Range("A1").Select command, then edits its contents using ActiveCell.FormulaR1C1 = "1". So for the remaining steps we can repeat these steps, changing the cell address and the value you want to write to that cell. For example, to set cell A4 to 4, you would write:

And repeat similar steps for the remaining values.

Once you're done editing, save your book. You can run the macro by pressing the F5 button, or by returning to the working Excel workbook, go to tab Developer to the group Code -> Macros and select from the list the macro you are interested in.

Take a few minutes to carefully study the code that Excel generated. If you're a beginner, investing a few minutes in learning the code will yield amazing results in getting to know VBA objects later on. Please note that the example we discussed is just an illustration. There are faster and effective ways achieving similar results, which we will discuss later.

Increase the speed of Excel macro execution

So far so good. Let's look at a couple of tricks that will help speed up macro execution. Let's take the code snippet above as an example. Modern computers will work on the code in question so quickly that you won’t even notice it. But what if you need to perform the operation 50,000 times. This will take some time. If the macro you write is hundreds of lines long, you can speed up code execution by trimming the part of the processes that is not used during macro execution.

Using the Application.ScreenUpdating Command

The first trick is to avoid updating the screen while the macro is running. This will allow Excel to save computing power computer and update the screen with the latest values ​​only after all the code has been executed. To do this, you need to add a command to disable screen refresh at the beginning of the macro and a command to enable screen refresh at the end of the macro.

The Application.ScreenUpdating command tells Excel to stop displaying the recalculated data on the screen and return the finished values ​​at the end of the code execution.

Using the Application command. Calculation

The second trick is to disable automatic calculations. Let me explain. Every time a user or process updates a cell, Excel tries to recalculate all the cells that depend on it. So let's say if the cell that the macro is trying to update affects 10,000 other cells, Excel will try to recalculate them all before the code finishes executing. Accordingly, if there is whole line influencing cells, recalculation can significantly slow down code execution. To prevent this from happening, you can install the Application command. Calculation at the beginning of the code, which will switch the recalculation of formulas to manual mode and then return the automatic calculation at the end of the macro.

Be careful, do not forget to switch this option again to auto mode at the end of the macro. Otherwise, you will need to do this in Excel itself by clicking on the tab Formulas to the group Calculation and choose Calculation options –> Automatic.

Avoiding selecting cells and ranges

In mode automatic recording macros, you may notice that Excel very often uses the cell selection command, for example, Range("A1").Select. In our example, we used this command repeatedly to select a cell and change its value. You can avoid this by simply specifying the cell address and giving it the required value (The macro recorded the cursor movement from one cell to another, hence inserting these steps. However, they are not necessary). So, a more efficient code would look like this.

IN in this case we simply referenced the cell and gave it the required value without selecting it at all. This method is faster than the previous one.

Excel Macro Examples

Below are some sample VBA codes that will help you automate the most common tasks.

If you are not strong in programming, but at the same time, you need to implement somea task that goes beyond standard features and MS Excel commands, you can search for a solution on the Internet. The solution will most likely come in the form of VBA code that you need to copy and paste into your workbook, then somehow make this code work for you, in this article I will tell you how to do this.

Let's look at two examples:

1. Find and use the command

For example, we need a command that would insert the number “1” into the selected cells. We launch the search engine, type the search phrase, get the results, start browsing, find code something like this:

Sub Insertion1() Dim q As Object On Error Resume Next Set q = Cells For Each q In Selection q = 1 Next q End Sub

We highlight this code(without line numbering, starting with the word Sub) and press Ctrl+C. Go to your MS Excel workbook and press the keyboard shortcut Alt+F11, a VBA editor window will open:

In the left window "Project - VBA Project" we select (click with the mouse) our workbook into which we need to insert the macro, for example, "VBAProject (Book2)":

In the "Insert" menu item, select "Module":

In the left window "Project - VBA Project" you should see new folder"Modules" and in it a new object "Module1":

Move the cursor to the right “Large” input field and click Ctrl+V, the copied macro is pasted into the module:

Macros in MS Excel can be inserted in the following places:

• In the Module, they usually insert the code of macros, which will be launched when the user presses a button (as, for example, in our case) or the code of functions (formulas);
• In the Worksheet, the code of macros is usually inserted, the launch of which should occur automatically depending on user actions or changes in data in the sheet (the data has changed, the macro is executed);
• Macro code is usually inserted into the Workbook, which should be launched automatically depending on the actions performed on the workbook (file). For example, a macro that runs when a workbook is opened or closed, or when it is saved;
• Macros can also be part of a user form.

Typically, the person who posts the code specifies where it should be inserted, into a module, worksheet, or workbook.

To insert code into a Worksheet, select the appropriate sheet in the left window of the VBA editor, double-click on it with the left mouse button, move the cursor to the right input field and paste the code.

To insert a code into a Book, select "ThisBook":

Let's practice. Paste the code posted below into "Sheet1".

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo a If Target = 2 Then VBA.MsgBox ("Cell " & Target.Address & " = 2") End If a: Exit Sub End Sub

This macro displays an information message if you enter “2” in any sheet cell.

Return to the workbook, go to “Sheet1” and enter the number “2” in cell “A1” and click Enter, after which you should see the following message:

If you see this message, then you did everything right. If not, then you inserted the code somewhere in the wrong place, try again.

When pasting code, you need to be careful where you paste it. This can be done by looking at what is written in the title of the VBA editor window:

2. Find and use the function

We find on the Internet the VBA code of a function that counts, for example, the number of words in a cell:

Public Function Number Of WordsIn Cell(Cell As Range) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Cell.Value), " ") Number Of WordsIn Cell = UBound(q) + 1 End Function

Copy the code and press the key combination Alt+F11, the VBA editor will open:

Add new module Paste the copied code into your book and into this module:

Close the VBA editor and go to your workbook. Create a new sheet (optional) in a cell A1 enter the text “mom washed the frame.” We go to the cell in which we want to get the result (number of words), in the “Formulas” menu, click the “Insert function” button:

In the "Insert Function" window that opens, in the "Category" field, select "User Defined"

On the list available functions select "Number of Words in Cell", click "OK":

Enter the required arguments and click "OK":

We get the result:

Important:

If you do not save the workbook in which you inserted the macro as a “Macro-enabled workbook,” all modules with macros will be deleted and you will have to do all this work again.

If, when inserting a macro into a module, you see red signs instead of some text " ???????? "

This means that you have an encoding problem, which occurs with copying Cyrillic text from some browsers. To overcome this problem, try pasting the copied code into blank sheet MS Excel as "Text in Unicode encoding". To do this, go to the MS Excel workbook, select or create a blank sheet, go to cell "A1" and press the keyboard shortcuts Ctrl+Alt+V. A menu should appear " Special insert", select "Unicode text" and click "OK".

The code should be pasted into the worksheet without the question marks:

After that, copy the code pasted into the sheet again and paste it into the module.

If you do not see the "Project - VBA Project" window in the VBA editor, go to the "View" menu tab and select "Project Explorer" from the list or press the keyboard shortcut Ctrl+R:

Excel tables are one of the most popular ways to store and organize information. If implemented in Excel macros, then the program’s capabilities can be expanded almost twice. For example, macros help automate routine processes and speed up monotonous work. They are often used by specialists who translate reports from 1C into Excel. With the help of embedded code, they can easily be brought to a single standard template.

What are Excel macros?

Macros are used not only in tables, but in everything Microsoft Office. They are code created using Visual Basic for Applications. If you are partially familiar with web programming, then you can draw an analogy with Javascript. Excel macros do roughly the same thing that Javascript does with HTML data.

A small part of what a macro can do in office applications:

Align the document according to style and format it;
. carry out operations with numerical and text data;
. seek help external files(text, databases and others);
. create new documents from scratch;
. combine several actions and perform them automatically.

To work with a table, you can create it yourself or download it ready-made option from the Internet. Macros should only be downloaded from trusted sites, otherwise you can introduce a virus into Excel.

How can macros in Excel be dangerous?

Damage files on your PC;
. steal confidential data.

The virus is introduced during the operation of one of the office applications- Word or Excel. After you stop working, they will start autonomous operation virus and infecting the entire system.

Another way a malicious macro works is as an intermediary to introduce threats into the system. In this case, it is a kind of gateway for the introduction and generation of Trojan software. It will no longer be controlled by a modified macro, but by the operating system, and therefore the threat becomes larger. It is not always possible to cure such viruses; sometimes you have to completely change the Windows OS or HDD PC (depending on the tasks that the hacker set when writing the code).

You can choose any method of protection against such macros. The most popular option is to increase the security level of Excel, but in this case the program may no longer support your copyright, useful macros. You should not trust files from unknown sources, as they may become infected. Even a macro received from your colleague’s PC can be dangerous. If a friend gave you a file to work with that supports the use of macros, then before allowing the system to use it, clarify how he received this macro.

The best option is to independently learn the art of creating macros for your needs. In this case, you can fully vouch for their quality and safety.

How to Record Macros in Excel

The easiest way to create a macro is to program directly in Excel. To do this, you will need a table with the entered data and a little knowledge to enable recording of such code in the program.

Creation begins with the View menu. In it you need to select the “Macros” option and the “Record Macro” command. Next, you need to specify a name without spaces, for example, “Format” (if the code will be responsible for changing the format of text or cells).

After which all your actions will be automatically recorded and converted into macro code. A button with a small square (“Stop”) will appear at the bottom of the document, by clicking on which you can stop recording. This way you can automatically record macros in Excel, examples of which you can analyze further in the learning process.

How to enable macros in Excel

In order to enable a recorded macro in other documents, you must save it after clearing the table. The document format must be set as XLTM (it is the one that supports macros).

When you next access your document to enable macros in Excel, you must first enable them in the pop-up window. top line. Then you need to import the data that will be edited by the macro from a regular CSV table. Once the import is complete, you need to go back to the “View” menu, select “Macros” there and find the name of the code you made in the list. Finally, click the “Run” button. After which you will see how the program, according to the algorithm you previously specified, performs calculations and formats the document.

How can I see the macro code?

All macros created in Excel are written automatically, without human intervention. The program composes itself program code, which can be opened from the table by calling using the menu.

To do this, you need to run the commands sequentially:

Expand the “View” tab;
. open "Macros";
. select the name of the required macro;
. Call up a window with its code using the “Change” button.

In the new dialog box you can edit and change the macro. You will see there the code that the program recorded automatically, and you can manually add any action. After saving the information, the macro will work differently when importing new data.

How to start a macro by clicking on a cell

Let's say you want a macro to run when cell A1 is selected. To select in Excel cell running the macro, you need to add the program code to the first module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then: Call module1
End Sub

The best way to learn macros is in Excel tutorial. There is more than one similar example, and for those who are just getting acquainted with all the possibilities of the table, this is The best way learn to fully utilize its functionality.

Where can you learn to work with macros?

There are several ways to learn how to create more complex macros. They must be distinguished by logic and a clearly defined sequence of actions. Such macros can only be created by writing VBA code manually. To create complex macros you will need to spend some time learning the intricacies of the language Visual programming Basik.

Now to explore everyone Excel capabilities There are many special courses that last for several weeks, but to attend them you will need to travel to a training center or attend webinars (which is not always convenient due to the time difference). But a more effective and simpler way to learn how to create macros in Excel is a tutorial with examples. You can study it at any time convenient for you, and step-by-step textbooks will help you develop different variants: from the most simple codes to the most complex. Microsoft Excel is a universal spreadsheet editor that can be used to solve many complex problems.

Examples

There are codes that allow you to achieve automation various tasks. So, as an example, let's look at the row insertion macro. It looks like this:

Sub Macro1()