Once you know how to activate the developer tab, you know what happens when you click Alt+F11 and you know how to create your VBA code. It is time to run your macro.
If you don’t know yet, you can find out how to do it here.
VBA How to run a macro in Excel
Learn how to run a Macro in Excel VBA – Two main options to run the macro:
- Step by step – you are running the macro line by line
This is my recommended option while you are learning how to program.
- End to end – you run the macro fully without stops.
This is the option to go once you know your tool is working as expected.
VBA enable Macros
Why are macros disabled by default in Excel?
For security reasons, macros by default are disabled in Excel.
Easier to understand with an example.
Imagine you have a macro that automatically opens every time you turn on your laptop and checks if one is higher than zero (1>0), and if yes triggers a restart of your laptop.
As one will be always higher than zero (1>0),
you would not be able to operate your laptop as it would be always in restarting mode.
Each time you want to run a Macro ensure you Enable Content.
See the below example of the Security Warning – you simply need to click on Enable content.
This is one of the main reasons why macros do not run for people in Excel.
Always ensure you enable content, so macros will work as expected.
VBA Tool Bar options
The way to access the VBA Tool Bar options is by opening the Microsoft Visual Basic for Applications.
How to do it? Click Alt+F11
This will be your default view when you open Microsoft Visual Basic for Applications.
Do right-click with your mouse next Help (not on Help, but next to see the red x)
The following four options will appear Debug, Edit, Standard and UserForm.
From all these options – we care mainly about two: Debug and Edit.
How to include Debug and Edit options in VBA:
Simply tick both options and they will appear as per below:
Run Excel macro step by step
You may wonder why I would like to run a Macro step by step.
The answer is simple: This is the best way to test that the macro code is behaving as expected.
You can run macro step by step in two different methods:
- Shortcut key F8
- Including Toggle Breakpoint ✋
Shortcut key F8 – how it works:
Every time you click on F8, Macro will be running to the next line of code, omitting the comments. (You can identify the comments in green.)
By pressing F8, you will see how to code is advancing through the lines – skipping the lines in green (the comments)
Toggle breakpoint ✋- how it works:
Select the line of code where you want to stop running the macro.
Click on the hand ✋
After clicking on the hand – you will see a red dot appearing on the screen.
What does it mean?
When you will run the macro (click the green play ▶️),
it will go automatically until the next red dot (and if not red dot until end Sub).
PRO TIP: Split your main screen into two screens.
On the left side, you can place the excel file and on the right side the code in Visual Basic for application.
This way you can see both windows at the same time, while running the code and confirm that the code is really doing what you expect the code to do.
Best way to see it is with an example:
Run Excel macro end to end
That would be the option to go once you are sure the macro is behaving as expected.
Two main options to run the macro
- Shortcut key F5
- Green Play button
Both options are doing exactly the same, when you are pressing F5 or play,
the macro will run from the beginning of the macro (whre you can find the first Sub), until the end of the macro, where you can find the End Sub.
This is very helpful while you are testing your macro tool end to end, so you can ensure your macro is working as expected.
Once you are sure your macro tool is doing what you expect,
the natural next step would be to create a button ⬜️ assigned to the macro that you want to run.
You click the button ⬜️ – and the macro runs until the end. Simple.
Goods stuff, at this stage you are your macro is working as expected – and you want to activate it in an easy way.
Instead of going each and every to Alt + F11, you may want to have a button in the excel file that you click and the magic happens.
This is how to do it:
Go to the Developer tab and click on Insert
Select the shape you would desire – in my case a square – and draw it in excel.
Assign the macro that you want to run after clicking the button and press OK.
There you go! – you have a button connected to the macro.
Remember to save the macro always as Excel Macro-Enabled Workbook
Comment/Uncomment code in VBA
PRO tip: Every time you program a macro – always comment it.
What does it mean to comment a macro?
It does not mean saying “Ey what’s up, how are you doing today”
It means indicating what each line of code stands for.
As an example: ‘Clear content in the cell selected or ‘Select cell B2
How do identify the comments in a macro?
You can identify the comments as they are in green colour and always start with ‘
There are two main ways to comment on a code in macros:
- Using “ ‘ ”
- Using the below commands in the edit section
Will the code Macro run on the comments?
No, the macro will not run on the comments, they will be omitted.
You can write whatever you want in the comments, it will not impact your macro.
Why is it important to comment on the code?
Whenever you need to read/modify the macro code,
you will easily understand what each line is doing, and you will not have to waste time trying to understand what you did back in the past.
It allows continuous improvement, if you need to modify a macro you did not originally program yourself – trust me.
You will be thankful if the macro has comments for every line. Otherwise, it can be a huge nightmare to decode the macro.
If you want to learn more about how to build your first macro – keep reading here.