Creating and Applying Macros in Microsoft Excel

If you need to apply a certain sequence of actions, you do not need to do them all over again. Enough to record a macro in the Excel system. It will contain all the commands in its code.

Once a macro has been recorded and saved, it can be used an unlimited number of times. It is enough to start it by pressing the key selected to call it. It will be more convenientthan repeating the same steps manually. 

To save a macro, you need to initiate recording process. To do this, look up Excel top bar and find the tab “View”, open it. After that click on the button “macro”

Note: In the 2003 version of Excel, this menu is located in the Tools panel.

These parameters can be seen in the picture below:Creating and Applying Macros in Microsoft Excel

After entering the macro menu, press the button “Record”. It will be on the right. You can name the macro if you wish. It is recommended to give it an exact name. Then when you return to it there will be no confusion. 

If there is no username, the system will name it automatically and number it. For example, Macro_1, Macro_2, etc.

When creating a macro, you can assign a keyboard shortcut. This will speed up its use. Be careful! You do not need to assign key combinations already predefined by the system to the macro. For example, CTRL+C. Such an action will break the usual work, because your macro will be written in place of the standard command.Creating and Applying Macros in Microsoft Excel

Once all parameters are set, click OK. The system will start recording the macro. 

When a macro starts running, the action nested in it (selecting cells, entering data, formatting cells, scrolling the worksheet) will be written as VBA code.

During the process of creating and recording a macro, the process can be stopped with a special button. It is located in the lower left corner. 

Note: In the 2003 version of Excel, this key appears on the toolbar. 

All as shown in the picture:Creating and Applying Macros in Microsoft Excel

Once the desired actions are recorded, you can stop the macro from running. Its code will now be stored in one of the Visual Basic editor modules.

Option “Use relative links”

If you select Relative References while recording a macro, all cell references in the macro will be relative. Otherwise, the links will be absolute in the VBA code.

The Relative Links option is in the Macros menu. In the 2003 version of Excel, this option is available on the toolbar.Creating and Applying Macros in Microsoft Excel

Viewing VBA code

The VBA code generated by the macro recording is in a module. You can access and view it using the Visual Basic editor. To do this, use the keyboard shortcut ALT + F11. Hold your finger on the ALT key and press F11 while doing so.Creating and Applying Macros in Microsoft Excel

The code is in one of the project window modules. It is located to the left of the Visual Basic Editor. The picture above shows that the macro code can be seen by clicking on “Module 1”.

Running macros in Excel

You can run a macro with the Sub procedure (not to be confused with the Function procedure). When assigning keys to a macro, simply press the necessary buttons on the keyboard. Otherwise, resort to the following steps:

  1. Press the key combination ALT + F8 (without releasing ALT, press F8). The macro menu will open.
  2. In the Macros dialog box, select the desired item.
  3. Click Launch.

Disadvantages and limitations of functionality

Recording macros in Microsoft Excel is an easy way to create VBA code. But its functionality is limited to simple operations. The following code functions cannot be used:

  1. Definition of a constant, work with variables and arrays.
  2. Operations with conditions.
  3. Parentheses.
  4. Built-in functions and other procedures.

Also, Sub procedures cannot return variable values. They do not have access to the worksheets and argument value ranges that are stored in the workbook. The generated code is therefore not a profitable solution for such operations.

If you want to create macros for more complex operations, you can adapt the VBA code for yourself. To do this, you need to overwrite it and implement more complex macros.

Leave a Reply