In this tutorial, you will learn to add a developer tab and “write” macros in Excel using VBA or Visual Basic for Applications in Microsoft Excel.
We have already learned to add macros in Excel from the View tab. If you don’t know how to do it, click here.
Also read: How to Record Macros in Excel?
If you want to learn to record macros using VBA, you’re at the right place.
How to add the developer tab in Excel?
Before you start writing macros using VBA, you need to first add the Developer tab in Excel.
You can use the Developer tab to record and secure macros, manage XML and COM add-ins, use VBA, and few more tasks.
Here are the simple steps to add the Developer tab in Excel.
- Open an Excel file.
- Right-click on the Excel ribbon and click Customize Ribbon.
- Or, go to the File tab.
- Click Options in More on the left.
- Click on the Customize Ribbon tab.
- Check Developer in the list.
- Click OK.
You will see that the Developer tab has been added in Excel.
What is VBA or Visual Basic for Applications?
VBA or Visual Basic for Applications is majorly used to record or write macros in Excel. However, there is a lot you can do with this Office tool.
The VBA tool is accessible in the Developer tab in Excel, which can be added from the Customize Ribbon settings. Follow the aforementioned steps to add the Developer tab in Excel. Or, you can press ALT+F11 or ALT+Fn+F11 to open the VBA window.
The significant uses of Excel VBA include:
- Record macros by code
- Create custom buttons with a custom function
- Create custom add-ins
- Introduce new features and power-up Office apps like Word, Excel, and Access
- Create pricing for analysis and trading in exchange markets
- Real-time trend forecasts and tracking
- Introduce custom complex calculations and more
You can enhance Microsoft Excel to a whole new level with the help of VBA, especially if you’re a developer too.
With basic knowledge of VBA, you can record macros that are useful in executing various tasks like a few mentioned above.
What is “Use Relative Reference”?
Before recording macros, it is important to understand what the Use Relative Reference in the Developer tab means.
When enabled, this tool records actions relative to the initial cell. It makes recording macros a little smarter.
Here is an example of a series of events to help you understand better.
- Selected the Use Relative References option.
- Started recording a macro.
- Colored cell A1 as Red and clicked on cell B1.
- Stopped recording the macro.
- Ran macro on cell D2 in the same sheet.
- D2 is colored Red, and the cursor automatically shifts to cell E2.
If you did not enable the Use Relative References option before recording a macro, the cursor would move back to cell B1 and not on E2, on running the macro. This is because recorded actions are not relative to the initial cell.
How to record macros using VBA?
Let’s get started with recording macros in Excel using VBA. Now that you have learned to add the Developer tab in Excel, we can start recording macros using VBA.
Fundamentally, we are going to “write” macros by code in the VBA application. We will manually write each step with codes in VBA, which is involved in recording a macro in Excel.
Let’s begin to manually write a macro in the backend in Microsoft Excel.
This is how a frontend recorded macro looks like in VBA.
You can see that every action that occurred after recording the macro, is recorded as a code in VBA.
sub – A VBA code begins with a subject line. You can add a custom subject line or an “argument” if you like. If there is no argument, make sure it ends with an empty closing and opening parentheses. We haven’t added any argument for the sub.
“Table Macro” is the name of the macro that you put in before recording a macro.
“Table pasted” is the description of the recorded macro.
You can also see the keyboard shortcut you applied to run this macro as “Keyboard Shortcut: Ctrl+Shift+P”
Notice that these commands are written after a ‘ or single quote because they are considered comments in VBA. This is the reason why they are green-colored.
The running of the macro is written in black without a single quote with a set of syntax.
Each action has a syntax to it. For example, we copied a range and the syntax “Selection.Copy” was executed.
Let’s manually record a macro in the editor, writing each step by code.
- Click on Visual Basic in the Developer tab.
- Click Insert and Module to insert a new module.
- Add a subject. Adding an argument is optional.
Sub testing_macro()- Name the macro.
- Press ENTER and type ‘
‘ Sample_Macro- Write a description. Press ENTER for every new comment or command.
‘ This is a test macro.- Press ENTER and type ‘
- Give a custom keyboard shortcut to run the macro.
‘ Keyboard shortcut: Ctrl+Shift+U
This is how the VBA code should look like so far.
Our goal is to copy this range shown below and paste it into a different cell address in the same sheet.
The selected cell range is where we want to paste this table.
So, this will be the code to select the range I5:K8 first and exactly paste the contents in the range I10:K13.
Range("I5:K8").Copy
Range("I10:K13").PasteSpecial
Application.CutCopyMode = False
The Application.CutCopyMode is the marching ants selection that shows even after pasting the contents of a cell. So, we have set it as False. Meaning, the cut-copy selection won’t show up anymore.
Type End sub at the bottom to complete the code. This is how the code should like before pressing Step Into.
Click Run or Step Into (F8) in the VBA editor to check if the code is working correctly.
We can see that the macro is running correctly in Excel.
Conclusion
That was it! You learned how to code and run a simple macro in Microsoft Excel using VBA. You also learned what VBA is and how to add the Developer tab in Excel.
References: Microsoft, Corporate Finance Institute.