If you are new to developing macros and programming with Visual Basic in Excel, you are at the right spot. In this blog post, we will cover step-by-step instructions on how to set up Visual Basic in Excel, along with some starter concepts to give you a head start in VBA.
#1: Find/Add the Developer Tab
“Developer” tab in Excel is what provides users the opportunity to program code with Visual Basic in Excel.
On the ribbon at the top of your Excel sheet, you will see options like “Home”, “Insert”, “Draw”, etc. If you don’t see an option for “Developer”, we must add that option to the ribbon before setting up a macro-enabled Excel file. To add the developer option, right-click on any tab in the ribbon. You will see an option named “Customize the Ribbon”. Clicking on that option will open a window.
On top, you will see a drop-down menu titled “Choose commands from:”. Choose “Main Tabs” from the drop-down. When the “Main Tabs” appear, you can see the “Developer” option. Now choose the Developer tab and “Add” so it appears on the ribbon. This tab will be positioned to the right of the list, alongside other tabs already visible on your ribbon. Click “OK” when done.
This will set up your ribbon with the options for Visual Basic in Excel.
#2: Open Visual Basic in Excel
Go to the “Developer” tab and click “Visual Basic.” A new window will open, and all open Excel workbooks will be visible on the left side. Each workbook will display a ± symbol to its left. This allows users to expand or contract the workbook to view and work on the desired sheets or modules.
VBA Code on Sheet vs. Module
Now, before we proceed with programming the first sub with Visual Basic in Excel, it is essential to understand the pros and cons of having your Excel VBA code on a sheet versus a module. This will enable you to make an informed decision regarding reusability, redundancy, ease of use, and code hygiene.
In general, the code in Excel VBA runs in the form of subs, which are small elements of code performing a function. There may be multiple subs in your code that are related to each other.
If the functionality of subs is specific to a certain sheet, it is better to keep the subs on the sheet. However, if you wish to create a reusable code set with multiple code subs that you can export or call from other Excel workbooks, you should put your code in a module.
Also, keep in mind that the sheets in your Excel workbook can be deleted, so the code is at a higher risk of being lost. Decide, based on your specific use case, whether code on a sheet or a module is more suitable for you.
Additionally, any recorded macros in Excel will always appear under Modules with the same name used to define the macro. Macro recordings can be used for simple repetitive tasks in your worksheet.
#3: Setup A Module in Excel VBA
Modules do not form by default when programming with Visual Basic in Excel. You will have to add modules to your workbook to code.
To insert a module, click on the “Insert” Option on the top ribbon and select “Module”. A new module will be inserted linked to the workbook you selected. When you double-click on the module you created, a new blank sheet will open on the right. This is where your code goes.
#4: Saving a Macro-Enabled Workbook
You need to save Excel workbooks that contain macros or VBA code with a different extension. Save your workbook as “.xlsm” instead of “.xlsx” to enable the proper functioning of macros in the workbook.
When saving a file with a macro, Excel will automatically prompt the users to change the extension. You will see an error message as below.
Once you click “Save”, you can select the “Excel Macro-Enabled Workbook” option as shown below.
Conclusion
If you followed this setup guide step by step, you are now ready to explore the multitude of capabilities that you can add to your Excel functionality using Visual Basic code.
Check out more interesting Excel blogs here.
