To enable Macro/Visual Basic for Application (VBA) in Excel, there are two steps. Before we start, let’s talk about what Macro and VBA are about.
Macro is a recording tool in Excel – we press a button to “start recording”, we then do a series of activities/processes and at the end we press a button to “end recording”. Excel records that series of activities/processes so that with a click of a button, Excel will repeat those specific steps for us.
VBA (Visual Basic for Application) is very similar. Except, instead of recording the series of steps, we have to write them as lines of codes in modules. We can create user forms, automate a series of steps, create reports, send emails out and a lot more. It is important to note that Macro is really part of VBA. Once the series of activities/steps are recorded, the Macro is essentially created into a module in VBA and we can go in to review/edit the codes.
Tip: a simple way for anyone to start learning VBA is to record a simple Macro and then go into VBA to review the module. That way we can learn the basics of how VBA language works.
We will not go through how to record Macro or how to use VBA in this article. They will come in later articles. Right now, let’s first go through how to set up Excel properly so that it is ready to use Macro/VBA:
1. Enabling Developer Tab
To enable the Developer tab:
- Go to File > Options
- Click on Customized Ribbon
- Under the Main area, make sure Developer is in this section and selected. If Developer is not already in the Main tab, you can find it in All Commands section and add Developer to the Main area:
And you will be able to see the Developer tab:
2. Save the File as Macro-Enabled Worksheet Format
The second step is to save the file as Macro-Enabled Worksheet format:
- Go to File
- Click on Save As
- Select folder path
- Save as type: Excel Macro-Enabled Template
Once that is saved, your Excel file is now in Excel Macro-Enabled format.
And that’s it. You will now be able to see the Developer tab, record a Macro or go into Visual Basic and start inserting your own module and write some codes. If we’ve missed anything in this article, feel free to leave a comment below!