How to write a macro in Excel. How to write a macro in Excel using the VBA programming language

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 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 current row number and exit the loop For iRowNumber = i Exit For End If Next i "Inform the user in a pop-up window whether the required row has been found "If the specified row is found, indicate in which cell the match is found If iRowNumber = 0 Then MsgBox "Row " & sFindText & " not found" Else MsgBox "Row " & sFindText & " 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 "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 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 working with dynamic arrays and also using a loop Do Until. 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 performs arithmetic operations on them. 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 "The following command writes the result to column A of the active worksheet "Indicate the sheet name in the link not necessary since this is the active leaf 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 " Open the workbook with the 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 searched file "in the desired folder and then continue executing the MsgBox macro "The Data.xlsx file is not found! " & _ "Please add the workbook to the C:\Documents and Settings folder and click OK" Resume End Sub

Let's look at ways to create macros in Excel. First of all, you need to check the security settings to ensure that macros are enabled, otherwise nothing will work. Go to the main menu " Service-Macro-Security »

Set the level checkbox to low (when running workbooks with macros, Excel will not ask questions about blocking) or medium (a warning will be issued). For training purposes, you can set the security to low. Restart Excel.

There are two ways to create a macro in Excel:

Record using the corresponding menu item
Create manually

The first method is easy and does not require any programming knowledge. Just select in the main menu Service- >Macro->Start recording...

In the macro recording window that opens, you need to specify its name, which will be displayed in the list of available macros, you can add a description (what the macro is for, author, etc.), assign a key for quick launch, and indicate in which book to save the macro. After pressing " OK» recording will start

Now, everything you do in the workbook (add, change, delete, create summary, etc.) will be recorded. For example, write in cell B3=45, B4 = 5, and in B5 the formula “=B3+B4*10”. To stop recording, click the appropriate button:

After recording is complete, our macro will appear in the list Tools->Macro->Macros ( Alt+ F8)

All you have to do is select it and click “ Execute».

All actions that we performed during recording will be repeated exactly. To check, clear the sheet and run the macro. But this method is not convenient and practically it is impossible to use the recording in the future because lack of versatility. The advantage is that by recording any actions we get a ready-made code, which in skillful hands becomes universal and is tailored to the necessary tasks. Let's look at what code was recorded. To do this, click the “Change” button in the menu Tools->Macro->Macros .

The following code will open:

Sub Macro1()
Range("B3").Select
ActiveCell.FormulaR1C1 = "45"
Range("B4").Select
ActiveCell.FormulaR1C1 = "5"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C*10"
Range("B6").Select
End Sub

Sub … EndSub– all macros launched through the menu Tools->Macro->Macros start with keyword Sub(procedure). Next comes the name of the procedure “Macro1”, which is also the name of our macro, which is indicated at the start of recording. Empty parentheses are required! Please note that the “running” procedure must not contain any parameters, otherwise the macro will disappear from the list. All procedures in VB are completed with the command EndSub. Sub has additional keywords Private And Public, defining the visibility area of the procedure. This will be discussed in future articles.

Range("B3").Select - this and subsequent commands were written when we selected cells B3, B4, B5.

ActiveCell.FormulaR1C1 – a command that writes a value or formula into a selected cell after the equal sign. This entry for assigning a value and formula to a cell is not very convenient. In the following lessons we will use the Cells property of the Worksheet object.

That's it. The simplest actions are recorded, but such a recording cannot be used in practice.

The second method is to write the VBA code manually. This method will be discussed in the next lesson and in all subsequent ones; we will work only with the second method.

And finally, a video demonstration of recording a macro.

Video: Record a macro in Excel

Many people think that macros in Excel are very difficult, but this is not true. In this article you will find some interesting and simple examples of how you can automate your everyday tasks in Excel. A macro is a Visual Basic program designed to automate tasks in Microsoft Office. In my examples I use Excel 2010, but you can just as easily use Excel 2007.

Create a macro using the “Record Macro” command

The recorded macro can be accessed using the command View Macros(Macros), which is located on the tab View(View) in the dropdown menu Macros(Macros). A dialog box will open Macro(Macro) in which you can select the one you need. Double-click the macro name to execute the program.

In addition, you can associate a macro with a button. To do this:

On the tab File(File) click Options(Options) > Quick Access Toolbar(Quick Access Toolbar).
In the field Choose commands from(Select commands from) select All Commands(All teams).
Find a team Option Button(Button), we need the one that belongs to the section Form Control(Form controls). Select it and click Add(Add). Then click OK to close Excel Options.
Select the command you just added to the Quick Access Toolbar and draw the outline of the button on your Excel worksheet.
Assign a macro to an object.

