In this guide, we're going to show you 5 tips for VBA beginners in Excel and get you up to speed in no time!
Saving macros - tips for VBA beginners
Since the 2007 version, Excel has been differentiating between files containing macros based indicated by their file extension (*.xlsm), and warn users about the macro-enabled file that can contain malicious code.
If you start working on an existing, regular Excel file (XLSX), you must save it as an excel Macro-Enabled Workbook to save your codes in it. You can find the option when you click the file type input.
Making macros available on all workbooks
However, macros cannot be used once you close the workbook. What if you need to use macros with your other workbooks?
Luckily, there is an easy way to make your macros available on all workbooks. Excel can create a special workbook called Personal.xlsb, also known as Personal Macro Workbook. Once created, this workbook will be opened in the background whenever you start Excel.
To check whether the file has been created correctly, open a blank workbook and activate the View tab in the Ribbon. If the Unhide button is disabled, then your Personal.xlsb file has not been created.
You can easily create by recording a macro inside it.
- Click Record Macro in the Developer tab or Status Bar. If the Developer tab is hidden, see How to display the Excel Developer tab for using advanced features.
- Select Personal Macro Workbook from the Store macro in list
- Click the OK button to start recording a macro.
- If you do not want to record any action, you can click the Stop Macro button right away.
Once the macro recording stops, Excel creates the Personal.xlsb file. You can either use the Unhide command to make it visible or open the VBA window and access it through there.
Write your macros in Personal.xlsb and save the file in the VBA window.
Recording a macro as a cheat sheet
Our next tip for VBA beginners is recording any other frequently used actions.
For example, let’s say you are looking to find which property you need to change the color of a cell. Start a recording session, change the color of a cell, and stop the recording. Open the VBA window and see what Excel has recorded.
Your actions are essentially executed on the selected cell(s) and you need to access the cell’s Interior and Color properties. This can also help you find out more about how VBA works.
Assigning shortcut key to an existing macro
You can assign keyboard shortcuts to any macro that you have recorded, entered, or even pasted from somewhere else. There is a Shortcut key input in the Record Macro dialog.
- Activate the Developer tab in the Ribbon
- Click Macros
- Select the macro you want to assign a shortcut key
- Click Options
- Type in a character into the small box. Excel will give you the key combination depending on which key you typed in.
- Click OK to assign the shortcut key.
Immediate Window - tips for VBA beginners
Our final tip for VBA beginners is a helper tool in VBA. The Immediate Window is the tiny editor at the bottom the VBA screen. Press Ctrl + G if you do not see it. Use it to see the result of single line codes or checking properties of objects when debugging your code.
For example, you can check a color of a cell for its VBA equivalent.
Alternatively, you can write information given in your code into the Immediate Window. Use the Debug.Print command with the output.
