Why macros are needed with Excel VBA

Now we will introduce the basics of automating tasks, which is not possible without the use of macros. A macro is a program consisting of a list of commands that an application must execute. A macro serves to combine several different actions into a single procedure. This list of commands mainly consists of macro statements closely related to application commands in Microsoft Office. Most macro statements correspond to menu commands or options set in dialog boxes.

There are three main types of macros:

• Command macros — the most common type of macros, usually consisting of statements equivalent to specific menu commands or dialog box options. The primary purpose of these macros is to perform actions similar to menu commands, i.e., changing the environment and core objects of the application. For example, modifying a worksheet or workspace in Microsoft Excel, saving, or printing, etc. Thus, executing a macro results in changes either to the document being processed or to the overall application environment.
• User-defined functions — work similarly to built-in Microsoft Excel functions. Unlike command macros, these functions use the values of arguments passed to them, perform calculations, and return a result to the calling point, but do not change the application environment.
• Macro functions — a combination of command macros and user-defined functions. Like user-defined functions, they can use arguments and return results, but, like command macros, they can also modify the application environment. Macro functions are often called from other macros and are widely used in modular programming. If a series of identical actions needs to be performed in various macros, these actions are usually separated into a standalone macro function (subroutine).

Typically, macros are used to quickly generate a draft version of code. Keep in mind the sequence of actions involved in macro development:

1. Logical procedure design. First, you need to clearly define the result that the macro should produce and the logical sequence of actions required to achieve this result.
2. Document preparation. Perform preliminary actions that do not need to be included in the procedure (e.g., creating a new worksheet or moving to a specific part of a worksheet, etc.).
3. Recording the macro using the macro recorder. The macro recorder is a translator that creates a program (macro) in VBA language, translating the user’s actions from the moment the macro recorder starts until the recording ends. To record a macro using the macro recorder:
   • Go to the Developer tab on the ribbon and, in the Code group, click Record Macro.
   • In the Record Macro dialog box, set the parameters of the procedure being recorded (name, description, shortcut key, and which documents the macro will be available for) and enter macro recording mode. The Record Macro button on the Developer tab will change to Stop Recording; the Pause button will also become active (if you want to pause the recording temporarily to perform other actions with the document).
   • Perform all necessary actions with the document and its contents as planned in step one.
   • Stop the recording (Stop Recording button in the Code group on the Developer tab).
4. Viewing and editing the created procedure:
   • Click Macros in the Code group on the Developer tab.
   • In the Macro dialog box, select the macro name and click Edit. The main Microsoft Visual Basic editor window and the Module window containing the macro code will open.
   • Make the necessary edits to the macro code and close the editor window.
5. Running the macro:
   • Click Macros in the Code group on the Developer tab.
   • In the Macro dialog box, select the macro name and click Run.

NOTE:
You can assign a button to a recorded procedure and place it on the Quick Access Toolbar to simplify macro execution.