Note: If you have the tab enabled Developer(Developer), then you can access the form controls from it. To do this, go to the tab Developer(Developer), click on the icon Insert(Insert) and select the desired item from the drop-down menu.

Don't know how to display a tab Developer(Developer)? Excel 2007: click on the button Office > Excel Options(Excel Options) > Popular(Basic) and check the box next to the option Show Developer tab in the Ribbon(Show the “Developer” tab on the ribbon). Excel 2010: click on the tab File(File) > Options(Options) > Customize Ribbon(Customize the Ribbon) and in the right list enable the tab Developer(Developer).

FOR Loop

In the following example you will see how to use a loop FOR. Cycle FOR allows us to repeat the loop with different values. Let's see how we can fill in numbers from 1 to 5 cells A1:A5.

To do this, on the tab Developer(Developer) click Visual Basic. Double click on an object from the list Microsoft Excel Objects, in which the macro should be saved. Enter this code:

Sub Macro1 () For n = 1 To 5 Cells(n, 1) = n Next n End Sub

Save the file. To run the macro, go View > Macros > View Macros(View > Macros > Macros), select the name of the desired macro from the list and click Run(Run).

The following code displays the phrase “ Hello World” in the Windows message window.

Sub MacroName() MsgBox("Hello World!") End Sub

In the following example we create a message with a choice Yes(Yes) or No(No). If you select the option Yes(Yes), then the cell value will be deleted.

Sub MacroName() Dim Answer As String Answer = MsgBox("Are you sure you want to delete the cell values ?", vbQuestion + vbYesNo, "Delete cell") If Answer = vbYes Then ActiveCell.ClearContents End If End Sub

Let's check this code. Select the cell and run the macro. You will be shown this message:

If you press Yes(Yes), the value in the selected cell will be deleted. What if No(No) – the value will be saved.

IF design

In Microsoft Excel you can also use the construction IF. In this code, we will color the cells depending on their value. If the cell value is greater than 20 , then the font will turn red, otherwise it will turn blue.

Sub MacroName() Dim CellValue As Integer CellValue = ActiveCell.Value If CellValue > 20 Then With Selection.Font .Color = -16776961 End With Else With Selection.Font .ThemeColor = xlThemeColorLight2 .TintAndShade = 0 End With End If End Sub

To test this code, select a cell with the value more than 20:

When you run the macro, the font color will change to red:

If the second condition is met, the font will turn blue:

CASE design

You can also use the construct Case to associate the execution of an action with the appearance of a specific value in a cell. The following example displays a message whose content depends on the person's specified age.

Sub MacroName() Dim CellValue As Integer CellValue = ActiveCell.Value Select Case CellValue Case 60 To 200 MsgBox "The person is old" Case 30 To 59 MsgBox "The person is adult" Case 18 To 29 MsgBox "The person is young" Case 0 To 17 MsgBox "The person is a child" Case Else MsgBox "Unknown age" End Select End Sub

To test this example, you must select a cell with a value and run the macro. If the value of the selected cell, for example, is 44 , then this message will be shown.

Excel has a powerful, but rarely used, ability to create automated sequences of actions using macros. A macro is an ideal solution if you are dealing with the same type of task that is repeated many times. For example, processing data or formatting documents using 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 go ahead boldly - then we will go through the entire process of creating a macro step by step with you.

What is a Macro?

A macro in Microsoft Office (yes, this functionality works the same in many applications in the Microsoft Office suite) is program code in a programming language (VBA) saved inside a document. To make it clearer, a Microsoft Office document can be compared to an HTML page, then a macro is an analogue of Javascript. What Javascript can do with HTML data on a web page is very similar to what a macro can do with data in a Microsoft Office document.

Macros can perform almost any action you could possibly want in 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.
Do all of the above steps in any combination.

Creating a Macro - Practical Example

For example, let's take the most ordinary file CSV. This is a simple 10x20 table filled with numbers from 0 to 100 with headings for the columns and rows. Our task is to turn this data set into a presentable formatted table and generate totals in 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 line of code, which is what we'll do right now.

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

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

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

Excel signals that macro recording mode is enabled in two places. First of all, on the menu Macros(Macros) – instead of a line Record Macro(Record macro...) line appeared Stop Recording(Stop recording).

Secondly, in the lower left corner of the Excel window. Icon Stop(small square) indicates that macro recording mode is enabled. 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 macro recording mode is enabled, let's get down to our task. First of all, 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 formulas and copy them to all rows of our table by dragging the autofill handle.

Once this step is completed, the corresponding totals should appear in 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 original table data. If you take the average of the averages for individual rows, 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 original table data for the reason stated above.

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

Bold font style.
Center alignment.
Filling with color.

And finally, let's set up the format of the total values.

This is what it should look like in the end:

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

