Useful Tips

Creating Macros in Microsoft Excel

Pin
Send
Share
Send
Send


A macro is a small program that performs a series of activities within software, such as word processors and spreadsheets. Most programs come with many macros, r which can be accessed from the menu or by pressing a few keys. However, you may find that you can complete the work you are doing easier and faster if you create your own macros. Each software package has its own way of creating macros, but most should allow you to create macros by recording.

Macro Recording Methods

A macro can be written in two ways:

Using the first option, you simply record certain actions in the Microsoft Excel program that you are currently executing. Then, you can play this recording. This method is very easy, and does not require knowledge of the code, but its application in practice is quite limited.

Manual macro recording, on the contrary, requires programming knowledge, since the code is typed manually from the keyboard. But, correctly written code in this way can significantly speed up the execution of processes.

Automatic Macro Recording

Before you can start automatic macro recording, you must enable macros in Microsoft Excel.

Next, go to the "Developer" tab. Click on the “Macro Record” button, which is located on the ribbon in the “Code” tool block.

The macro recording setup window opens. Here you can specify any macro name if the default one does not suit you. The main thing is that the name begins with a letter, and not with a number. Also, the title should not contain spaces. We left the default name - "Macro1".

Immediately, if desired, you can set the keyboard shortcut, when clicked, the macro will be launched. The first key must be the Ctrl key, and the user sets the second key independently. For example, we, as an example, set the key M.

Next, you need to determine where the macro will be stored. By default, it will be stored in the same book (file), but if you wish, you can set the storage in a new book, or in a separate macro book. We will leave the default value.

In the lower field of macro settings, you can leave any description of the macro that is suitable for the context. But, this is not necessary.

When all the settings are completed, click on the "OK" button.

After that, all your actions in this Excel workbook (file) will be recorded in a macro until you yourself stop recording.

For example, we write the simplest arithmetic action: adding the contents of three cells (= C4 + C5 + C6).

After that, click on the “Stop Recording” button. This button was converted from the "Macro Record" button, after recording was started.

Macro run

In order to check how the recorded macro works, click the "Macros" button in the same Code toolbar, or press Alt + F8.

After that, a window opens with a list of recorded macros. We are looking for the macro that we recorded, select it, and click on the “Run” button.

You can do even easier, and do not even call the macro selection window. We remember that we recorded a combination of "hot keys" for quick macro calls. In our case, this is Ctrl + M. We type this combination on the keyboard, after which the macro starts.

As you can see, the macro performed exactly all the actions that were recorded earlier.

Macro editing

In order to edit the macro, again click on the "Macros" button. In the window that opens, select the desired macro, and click on the "Change" button.

Opens Microsoft Visual Basic (VBE) - the environment where editing macros.

Recording of each macro begins with the Sub command, and ends with the End Sub command. Immediately after the Sub command, the macro name is indicated. The operator "Range (" ... "). Select" indicates the selection of the cell. For example, with the command “Range (“ C4 ”). Select”, cell C4 is selected. The operator "ActiveCell.FormulaR1C1" is used to record actions in formulas, and for other calculations.

Let's try to change the macro a bit. To do this, add the expression to the macro:

Range ("C3"). Select
ActiveCell.FormulaR1C1 = "11"

The expression "ActiveCell.FormulaR1C1 =" = R [-3] C + R [-2] C + R [-1] C "" is replaced by "ActiveCell.FormulaR1C1 =" = R [-4] C + R [-3 ] C + R [-2] C + R [-1] C ”.”

We close the editor, and run the macro, as last time. As you can see, due to the changes we introduced, another data cell was added. It was also included in the calculation of the total amount.

If the macro is too large, it can take a long time to execute. But, by making a manual change to the code, we can speed up the process. Add the command “Application.ScreenUpdating = False”. It will save computing power, which means speed up work. This is achieved by refusing to update the screen while performing computational actions. To resume updating after executing the macro, at the end we write the command “Application.ScreenUpdating = True”

We also add the command “Application.Calculation = xlCalculationManual” at the beginning of the code, and at the end of the code we add “Application.Calculation = xlCalculationAutomatic”. Thus, at the beginning of the macro, we turn off the automatic recalculation of the result after each change in the cells, and at the end of the macro we turn it on. Thus, Excel will calculate the result only once, and will not constantly recount it, which will save time.

Writing a macro code from scratch

Advanced users can not only edit and optimize recorded macros, but also write macro code from scratch. In order to start this, you need to click on the button "Visual Basic", which is located at the very beginning of the developer ribbon.

After that, the familiar VBE editor window opens.

The programmer writes the macro code there manually.

As you can see, macros in Microsoft Excel can significantly speed up the execution of routine and uniform processes. But, in most cases, macros whose code is written manually rather than automatically recorded actions are more suitable for this. In addition, macro code can be optimized through the VBE editor to speed up the task.

Thank the author, share the article on social networks.

Pin
Send
Share
Send
Send