VBA is an excellent tool in Microsoft Office suite, including MS Excel application. It can assist you to accomplish a lot of tasks not supported by the native features in Excel. Now, in this article, we will teach you to run VBA code in Excel step by step.
If you intend to write some VBA codes to achieve some of your requirements, you have to firstly figure out how to run VBA code in your Excel. Now, thereinafter, we will use MS Excel 2010 for an instance to show the detailed steps, including how to enable macro, trigger VBA editor and several means to run macro, etc.
Step 1: Show “Developer” Ribbon
- First off, launch Excel application.
- Then, go to “File” menu and click “Options”.
- In the popup “Excel Options” window, turn to “Customize Ribbon” tab.
- Next, in the right side, find and check the “Developer” option.
- Finally, click “OK” to enable the modifications.
Step 2: Change Macro Security Settings
Since Excel comes with macro auto disabled in security settings, you need to first change the settings by following the steps below:
- At first, in Excel main window, switch to “Developer” ribbon.
- Then, click “Macro Security” button in “Code” group.
- Next, in the new window, select “Disable all macros except digitally signed macros” or “Enable all macros” option to your liking.
- Lastly, click “OK” to save the changes.
Step 3: Trigger Excel VBA Editor
Excel VBA editor is the place where you put the code. You can get access to it by 2 means.
- On “Developer” tab, click the “Visual Basic” button in “Code” group.
- Directly press “Alt + F11” key shortcuts to bring up “Microsoft Visual Basic for Applications” window.
Step 4: Add VBA Code
After entering Excel VBA editor, you need to add your code into either a project or a module.
1. Put Code into Existing Project
As you can see, under expanded “Microsoft Excel Objects” folder, you can see the existing projects, including the projects for each worksheet and the one for the current workbook called “ThisWorkbook”.
- To begin with, double click on a project to open its window.
- Then, put the code into it.
2. Put Code into Module
Besides, you can add code to a module by the following steps.
- In the first place, click “Insert” button in toolbar.
- From the drop down list, select “Module”.
- Then, in the new module, you can put your code.
Step 5: Run VBA Code
1. Run Macro in VBA Editor
To run VBA in the “Microsoft Visual Basic for Applications” window, you can just press “F5” key button or click the “Run” icon in the toolbar.
2. Run Macro through Quick Access Toolbar (QAT)
Moreover, you can add macro to Quick Access Toolbar and run it through QAT by the steps below.
- At the outset, go to “File” > “Options”.
- In the popup window, access “Quick Access Toolbar” tab.
- Then, select “Macros” from the list of “Choose commands from”.
- Next, choose the macro in the left side.
- Later, click “Add” button in center.
- After that, click “OK” to save the changes to Quick Access Toolbar.
- Finally, in the main Excel window, to run this macro, you can click the macro button in Quick Access Toolbar, as shown in the following screenshot.
3. Run Macro through Ribbon
In addition, like Quick Access Toolbar, you can add and run macro in ribbon.
- For a start, access “File” menu and hit “Options”.
- In “Excel Options”, go to “Customize Ribbon”.
- Then, create a new group on “Home” tab by “New Group”.
- Optionally, you can rename this group by “Rename…” button.
- Subsequently, select “Macros” in the list of “Choose commands from”.
- After that, pitch on the desired macro in the left side.
- Next, click the “Add” button to add this macro to the new group.
- Eventually, click “OK” to save options.
- Since then, to run this macro, you can click the newly added macro button in ribbon.
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf recovery and outlook repair software products. For more information visit www.datanumen.com