This step-by-step tutorial covers how to insert VBA code in Excel to add and run a macro or a custom function.
This article will provide a beginner’s guide for users who might not have VBA knowledge. Just follow the simple steps of inserting VBA code into your Excel workbook, enabling you to enhance your productivity.
Table of contents:
- What is a VBA code?
- Getting Started: Enable the Developer Tab
- Steps to Insert VBA Code in Excel
- How to Run a VBA Code in Excel
What is a VBA code?
VBA is a programming language integrated into Excel and other Microsoft Office applications. It is a powerful tool for Excel to automate repetitive tasks. VBA is your friend if you work on string manipulation or data cleansing projects. You can build macros and functions to manage large data sets more effectively using small and fast codes.
Getting Started: Enable the Developer Tab
Before inserting a VBA code in Excel, you must ensure the Developer tab is visible. Do not forget: the Developer tab is hidden by default.
How to add the Developer tab to the ribbon in Excel?
- Click on the ‘File‘ tab in the top-left corner of Excel.
- Select ‘Options‘ at the bottom of the sidebar to open the Excel Options dialog box.
- In the navigation pane, click on ‘Customize Ribbon.‘
- In the right pane, you will find the ‘Main Tabs‘ area; check the ‘Developer‘ checkbox.
- Click ‘OK‘ to close the dialog and return to Excel.
- The Developer tab should now be visible in the ribbon.
Steps to Insert VBA Code in Excel
How to insert VBA code in Excel?
- Open the Visual Basic Editor using the Alt + F11 shortcut key
- Right-click on the workbook where you want to insert your code.
- Select ‘Insert‘ from the context menu, then choose ‘Module.‘
- Write or Paste your code
- Press CTRL + S to save your workbook, or go back to Excel and click ‘File,’ then ‘Save As.‘
- In the ‘Save as type‘ dropdown, select (*.xlsm) file format. Regular Workbook formats like *.xlsx will not save your VBA code.
- Choose the location, add a name, and click ‘Save.’
Note: The Developer Tab > Visual Basic command can reach the VBA editor.
Inserting a macro or code is not rocket science. Paste the code into the new module.
How to Run a VBA Code in Excel
You can use multiple ways to run the inserted macro or code.
How to run the inserted VBA code?
- VBA Editor: With the code displayed in the VBA editor, press F5 (this runs the entire code in the module). If you only want to run a part of the code, highlight it, then press F5.
- Run a Sub Procedure inside the editor: Within your code, place your cursor within the sub-procedure you want to run (a sub-procedure starts with ‘Sub’ and ends with ‘End Sub’). Press F5 to run only that sub-procedure.
- Excel: Go back to Excel without closing the VBA editor. On the Developer tab, click ‘Macros’ to see a list of available VBA sub-procedures. Select the one you want to run, then click ‘Run.’
- Ribbon: Call the code from the ribbon. If you create a button in the ribbon, you provide a user-friendly way to execute VBA code.
In addition, here are a few tips to speed up your VBA code.
How to speed up your VBA code
- Disable Automatic Screen Updating: Excel refreshes the screen each time it executes a macro. Turn off the screen updating using this command: “Application.ScreenUpdating = False“
- Turn Off Automatic Calculations: The built-in automatic calculation feature may slow down performance if your workbook contains many formulas. To run your code faster, turn it off at the start of your macro with: “Application.Calculation = xlCalculationManual”. After the execution, you can turn the auto calc option back on using the “Application.Calculation = xlCalculationAutomatic” command.
Conclusion
We hope that you find this guide useful and that inserting a VBA code in Excel is easy for you. Thanks for being with us today. Stay tuned.
Additional resources:
