Ready macros. Mironov

Good day!

I want to devote this article to such a huge section of MS Excel as macros, or rather, let's start over and look at how to create a macro in Excel, what it is for and how to use it in your work.

As you know from own experience, when working with a macro, there is a lot of "routine", that is, the same operations and actions are performed that are needed to obtain a result, it can be filling out the same type of tables or forms, processing data, similar to twins, weekly, monthly reports, and etc. But the use of macros will allow you to perform these actions in automatic mode, using the capabilities of Excel to its fullest, throwing these routine and monotonous operations onto the powerful shoulders of Excel. Also, the reason for using macros can be adding the right opportunities which have not yet been implemented in standard Excel functions(for example, output, data collection on one sheet, etc.).

If you have never heard of a macro, then its most accurate definition would be as follows, these are actions that are programmed in a specific sequence and recorded in the programming environment on Visual language Basic for Applications (VBA)... A macro can be run multiple times and this will force Excel to perform any sequence of actions we need, which we simply don’t like or don’t want to do manually. Despite the great variety of programming languages ​​for the entire complex Microsoft Office the standard is exactly VBA and it works in any application of the office suite.

So, creating a macro in Excel is possible in 2 ways:

Create a macro in Excelusing a macro recorder

First, let's clarify what a macro recorder is and what a macro has to do with it.

Macro recorder Is a small program embedded in Excel that interprets any user action in language codes VBA programming and writes the commands that were received during the work to the program module. That is, if, with the macro recorder turned on, we create the daily report we need, then the macro recorder will record everything in its commands step by step and, as a result, create a macro that will create a daily report automatically.

This method is very useful for those who do not have the skills and knowledge of working in the VBA language environment. But this ease of execution and recording of a macro has its drawbacks, as well as its advantages:

• The macro recorder can only record what it can touch, which means that it can record actions only when buttons, icons, menu commands and everything in this spirit are used, such options as are not available for it;
• In the event that an error was made during the recording period, it will also be recorded. But you can use the cancel button last action, erase the last command that you wrote incorrectly in VBA;
• Recording in the macro recorder is carried out only within the boundaries of the MS Excel window, and in the case when you close the program or turn on another, the recording will be stopped and stop running.

To enable the macro recorder for recording, do the following:

The next step in working with the macro recorder will be setting up its parameters for further recording of the macro, this can be done in the window "Recording a Macro", where:

• Macro name field- you can write a name that you understand in any language, but must begin with a letter and not contain punctuation marks and spaces;
• keyboard shortcut box- will be used by you, in the future, for quick start your macro. In the event that you need to register a new one, this option will be available in the menu "Service" - "Macro" - "Macros" - "Run" or on the tab "Developer" pressing the button "Macros";
• field "Save to ..."- you can specify the place where the text of the macro will be saved (but not sent), and these are 3 options:
  • "This book"- the macro will be written to the module of the current book and can only be executed if this Excel workbook will be open;
  • "A new book"- the macro will be saved to the template on the basis of which an empty one is created in Excel A new book, which means that the macro will become available in all books that will be created on this computer from that moment;
  • "Personal Macro Book"- is a special Excel macro book called "Personal.xls" and is used as a special storage-library of macros. At startup, macros from the book "Personal.xls" are loaded into memory and can be run in any book at any time.
• Description field- here you can describe what and how the macro should do, why it was created and what functions it has, this is a purely informative field, which is called for memory.

After you have run and recorded your macro by doing all necessary actions, recording can be stopped with the command Stop Recording and your macro will be created using the macro recorder.

Create a macro in Excelin the Visual Basic Editor.

In this method, we will look at how to create a macro in Excel through the VBA program editor, which, as I said above, is built into any version of Excel.

The VBA program editor is launched differently, depending on the version of your Excel program:

• in versions of Excel 2003 and later, we need in the menu "Service", select item "Macro" and press "Editor Visual basic» ;
• in versions of Excel 2007 and newer, we need in the tab "Developer" push the button "Visual Basic Editor"... In the case when you do not find this tab, you need to activate it by selecting the menu item "File" - "Options" - "Customize the Ribbon" and in the dialog box, activate the tab "Developer".

In the window that opens, you can contemplate the interface of the VBA editor, everything here is written in English and there are no localizers, you don't have to search, but just accept and work, especially since this is only incomprehensible at first, and then everything will be familiar.

So, how can I work in the VBA editor, where is what is stored and how to create a macro in Excel. Questions of this nature arise as soon as you see the editor and now we will consider all of them.

All our macros will be stored in so called software modules... In any of the books, any Excel versions we can create any software modules in any quantity and place in them all the macros we have created. One module is able to contain any number of macros you need or created. Modules are available in the window "Project Explorer" and are located in the upper left corner of the macro editor (you can also call it with the keyboard shortcut CTRL + R).

Program modules in the VBA editor exist in several forms and are used to different options and situations:

Actually, the macro itself when working in a standard module looks like this:

Let's consider an example of a working macro:

• All macros will necessarily begin with an operator Sub followed by the name of your macro and a parenthesized list of arguments. In cases where arguments are missing, parentheses should be left blank;
• Without fail, all macros end with the operator End Sub;
• Data that is between operators Sub and End Sub, is the body of the macro that will run when the macro is run. In the example, the macro checks and, when entering data, finds it in the list of the database and displays the value specified by the criteria.

As you can see, the second method is more difficult to use and understand if you have no experience in programming in general or in particular in VBA. It is very difficult to understand and understand what commands and how are entered, what arguments it uses in order for the macro to start performing its work in automatic mode. But the road will be mastered by the one walking, as the ancient sages said, and therefore you should not give up, but follow the commandments of Lenin's grandfather ...

Creating a button to run macros in the toolbar

As I said earlier, you can call a macro procedure hot combination keys, but it is very tedious to remember which combination is assigned to whom, so the best thing is to create a button to run the macro. Create buttons, possibly of several types, namely:

This method is available for any version of MS Excel and it consists in the fact that we will bring the button directly to our worksheet as a graphic object. To do this, you need:

• In MS Excel 2003 and older go to the menu "View", choose "Toolbar" and press the button "Forms".
• In MS Excel 2007 and newer you need in the tab "Developer" open dropdown menu "Insert" and select an object "Button".

After all this, you must draw a button on your sheet while holding down the left mouse button. After the end of the drawing process, a window will automatically turn on, where you will need to select the macro that must be executed when you click on your button.

How to create custom functions in VBA

In principle, the creation of so-called user-defined functions is not very different from the creation of a regular macro in a standard program module. The difference between these concepts is that the macro will perform the actions inherent in it with the objects of the book or sheet (these are formulas, cells, etc.), but the custom function works only with the values ​​that it receives from us and are the arguments. initial data for calculations.

For example, to create a custom function for value added tax, aka VAT, we need to open our VBA editor and add new module, select from the menu "Insert" paragraph "Module" and enter the text for our function there: It should be noted that the main difference between a function and a macro is the header Function replaced Sub and there is a filled list of arguments, in our example it is Summa... After our code is entered, it will be available in standard window Function wizards found in the menu "Formulas", paragraph "Insert function".
And choose a category "User-defined" in which our written function will be displayed "NDS".
After selecting our function, you can place the cursor on a cell with an argument, which will contain the amount for which we calculate VAT, everything happens, as with normal function.
And that's all for me! I really hope that an article on how to create a macro in Excel was understandable and useful to you. I would be very grateful for the comments left, as this is an indicator of readability and inspires to write new articles! Share what you read with your friends and like it!

I started building this site like notebook on convenient opportunities Excel. At any time, you can brush up on your knowledge of a particular feature of the program by going to the site, plus you can send a link to the article to friends or colleagues who ask for help using Excel. The audience of the site has grown quite quickly, and as I see it, the recorded information is useful not only to me and a few other people, but also to many people I don't know and is highly rated by search engines. If by Excel capabilities, I am confident in myself, then a specialist is very high level I can't name myself in VBA. Therefore, I would like to create great article helper where I will write useful macros that I use frequently. I am sure such a collection of VBA features will be useful not only for me.

The site already has several articles on VBA, including, but I think it will be useful to keep code gadgets that are often used in one place.

Turn off the display of macro actions on the screen with a simple function. This will not only improve the visual performance, but also significantly speed up the execution of the macro.

Sub Makros1 () Application.ScreenUpdating = 0 "Your code Application.ScreenUpdating = 1 End Sub

Don't forget to include the function at the end of the macro

How to remove copy selection after macro execution?

If you are all such in your macro, allocate a range for copying and pasting. Do not forget to turn off selection at the end of the macro

Sub Makros1 () "Your code Application.CutCopyMode = 0 End Sub

Useful macros. How to find the last row or column of a range

This construction will help you easily find the number of the last row or column of the filled range. It is especially convenient to use in loops, you do not need to specify 1000 loop lines with a margin, Excel will find itself where the end of the range is using the following construction:

Sub makros1 () Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells (Rows.Count, 1) .End (xlUp) .Row "Finds the last row number of the filled range nLastCol = Cells (1, Columns.Count) .End (xlToLeft ) .Column "Finds the number of the last populated column in the range End Sub

Moreover, I immediately declare the variable as Long (length 2 147 483 647), so as not to get into a situation where the popular Integer may not be enough (32 767) for large tables.

You should also pay attention that the search takes place on the first column or the first row, select desired number, for the correct definition.

For Loop and Checking a Condition in a Loop

If you find last issues row and column ranges, you can use them in loops. For example, you need to run through the first column and count the number of empty cells.

Sub makros1 () Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Variable amount Dim i As Long" Cycle variable mLastRow = Cells (Rows.Count, 1) .End (xlUp) .Row Kol = 0 For i = 1 To mLastRow If Cells (i, 1) .Value = "" Then Kol = Kol + 1 End If Next i MsgBox Kol Application.ScreenUpdating = 1 End Sub

Here Msgbox is also used with this feature, you can display data in a separate window. For my example, it will look like this:

Counting Macro Execution Time

Sub makros1 () TimeStart = Now TimeFinish = Now MsgBox "Time:" & Format (TimeFinish - TimeStart, "h: mm: ss") End Sub

MsgBox produces this output:

It is very convenient to track the execution time of macros for subsequent optimization.

These are some useful macros that I use very often. I will gradually expand this list. All effective automation!

Share our article on your social networks:

The following simple Excel macro examples 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 references to Excel cells, using the loop For, conditional operator If and displaying a message box.

"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 after the other until a string is found sFindText For i = 1 To 100 If Cells (i, 1) .Value = sFindText Then" If a match with the given string is found " save the number current line and exit from For loop iRowNumber = i Exit For End If Next i "We inform the user in a pop-up window if the search string was found" If the specified string is found, indicate in which cell the match was found If iRowNumber = 0 Then MsgBox "The string" & sFindText & "was not found" Else MsgBox " String "& sFindText &" found in cell A "& iRowNumber End If End Sub

Excel Macro: Example 2

Next procedure Sub- an example of using a loop Do while... Here you can also see how variables are declared, how to work with Excel cell references, and how to apply the conditional operator. If.

"Sub procedure outputs Fibonacci numbers not exceeding 1000 Sub Fibonacci () Dim i As Integer" Counter for indicating 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 run 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 "Print 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 position index by 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Excel Macro: Example 3

This procedure Sub loops through 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 demonstrates working with dynamic arrays as well as using a loop Do until... V 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 values ​​of the cells in 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 "Array for storing the values ​​of the cells iRow = 1 ReDim dCellValues ​​(1 To 10)" Loop Do Until loops sequentially through the cells of column A of the active sheet "and extracts their values ​​into an array until it encounters empty cell Do Until IsEmpty (Cells (iRow, 1)) "Check that the dCellValues ​​array is large enough" If not, increase the array size 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 performs with them arithmetic operations... The results are recorded in the cells of the column A on the active worksheet. This macro demonstrates the use of Excel objects. In particular, an appeal is made 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 referring to a cell or a range of cells on the active sheet, it is not necessary to specify the name of this sheet when writing the link.

