VBA BEGINNER

VBA BEGINNER

Basic knowledge of Macro and VBA

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
Analyze your household with graph

* You can check the trend of up to four items in graphs.

* You can also compare an item for up to four different years.

Here is for more information.

Recent post
  1. How to highlight today’s date in Excel

  2. How to highlight cells or rows that match a condition

  3. Three solutions to try when you cannot save an Excel file

  4. How to format date to uppercase month in the mail merge document

To display the list of articles, please create at least 4 articles in the custom post Blog.

TOP