How to use macros. Learning to Write Macros - Office Space

Features of Excel are not limited to a set of built-in functions. By writing macros, you can create your own functions to perform non-standard tasks in Excel.

For example, a self-written macro can be attached to an icon and displayed on the Menu Ribbon. Or you can create a User Defined Function (UDF) and use it just like the rest of Excel's built-in functions.

A macro is a computer code written for Excel in a programming language Visual Basic for Applications (VBA). The basic concepts of the VBA programming language are covered on our website in the VBA Tutorial. However, before you start writing VBA code, we recommend that you read the lessons that discuss the security of Excel macros and the Visual Basic editor.

Setting permission to use macros in Excel

Excel has built-in protection against viruses that can enter your computer through macros. If you want to run a macro in an Excel workbook, make sure the security settings are set correctly.

Visual Basic Editor

Macro recording

The Excel macro recording tool is a great way to efficiently perform simple repetitive tasks. It can also be used as an aid in writing more complex macros.

Excel VBA Tutorial

For those who are just starting to learn the Excel VBA programming language, we offer a small introductory course on Visual Basic for Applications.

If you have tasks in Microsoft Excel that are repetitive, you can record a macro to automate those tasks. A macro is an action or set of actions that can be run as many times as you like. When you create a macro, mouse clicks and keystrokes are recorded. Once a macro has been created, it can be modified to make small changes to how it works.

Let's say that every month you create a report for your accounting manager. You want to format expired usernames in red and apply bold formatting. You can create a macro that quickly applies these formatting changes to selected cells and run it.

Procedure

Macro Recording

To edit a macro, in a group the code tab developer press the button macros change

Further actions

Procedure

Detailed consideration of the macro To familiarize yourself with the Visual Basic programming language, you can modify the macro. To edit a macro, on the tab developer press the button macros, select a macro name, and then click change. Will open visual editor basic. See how the recorded actions are output as code. Some of the code will most likely be understandable to you, and some of it may be a bit mystical. Experiment with the code, close the Visual Basic Editor, and run the macro again. This time, see what happens if something goes wrong.

additional information

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

The article is intended for people who want to learn how to write programs in the built-in Excel Visual Basic (VBA), but absolutely do not know what it is.

First, a few words about why this is needed. The VBA tool in MS Excel presents us with a versatile tool for quickly and exact solution any individual user tasks in MS Excel. Of course, you can also use the built-in MS Excel Functions of which there are a great many, but they do not always solve the problem.
So let's create an example the simplest program. We will use MS Excel 2007. Open MS Excel, click "save as" and save your program file by clicking "Excel book with macro support".

Next, you need to enable the "Developer" tab. To do this, click "Excel Options"

Check the box next to "Show Developer tab on the ribbon"

After that, on the ribbon, at the top of the Excel sheet, the "Developer" tab will appear, which contains tools for creating VBA macros.
Let's imagine a small task - let's say we have 2 numbers, we need to add them up and get the value from our table using the resulting amount.
Let's put the following values ​​in the cells of Sheet1:

on Sheet2, fill in the cells by creating a table of 2 columns

Next, go to Sheet1, click on the tab "Developer", "Insert", select the button on it
and draw a button on Sheet1, after which the "Assign a macro to an object" window will immediately appear, in which we select "Create"

After that, the Visual Basic editor will open, and the name of the procedure that will be executed when the button is pressed will be automatically written. Under the name of the procedure, write following code:

The code will do the following:

• MsgBox ("This is my first Macro!") message
• The variable q is assigned the value of the cell on Sheet1, with coordinates 2 row, 2 column
• The variable w is assigned the value of the cell on Sheet1, with coordinates 3 row, 2 column
• In a cell on Sheet1, with coordinates 4 row, 2 column, the sum q + w is written

Next, we get the value of column B from Sheet2, which is located on the same line where the value of our sum matches the value of column A.
Let's enter the following code:

and we get the following result when the button is clicked:

the result shows that the macro picked up a number from the table on Sheet2 in accordance with our sum.
I won't go into the details of this tricky code, as the purpose of this article is to start writing macros. For VBA, there are a lot of resources on the Internet, with examples and explanations, although the amount of information in the help is quite enough to automate the calculations.

Thus, using VBA, it is possible to automate the calculation of any complexity and sequence. Reference tables can be copied from various literature onto separate Excel sheets and a sequential calculation can be written with buttons.

Creating macros in different versions Excel to automate repetitive user actions, which can be simple or complex, repetitive or infrequent. To use macros in practice, you usually have to adjust them in the VBA editor. For Excel to run macros, you need a .

