For many of us, VBA is a relatively new concept. In this article, we will introduce an effective way to self-learn VBA—by using the Record Macro feature in Excel.
Nội dung
1. What are macros?
Macros – or more specifically Excel Macros (in the scope of the article learning about VBA in Excel) are small programs that help repeat operations from simple to complex in Excel automatically.
Macros can consist of 1 or more statements contained within a procedure called Sub .
Each Sub will start with the keyword Sub and end with the keyword End Sub
2. How to create a macro using Record Macro
We can create a macro in a very simple way by performing Record Macro.
Record Macro means to record user’s actions, convert that action into a procedure (sub) to turn it into a Macro.
The steps to Record Macro are as follows:
Step 1: Open the Developer tab
The Developer tab is a special tab in Excel that serves developers who want to apply VBA to Excel to make Excel work more efficiently. This tab itself is already in the Excel toolbar, but by default it is not displayed. Therefore, to use this tab, we must open it.
Step 2: Click the Record Macro button
At the Developer tab, we select the Record Macro button as shown below:
When you click the Record Macro button, an information window about the created Macro will appear, including:
- Macro name : name of the Macro created by Recording Macro
- Shortcut key : shortcut key for created Macro
- Store macro in : where to store macros in VBA Projects
- Description : description of macro, notes
Next we click the OK button in the Record Macro window to start the Macro recording process.
Step 3: Notes when ending the Record Macro command
Once we enter the Macro recording process, we need to pay close attention:
- Any of our operations will be recorded into the code of Macro in VBA (whether working with Excel or not). Therefore, it is necessary to limit unnecessary operations: scrolling the mouse, selecting/moving too many cells, many sheets…
- Complex operations will produce complex statements. So try to use the simplest, most accurate operations if you are new to VBA.
- Always remember to press the Stop Recording button when you want to stop recording a Macro. The Stop Recording button is located exactly where the Record Macro button is when in Macro recording mode:
Step 4: Check the results of the recorded Macro
The recorded Macro will be in the VBA environment. Therefore, we need to open this environment to be able to view the content of the Macro.
In the Developer tab, click on Visual Basic (or shortcut key is Alt + F11 )
In the VBA working interface, we pay attention to the Project window: Here appears the Modules section, which includes Module1.
When you click on Module1, you will see Sub Macro1. This is the name of the Macro just recorded by Record Macro.
3. How to learn VBA by yourself through Record Macro
For those who are new to VBA programming language, remembering how to write commands is quite difficult. The thing we worry about the most is not understanding how to write a command correctly, then how to express a desire into a command in a macro.
Both of these problems can be solved by using Record Macro. We don’t need to know how to code or write it ourselves, because Excel will do it for us. We just need to know how to control Excel to do it, and know how to use the results.
Principles when self-studying VBA through Record Macro (not required, but should be followed)
Principle 1: Know the purpose and the operations to be performed.
This helps to reduce redundant operations, and at the same time helps to better understand the code through the operations just performed.
Principle 2 : Record the operations in Vietnamese first for each command.
This makes the process of reading and recompiling code easier and easier to understand when comparing Vietnamese with code.
Principle 3 : Remove redundant, unnecessary code
In the process of recording macros, it is inevitable that there will be redundant operations. Therefore, when applying principles 1 and 2, we can filter out which code segments are redundant and unnecessary. Once filtered, we should delete the redundant code segments to avoid confusion during the process of running and processing the code later.
Principle 4 : Save learned macros
After each time learning VBA through recording macro, we get a macro to do a specific task. These tasks can be repeated many times during the process of learning VBA, applying VBA to Excel. Therefore, we need to save the learned macros so that we can reuse them at any time, avoiding wasting time doing them again. This will help us increase our learning speed, know which code segments are new, which codes we already know… to add to the growing list of macros.
Conclude
Programming includes two main areas:
- Automate repetitive operations => Can be learned through macro recording
- Create programming commands as desired, not available => Need to learn programming thinking, learn how to write code
So when we know how to use record macro and learn VBA through this operation, it means we have learned almost half of what we need to learn and do. Therefore, for those who are planning to learn VBA from zero, we should start by learning how to use record macro in Excel right away. You will see that learning VBA is not as difficult as you think.