New to Excel VBA (Visual Basic for Applications)? Let’s examine the two easy ways to start using Excel VBA right now.
Method 1: Macro Recorder
The first method is to use the Macro Recorder. Let’s watch this video to see how to use the Macro Recorder to record simple instructions and then run the recorded Macro.
Let’s break down each step in the video.
1. Start Recording a Macro
To use the Macro Recorder, press “View” tab –> “Macros” –> “Record Macro…”.
You can assign a name for your Macro, e.g. with the purpose of your Macro. Click “OK” to start recording.
2. Record your actions
After turning on the Macro Recorder, we can do whatever we want, e.g. typing texts and formula, altering fonts of texts into bold or italic, modifying column width, etc. These actions would be recorded into our Macro.
If you click “Macros” under “View” tab, you will notice that “Record Macro…” is replaced by “Stop Recording”. This indicates that Excel is currently recording your activities until you click “Stop Recording”.
3. Execute your Macro
After we click “Stop Recording”, we can open a new sheet and run the Macro by selecting “View” tab –> “Macros” –> “View Macros”
Select the Macro you just recorded and click Run. Excel VBA will execute the exact recorded actions.
Power of Macro Recorder
This is an easy way to automate simple tasks and save your precious time. For example, let’s say you need to build a colorful table with headers and borders every week. Instead of building the table manually, you can record your actions using the Macro Recorder once. In the future, you just run the previously recorded Macro and the same table would be built automatically. This saves time for simple yet repetitive tasks.
The limitation of Macro Recorder is that it only records your exact action. The Macro Recorder cannot incorporate complex logic, e.g. to perform different tasks under different conditions. The solution is to alter and develop the backend VBA code.
Select “View” tab –> “Macros” –> “View Macros” –> “Edit”
Congratulations! You have successfully discovered the Visual Basic Editor (VBE). In VBE, you can view the code responsible for your recorded actions. For example, “ActiveCell.FormulaR1C1 = “formula/value” can assign formula/value to the ActiveCell (the cell you are currently selecting). “Selection.Font.Bold = True” can set the cell you are currently selecting to have a bold font.
If you are a beginner to excel VBA, you can learn how to code different actions through using Macro Recorder. Just start your Macro Recorder to record your actions, and see the syntax responsible for your actions.
Method 2: Developer tab
The Visual Basic Editor (VBE), where all your VBA work is done, can be accessed through Developer tab. By default, the Developer tab is not displayed. Turn on the Developer tab following these instructions:
1. Click “File” –> “Options”
2. “Customize Ribbon” –> Tick the box of Developer –> “OK”
3. Developer tab should be available now. Click Visual Basic to access the VBE.
Visual Basic Editor
The code you created using the Macro Recorder is stored in Module 1. In the Code Window, you can edit the recorded code or you can manually design code. Let’s design a very simple procedure called Greeting_User, which prompts a message box to greet the user.
1. Create a Sub (which stores the code you want to execute):
2. Press Enter and “End Sub” should be automatically provided like this:
3. Type the following code:
4. Click the Run button:
5. A message box would be displayed:
Congratulations! You have written your first Excel VBA programme in just a few minutes.
Saving your file as .xlsm format
It is essential to save your workbook as an Excel Macro-Enabled Workbook (file extension .xlsm). Otherwise, you cannot run your code next time when you open your workbook.
Summary and Key Takeaways
- Start using Excel VBA by using Macro Recorder, which records your exact actions into editable code.
- Turn on your Developer tab to easily access and modify your code.
- Remember to save your file in .xlsm format.
You can try create simple procedures and explore the power of Excel VBA right now! Enjoy!
Thank you very much for reading til the end. Please feel free to leave any comment down below. 🙂 Your comments are greatly valued.