Developer Tab

Macro recording is carried out by a built-in recorder, the launch button of which is located on the "Developer" tab of the main menu Excel programs. By default, this tab is not displayed. You can add a tab to a panel like this:

Excel 2010-2016

We pass along the path: File - Options - Customize Ribbon. In the "Customize Ribbon" section on the right in the "Main Tabs" list, check the "Developer" box and click the "OK" button.

Excel 2007

We pass along the path: the icon " Microsoft office"- Excel Options - category "Basic", in the category "Basic" check the box "Show the tab" Developer "on the ribbon" and click the "OK" button.

Excel 2000-2003

There is no need to display anything here: the buttons for working with macros and the VBA editor are located in the main menu on the "Tools" - "Macro" tab.

In fact, in Excel, not buttons, but menu items are used to work with macros, but, for convenience, I will continue to call them buttons.

Macro Recording

Excel 2007-2016

On the ribbon, on the Developer tab, click the Record Macro button.

Excel 2000-2003

Go through the menu items "Tools" - "Macro" - "Start Recording".

A window will open:

Macro name: can be changed or left by default ("Macro" with the next number). If you already have a macro with the same name, it will be written to a new program that will be created automatically.

Keyboard shortcut: add a letter if you want to run the macro with a keyboard shortcut Ctrl+letter. In which layout you enter the letter (for example, "й" or "q"), in that one the macro will run. If a letter is entered in upper case("Y" or "Q"), use the keyboard shortcut to run the macro Ctrl+Shift+"letter".

• "This book"- the macro will be recorded in the book from which the recorder was launched (the "Record macro" button is pressed).
• "A new book"- will open A new book and the macro will be written in it.
• "Personal Macro Book"- the macro will be recorded in and will be available from all open Excel workbooks.

Description: here you can add short description macro, which will be displayed in the window of available macros when it is selected in the list.

Having filled the window, press the “OK” button, the “Record macro” button changes its name to “Stop recording”, and you can actually start recording the macro. Perform the operations on the worksheet, the sequence of which you want to automate and, after their completion, the Macro will be recorded and appear in the list of macros.

Macro List

A window with a list of macros is opened in all versions of Excel by clicking the "Macros" button. It looks like this:

• Run- the selected macro will be launched for execution.
• To come in- transition to the macro code, launched and paused, as if an error was detected in the code.
• Change- transition to the macro code for editing.
• Create- the button will become active when you write the name of a new macro in the "Macro name" field.
• Delete- deleting the selected macro.
• Options...- editing keyboard shortcuts to run a macro and its description.
• Is in:- here you can choose everything open books or one of them.
• Description- text added to the field of the same name when creating a macro or editing its parameters.

Running a macro

You can run a recorded macro in the following ways:

• The keyboard shortcut specified in the macro parameters.
• The "Run" button from the macro list window.
• The "Run Sub" button or the "F5" key from the VBA editor.
• The button added to excel sheet from the Form Controls section and to which the triggered macro is assigned.

In addition, you can assign a macro to an image, add a launch button to the Quick Access Toolbar and to the menu (on the ribbon).

Example of recording a simple macro

Do the following:

Excel 2007-2016

1. Open an Excel workbook or create a new one and save it as a macro-enabled (.xlsm) workbook.
2. On the Developer tab, click the Record Macro button.
3. Go to the "Home" tab and choose any cell fill color.
4. Click the Stop Recording button.

Excel 2000-2003

1. Open an Excel workbook or create a new one and save it with the default extension (.xls).
2. Enable macro recording by going through the menu items "Tools" - "Macro" - "Start Recording".
3. In the Record Macro window, click the OK button, leaving the default settings.
4. On the toolbar, select any cell fill color.
5. Click the Stop Recording button.

As a result, the simplest macro of coloring the background of the selected cell or range will be recorded.

Open the macro list, select the recorded macro and click the "Edit" button. You will see your macro code:

Sub Macro1() " " Macro1 Macro " " With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

The number (5296274) in your example will be different, depending on the selected .

Select any cell or range on the active sheet, return to the VBA editor and press the "F5" key (the cursor should be anywhere inside the code of the macro being run). On the active sheet, the selected range will be painted in the color selected when recording the macro.

The macro recorder can record a lot of extra lines that you can do without. Similarly, the macro will work in this edition:

Sub Macro2() Selection.Interior.Color = 5296274 End Sub

Copy this code and paste it below, under your macro, in the VBA editor (you can change the numerical value of the color to your own or leave it as in the example). Check its performance by selecting unpainted cells and running the macro with the "F5" key, remembering that the cursor must be inside this macro.

