Microsoft Office products, such as Excel and Access, are applications that can use for many purposes. However, many things cannot be done with just the basic functions while using it for work. By using VBA, you can automate almost any action that you perform manually with Office products, and you can get more work done in less time.
We often hear the words “Macro” and “VBA”, but what are they. This lesson introduces you to the basic knowledge of Macro and VBA, and how to use the Macro.
Language: English Japanese
1. Macro and VBA
Macro
Macro is a series of Excel commands and instructions that are recorded so that they can be executed as a single command. Excel has a feature called “Record Macro“, which allows you to record all actions that you perform manually.
In our daily work, we have tasks in Excel that we do repeatedly. If you record a series of repetitive actions, you will not have to perform the task manually, which will greatly streamline your work.
You can create a macro by recording time-consuming tasks such as selecting and aggregating data, and you can also register it to a button. With just clicking a button, the process can be executed so that you can make your work more efficient and simpler.
VBA
Visual Basic Application (VBA) is a subset of the powerful Visual Basic programming language and is included with most Office applications. When you record a macro, the macro recorder records all the steps in VBA. Each step or instruction written in VBA is called “code“.
By programing with VBA, you can make complex processes that the Macro Recorder cannot record. All the main Office applications: Word, Excel, Outlook, Access, and PowerPoint support VBA. Once you have learned to use VBA in Excel, you will be able to apply the knowledge to using VAB in another application.
Macro and VBA are often confused, but it is better to understand that Macro is a function that executes a series of commands and instructions, while VBA is a programming language for creating macros.
2. Before you record a macro
Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it. The Developer tab provides convenient buttons for recording, executing, and editing macros. Show the Developer tab with the following steps.
1 Click on the File tab.
2 Click on Options. The Excel Options dialog box will appear.
3 Select Customize Ribbon.
4 Under Customize the Ribbon and under Main Tabs, select the Developer check box.
5 Click on the OK button.
6 The Developer tab will be displayed.
3. Create a macro
There are two ways to create a macro: Record Macro and Type code directly.
Record Macro
When you record a macro, the macro recorder records all the steps of your mouse clicks and keystrokes. It is not necessary to know VBA code or computer programming if the Macro Recorder does what you want. Since the macro recorder captures almost every step that you make while recording, you need to clean up any unnecessary recorded code that serves no purpose in the macro.
Type code directly
Type VBA code to create a macro. You can create your functions, control structures, and make complex processes that the Macro Recorder cannot record.
3-1. Record a Macro
Let’s record the following actions using the Record Macro function.
* Enter [ 2022 ] in cell A1 and [ January ] in cell A2.
* Set the font to [ Yu Gothic ] and the font size to 14 points.
* Set the text color to blue.
1 In the Code group on the Developer tab, click Record Macro. The Record Macro dialog box will appear.
2 Enter a name for the macro in the Macro name box.
3 Make sure [ This Workbook ] is selected in the Store macro in box, and then click the OK button.
4 Perform the actions you want to automate.
* Enter [ 2022 ] in cell A1 and [ January ] in cell A2.
* Select cells A1 and A2 and set the font to [ Yu Gothic ] and the font size to 14 points.
* Set the text color to blue.
5 In the Code group on the Developer tab, click Stop Recording.
3-2. Run a Macro
Let’s run the macro that you have recorded.
1 In the Code group on the Developer tab, click Macros. The Macro dialog box will appear.
2 In the Macro name box, select the macro that you want to run and click the Run button.
3 The macro will be executed.
3-3. Delete a Macro
You can delete a macro with the following steps.
1 In the Code group on the Developer tab, click Macros. The Macro dialog box will appear.
2 In the Macro name box, select the macro that you want to delete and click the delete button.
3 The macro will be deleted.
–
Next Lesson