Congratulations! You've just recorded your first Excel macro yourself.

To use the created macro, we need to save the Excel document in a format that supports macros. First, we need to delete all data from the table we created, i.e. make it an empty 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, right-click on the icon Select all, which is located at the intersection of the row and column headings, and from the context menu select the item Delete(Delete).

Now our sheet is completely cleared of all data, while the macro remains recorded. We need to save the workbook as a macro-enabled Excel template that has the extension XLTM.

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.

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

Running a Macro in Excel

Before revealing all the capabilities of the macro you created, I think it’s right to pay attention to a couple of important points regarding macros in general:

Macros can be harmful.
Read the previous paragraph again.

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

To run our data formatting macro, open the template file we created in the first part of this tutorial. If you have standard security settings, then when you open a file, a warning will appear at the top of the table stating that running macros is disabled, and a button to enable their execution. Since we made the template ourselves 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 you import data from a CSV file, Excel may ask you to configure some settings to ensure that the data is transferred correctly to the table.

When the import is complete, 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 running, you will see the table cursor jumping 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 that we formatted manually, just with different data in the cells.

Let's look under the hood: How does a macro work?

As has been mentioned more than once, a macro is program code in a programming language Visual Basic for Applications(VBA). When you enable macro recording mode, Excel actually records every action you take as instructions in VBA. Simply put, Excel writes the code for you.

To see this program code, you need to go to 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 recorded using automatic macro recording in Excel. But more complex macros, with finely tuned sequence and logic of actions, require manual programming.

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

Imagine that our original data file 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 the updated data. The process of opening this file and importing data from it can also be recorded in a macro:

Open the template file in which we saved the macro - FormatData.
Create a new macro named LoadData.
While recording a macro LoadData import data from file data.csv- as we did in the previous part of the lesson.
When the import is complete, stop recording the macro.
Remove all data from cells.
Save the file as an Excel template with macro support (XLTM extension).

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

If you want to get into 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 in Microsoft Excel, you can quickly record a macro. Let's say you have dates in different formats and you want to apply the same format to all of them. This can be done using a macro. You can record a macro that applies the desired format, and then run it when needed.

When you record a macro, all actions described in Visual Basic for Applications (VBA) code are recorded. These actions may include entering text or numbers, clicking cells or commands on the ribbon or menu, formatting cells, rows, or columns, or importing data from an external source such as Microsoft Access. A Visual Basic Application (VBA) is a subset of the powerful Visual Basic programming language that is included with most Office applications. Although VBA provides the ability to automate processes between Office applications, you don't need to know VBA code or software programming if you need it.

It's important to know that when you record a macro, almost everything you do is recorded. So if you make a mistake, such as pressing the wrong button, the macro recorder will record that action. In this case, you can write the entire sequence again 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 Display the Developer Tab.

Record a macro

On 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 macro security settings and their meaning.
	Macros can be run in a variety of ways, such as through a keyboard shortcut, a graphic, a Quick Access Toolbar, a button, or even when opening a workbook.
	You can use the Visual Basic Editor to edit macros that are attached to a workbook.

	assign macro. In the field Assign macro


	Learn how to enable or disable macros 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 recorded code that the macro recorder does not support. Since the macro recorder captures almost every step performed during recording, you may also need to remove unnecessary code. Reviewing recorded code is a great way to learn VBA programming or hone your skills.

An example of changing the recorded code can be found in the article Getting started with VBA in Excel.

Record a macro

Before recording macros, it is useful to know the following:

A macro written to work with an Excel range will only run on cells in that range. Therefore, if you add a new row to the range, the macro will not apply to it.

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

A macro can also contain non-Excel tasks. The macro process can span 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 email it.

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

Go to settings > Excel... Toolbar & > Ribbons.

To record a macro, follow the instructions below.

Working with macros recorded in Excel

On 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 you run a 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, you can close the workbook without saving it.

Here's more information about working with macros in Excel.

		Learn how to enable or disable macros in Excel for Mac.
	If a workbook contains a VBA macro that you want to use elsewhere, you can copy the module to another workbook by using the Microsoft Visual Basic Editor.
Assigning a macro to an object, shape, or graphic element	In a worksheet, right-click the object, picture, shape, or element to which you want to assign an existing macro, and then choose assign macro. In the field Assign macro select the macro you want to assign.
	You can assign a macro to an icon and add it to your Quick Access Toolbar or Ribbon.
	You can assign macros to forms and ActiveX controls on a worksheet.
Opening the Visual Basic Editor	On the tab Developer click Visual Basic or select Service > Macro > Visual Basic Editor.
	Learn how to find help for Visual Basic elements.

More information

You can always ask a question from the Excel Tech Community, ask for help in the Answers community, or suggest a new feature or improvement to the website