Macro names should differ within one , but even better - all macros in the same workbook should be given unique names.

Assigning a Macro to a Button

Now let's assign the recorded macro (either of the two) to the button added to the Excel sheet from the Form Controls section:

Excel 2007-2016

1. On the "Developer" tab, click the "Insert" button and in the "Form Controls" section, click on the button image.
2. Move the cursor to the tabular area of ​​the sheet, the cursor will take the form of a cross, and click the left mouse button.

Excel 2000-2003

1. 1. Display the "Forms" toolbar by going through the menu items "View" - "Toolbars" - "Forms".
   2. Click the Button on the Forms toolbar, then move the cursor to tabular part worksheet, press the left mouse button and, without releasing it, draw a rectangle (button outline) of the desired size, then release the mouse button.
   3. In the Assign Macro to Object window that opens, select one of the new macros and click the OK button. If you click the "Cancel" button, new button will be created without a macro assigned. It can be assigned later by right-clicking on it and selecting "Assign Macro..." from the context menu.

The button has been created. Select a cell or range and click the button you created. The background color will change.

You can record another one of the same macro, just select "No fill" in the palette, create another button and assign the newly created macro to it. Now, the first button will color the selected range, and the second will clear it from the fill.

An abbreviated version of the code for clearing the background would look like this:

Sub Macro3() Selection.Interior.Pattern = xlNone End Sub

To edit a button - change the caption, font, size, move, etc. - use context menu and markers that appear after clicking on it with the right mouse button. If you want the button not to leave the visibility zone when scrolling the sheet, fix the top line and place the button on it.

Good day. Alexey Gulynin is in touch. In this article, I would like to talk about how to record a macro. In my examples, I will use Microsoft Excel 2007, but you can use any (it is desirable that the version was not lower than mine). Go to Excel, select View - Macros - Macro Recording:

In versions 2010 and 2013, the panel may differ, but not significantly. Next, this dialog box should open, which is called "Macro Recording":

Specify the name of the macro if it will be the final solution, and you are going to use it in the future. If you are going to record a macro in order to peep something (that you forgot), then you can leave the name as it is.

It is forbidden to use in the macro name: spaces, punctuation marks, numbers at the beginning of the name. You can use: English and Russian letters, numbers (not at the beginning), underscore. It is also forbidden to name a macro whose name will match any existing object in Excel.
For a macro, you can set a keyboard shortcut, when pressed, it will run. You do not need to assign hotkeys to all macros, you only need to assign them to those that we will often use. In general, there are many ways to run a macro, as an option, you can place it on the ribbon (starting from the 2010 version), in the 2007 version there is a ribbon, but it cannot be edited, you can place it on a sheet. Also, the macro can be run on any event. Hotkeys have sensitivity to both case and layout, so be careful what hotkey you assign to the macro. You can put capital letters, for this you have to press Ctrl + Shift + Your letter.

If the macro is saved in this book, then it will be physically located in this file. Therefore, when the file is closed, this macro will not be available. In what cases it is justified to do this: the macro is required only for calculations in this book and no more in any others. If this macro needs to be used in other Excel files (books), then given file you have to always keep it open or constantly open and close, which is completely inconvenient. Here you need to choose another way to save: personal macro book.

It's almost a normal file and it's called Personal.xlsb . This file is located in the XLStart folder and is opened in stealth mode. While we have not written anything to this file, it does not exist. As soon as we write something, it will appear. Everything in the XLStart folder starts automatically. The advantage here is that the macro that you wrote down in your personal book will always be available (it will not need to be constantly opened and closed). I draw your attention to the fact that it will be available only on this computer.
You can also add a description to the macro.

Let's write the following macro, which will create a new sheet and in cell C4 will write the phrase Hello VBA. Moreover, the inscription should be red and the design style: bold. We press OK.
Insert the sheet like this:

Now in cell C4 we write the phrase Hello VBA. Then press Ctrl + Enter to stay in the same cell and apply formatting to it. If we just press Enter , then the focus will move to another cell (we don't need this). Then right-click on the cell and select Format Cells and go to the tab Font, select red and color and make the font bold:

Click OK. Now we can complete our macro. You can do this in several ways: View - Macros - Stop Recording, can also be disabled in the lower left corner (in the 2007 version) in the status bar (you can see it on the 3rd picture). "Sheet4" has been created. Let's delete it and use the macro to create a new sheet. For this we use hot key, which we set for the macro Ctrl + Shift + C. Make sure you are on the Russian keyboard. If you did everything right, then you should have created a new sheet with the record we need.