"Sub procedure using a loop reads the values ​​in column A of 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 Column A of the worksheet to the variable Col. Sheet 2 Set Col = Sheets (" Sheet2 "). Columns (" A ") i = 1" Using a loop, read the values ​​of the cells Col column 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 "The following command writes the result to Column A of the active worksheet "You do not need to specify 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 example VBA code that monitors an Excel event. The event the macro is bound to occurs every time a cell or range of cells is selected in the worksheet. In our case, when selecting a cell B1, a message window is displayed 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 if 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 the operators On Error and Resume to handle errors. This code also shows an example of opening and reading data from a file.

"The Sub procedure sets the Val1 and Val2 arguments to the values ​​of cells A1 and B1" from the Data.xlsx workbook located in the C: \ Documents and Settings folder Sub Set_Values ​​(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling " We open workbook with data Set DataWorkbook = Workbooks.Open ("C: \ Documents and Settings \ Data") "We assign values ​​from the specified workbook to the Val1 and Val2 variables 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 put the searched file" in desired folder and then continue executing the MsgBox macro "Data.xlsx file not found!" & _ "Please add the workbook to the C: \ Documents and Settings folder and click OK" Resume End Sub

Excel has the most powerful, but at the same time very rarely used, ability to create automatic sequences of actions using macros. A macro is an ideal solution if you are dealing with a task of the same type that is repeated many times. For example, processing data or formatting documents according to a standardized template. At the same time, you do not need knowledge of programming languages.

Are you already curious about what a macro is and how it works? Then boldly go ahead - then, step by step, we will go through the whole process of creating a macro together with you.

What is Macro?

Macro in Microsoft Office (yes, this functionality works the same in many applications Microsoft package Office) is program code in the programming language (VBA), stored inside the document. To make it clearer, a Microsoft Office document can be compared to HTML page, then the macro is analogous to Javascript. What Javascript Can Do With Data in HTML format located on a web page is very similar to what a macro can do with data in Microsoft document Office.

Macros are capable of performing almost any action you can wish on a document. Here are some of them (a very small part):

• Apply styles and formatting.
• Perform various operations with numeric and text data.
• Use external data sources (database files, text documents etc.)
• Create a new document.
• Perform all of the above actions in any combination.

Creating a Macro - A Practical Example

For example, let's take the most common file CSV... it simple table 10x20, filled with numbers from 0 to 100 with headings for columns and rows. Our task is to turn this dataset into a presentable formatted table and generate totals for each row.

As already mentioned, a macro is code written in the VBA programming language. But in Excel, you can create a program without writing a single line of code, which we will do right now.

To create a macro, open View(View)> Macros(Macros)> Record Macro(Recording a macro ...)

Give your macro a name (no spaces) and press OK.

From this moment on, ALL your actions with the document are recorded: cell changes, table scrolling, even window resizing.

Excel is signaling that macro recording is enabled in two places. First, on the menu Macros(Macros) - instead of a string Record Macro(Macro recording ...) line appeared Stop recording(Stop recording).

Second, in the lower left corner Excel windows... Icon Stop(small square) indicates that the macro recording mode is on. Clicking on it will stop recording. Conversely, when the recording mode is not enabled, there is an icon in this place to enable macro recording. Clicking on it will give the same result as enabling recording through the menu.

Now that the macro recording mode is on, let's get down to our task. First, let's add headers for the summary data.

• = SUM (B2: K2) or = SUM (B2: K2)
• = AVERAGE (B2: K2) or = AVERAGE (B2: K2)
• = MIN (B2: K2) or = MIN (B2: K2)
• = MAX (B2: K2) or = MAX (B2: K2)
• = MEDIAN (B2: K2) or = MEDIAN (B2: K2)

Now select the cells with the formulas and copy them to all rows of our table by pulling the autocomplete handle.

After completing this step, the corresponding totals should appear on each row.

Respectively:

• = SUM (L2: L21) or = SUM (L2: L21)
• = AVERAGE (B2: K21) or = AVERAGE (B2: K21)- to calculate this value, it is necessary to take exactly the initial data of the table. If you take the average of the individual row averages, the result will be different.
• = MIN (N2: N21) or = MIN (N2: N21)
• = MAX (O2: O21) or = MAX (O2: O21)
• = MEDIAN (B2: K21) or = MEDIAN (B2: K21)- we calculate using the initial data of the table, for the reason indicated above.

Now that we're done with the calculations, let's start formatting. To begin with, set the same data display format for all cells. Select all cells on the sheet, for this use the keyboard shortcut Ctrl + A, or click on the icon Select all which is at the intersection of the row and column headings. Then press Comma style(Delimited Format) tab Home(Home).

• Bold typeface.
• Center alignment.
• Fill with color.

Finally, we will customize the format of the totals.

This is how it should look in the end:

If you are satisfied with everything, stop recording the macro.

Congratulations! You just recorded your first Excel macro yourself.

To use the created macro, we need to save Excel document in a format that supports macros. First, you need to delete all data from the table we have created, i.e. make of her blank template... The fact is that in the future, working with this template, we will import the latest and most relevant data into it.

To clear all cells of data, click right click mouse on icon Select all which is at the intersection of the row and column headings, and from context menu select item Delete(Delete).

Now our sheet is completely cleared of all data, while the macro remains recorded. We need to save the book like Excel template with support for macros, which has the extension XLTM.

An important point! If you save the file with the extension XLTX, then the macro will not work in it. By the way, you can save the workbook as an Excel 97-2003 template, which has the format XLT, it also supports macros.

When the template is saved, you can safely close Excel.

Executing a macro in Excel

Before revealing all the possibilities of the macro you created, I think it is right to pay attention to a couple important points concerning macros in general:

• Macros can be harmful.
• Read the previous point again.

VBA code is very great opportunities... In particular, it can perform operations on files outside of current document... For example, a macro can delete or modify any files in the folder My documents... For this reason, only run and allow macros from sources you trust.

To run our macro formatting data, open the template file we created in the first part of this tutorial. If you have standard settings security, then when you open the file above the table, a warning will appear that macros are disabled, and a button to enable their execution. Since we made the template on our own and we trust ourselves, we press the button Enable Content(Include content).

The next step is to import the latest updated dataset from the file CSV(we created our macro based on such a file).

When importing data from a CSV file, Excel may ask you to configure some parameters for the correct transfer of data to the table.

When the import is finished, go to the menu Macros(Macros) tab View(View) and select the command View macros(Macros).

In the dialog box that opens, we will see a line with the name of our macro FormatData... Select it and click Run(Run).

When the macro starts to work, you will see how the table cursor jumps from cell to cell. After a few seconds, the same operations will be performed with the data as when recording the macro. When everything is ready, the table should look the same as the original, which we formatted manually, only with different data in the cells.

Take a look under the hood: How does a macro work?

As already mentioned more than once, a macro is a program code in a programming language Visual Basic for Applications(VBA). When you turn on the macro recording mode, Excel actually records every action you take in the form of instructions in the VBA language. In a simple way, Excel writes the program code for you.

To see this program code, you need in the menu Macros(Macros) tab View(View) click View macros(Macros) and in the dialog box that opens, click Edit(Change).

A window will open Visual Basic for Applications, in which we will see the program code of the macro we recorded. Yes, you understood correctly, here you can change this code and even create a new macro. The actions that we performed with the table in this lesson can be written down using automatic recording a macro in Excel. But more complex macros, with a finely tuned sequence and logic of actions, require manual programming.

Let's add one more step to our task ...

Imagine that our original file with data data.csv is created automatically by some process and is always saved on disk in the same place. For example, C: \ Data \ data.csv- path to the file with updated data. The process of opening this file and importing data from it can also be recorded in a macro:

1. Open the template file where we saved the macro - FormatData.
2. Create a new macro named LoadData.
3. While recording a macro LoadData import data from file data.csv- as we did in the previous part of the lesson.
4. When the import is complete, stop recording the macro.
5. Delete all data from cells.
6. Save the file as an Excel Macro-Enabled Template (XLTM extension).

Thus, by running this template, you get access to two macros - one loads data, the other formats them.

If you want to start programming, you can combine the actions of these two macros into one - simply by copying the code from LoadData to the beginning of the code FormatData.

To automate repetitive tasks, you can record a macro using the Macro Recorder in Microsoft Excel... Imagine you have dates in random formats and you want to apply one format to all. This can be done using a macro. You can record a macro by applying desired format, and play the macro if necessary.

When recording a macro, all necessary actions are written as Visual Basic for Applications (VBA) code. Such actions can include entering text or numbers, selecting cells or commands on the ribbon or menus, formatting cells, rows or columns, and even importing data from an external source such as Microsoft Access... Visual Basic for Applications (VBA) is part of the language Visual programming Basic. It is available in most Office applications. Although VBA allows you to automate processes in and between Office applications, you don't need to be proficient in programming or know the VBA language because the macro recorder will take care of everything you need to do.

It is important to know that when you record a macro, almost everything you do is logged. Therefore, if you make a mistake, such as clicking the wrong button, the macro recorder will log that action. In such a case, you can rewrite the entire sequence or change the VBA code. Therefore, before recording the process, you should work it out well. The more accurately you record the sequence, the more efficiently the macro will work.

Developer which is hidden by default, so you need to enable it first. For more information, see the article Displaying the Developer Tab.

Macro recording

In the tab Developer click Macros to view the macros associated with the workbook. Alternatively, you can press the keys ALT + F8... This will open a dialog box Macro.

Attention:

		Learn about the security parameters of macros and their meaning.
	Macros can be run different ways, for example using a keyboard shortcut, graphic object, panels quick access, buttons or even when you open a book.
	Using the Visual Basic Editor, you can edit the macros attached to the workbook.
	assign a macro. In field Assign a macro
	Learn how to turn macros on and off in Office files.
	Press the keys ALT + F11.

Working with recorded code in the Visual Basic Editor (VBE)

With the Visual Basic Editor (VBE), you can add your own variables, control structures, and other elements to the recorded code that the macro recorder does not support. Because the macro recorder captures nearly every step that occurs during recording, you might also need to remove unnecessary code. View recorded code - great way learn to program in VBA or hone your skills.

For an example of modifying the recorded code, see Get started with VBA in Excel.

Macro recording

It is useful to know the following before recording macros:

A macro recorded to work with Excel range, will only be executed for cells in this range. So if you add to the range new line, the macro will not be applied to it.

If you need to record a long sequence of tasks, we recommend using several smaller macros instead.

The macro can also contain tasks that are not related to Excel. The macro process can include other Office applications and other programs that support Visual Basic for Applications (VBA). For example, you can record a macro that first updates a table in Excel and then opens Outlook to send it by email.

VBA macros and tools are located in the tab Developer which is hidden by default, so you need to enable it first.

Please select Excel > Options> Ribbon and Panel.

To record a macro, follow the instructions below.

Working with macros recorded in Excel

In the tab Developer click Macros to view the macros associated with the workbook. This will open a dialog box Macro.

Note: Macros cannot be canceled. Before running the recorded macro for the first time, save or create a copy of the workbook to prevent unwanted changes... If you are not satisfied with the results of the macro execution, you can close the workbook without saving it.

Below are the additional information about working with macros in Excel.

		Learn how to turn macros on and off in Excel for Mac.
	To save time on frequently repetitive tasks, you can record the corresponding sequence of actions in the form of a macro. Learn how to create and run macros.
	If the workbook contains a VBA macro that you want to use elsewhere, you can copy the module to another workbook using Microsoft editor Visual Basic.
Assigning a macro to an object, shape, or graphic element	On the sheet, right-click the object, image, shape, or element to which you want to assign an existing macro, and then click assign a macro. In field Assign a macro select the macro you want to assign.
	You can assign a macro to an icon and add it to the Quick Access Toolbar or the Ribbon.
	You can assign macros to forms and ActiveX controls on the sheet.
Opening the Visual Basic Editor	In the tab Developer click Visual basic or select Service > Macro > Visual Basic Editor.
	Learn how to find help for Visual Basic items.

additional information

You can always ask the Excel Tech Community a question, ask for help in the Answers community, and also suggest new function or improvement on the website