Excel VBA Tutorial – #3 Running Recorded Macros Step-by-step
Last updated on November 5, 2018 By Victor Chan
Summary: Don’t you just hate it when you run a recorded macro and it works so fast you can’t see what it’s doing? Especially when it does something you don’t want it to… why did it do this or that on the sheet? I’ve been there and know the struggle… so I’m going to share some handy tips in this tutorial about running a macro step-by-step to avoid this feeling!
Difficulty: Beginner
Download the Sample Workbook
Download the sample file with VBA code
Stepping_Into_Macros (81 Kb)
#1 – Why it’s great to “Step Into” macros
I’m sure you remember the previous article about Macro Recording. There I mentioned the option where you can “Step into” the macro instead of just running it from start to end.
This option is going to be one of your favorite functions in VBA programming. Why do I say that? Because it’s my own experience – this is the best method to understand and debug your code!
You can save a lot of time by analyzing your code step-by-step only once … much better than running the whole macro again and again a thousand times and trying to figure out why it goes bad.
Don’t let this picture confuse you – it’s from the previous tutorial article. You don’t need these “Recording1” and “Recording2” macros now.
We are going to record new macros that suit our needs better.
#2 – Record two new macros to play back
Macro 1 – Exponentiation
Let’s start with a simple math function macro recording. I took exponentiation as example, but it really could be anything else: adding, subtraction, writing text, etc.
The purpose is purely to populate cells one after another, to observe the “stepping” function in the code.
You can download a workbook with the macro inside it here:
Stepping_Into_Macros (81 Kb)
Or if you prefer to copy/paste the code here it is:
Sub Exponentiation() Range("A1").Select ActiveCell.FormulaR1C1 = "base" Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A2").Select Selection.AutoFill Destination:=Range("A2:A7"), Type:=xlFillSeries Range("A2:A7").Select Range("B1").Select ActiveCell.FormulaR1C1 = "exponent" Range("B2").Select ActiveCell.FormulaR1C1 = "2" Range("C1").Select ActiveCell.FormulaR1C1 = "result" Range("C2").Select ActiveCell.FormulaR1C1 = "=RC[-2]^R2C[-1]" Range("C2").Select Selection.AutoFill Destination:=Range("C2:C7") Range("C2:C7").Select Range("A1").Select End Sub
As you can see, I recorded the whole macro, and the only thing I changed was replacing the green comments under the title. I don’t need them, because the title tells me it’s function.
That’s something I recommend to you as well: name your macros so that their titles tell you their function! Otherwise, you can find yourself in a VBE maze with no exit from strangely named macros
The code is a simple math example I made using the “pull down” function of Excel. This created the “Selection.AutoFill” lines.
I think the picture of the result speaks more than the code…
… Column A has numbers 1 – 6 (this is the base).
… Column B has the exponent: 2.
… Column C has the result (1^2 = 1, 2^2 = 4, 3^2 = 9 etc.)
Macro 2 – Cleaner
Now, if we need a clear sheet to test the macro again, how can we delete these values?
We could do it manually of course, but that’s not why we’re learning Visual Basic.
I would rather record another “Cleaner” macro, that does the work instead of me! Here’s the code I recorded:
Sub Cleaner() Cells.Select Selection.ClearContents Range("A1").Select End Sub
How does this work?
Let’s find out by stepping into this macro!
Download and open up the “Stepping_Into_Macros.xlsm” file, click on the button Macros. Select “Cleaner”, and click on “Step into”.
This happens:
The title turns yellow, meaning that this command is going to be executed when you press “F8”. Actually the title isn’t a real command, so it is just a sign for you that this particular macro is going to be stepped into.
Now, press “F8”.
The next command, “Cells.Select” turns yellow – it is marked for execution on your next “F8” press.
You can see that the yellow line is NOT executed yet. It is waiting for you to continue with F8.
So let’s step again. Press “F8”.
What happened?
The next line turns yellow, yes, but what about the previous command? Well, it is executed: all cells on our sheet got selected, as the picture shows:
One more thing happened along with the selection – the next command in VBE turns yellow.
This is the “Selection.ClearContents” line.
Now we are making progress here, the interesting stuff is going to happen now. Press “F8” again, and notice that all values got deleted from the sheet.
The last command line of the Cleaner macro will select Cell A1.
And if you press “F8” two more times, the macro will be finished. You don’t get any message about a successful macro run, so you should be aware when a macro is stepped until the end.
Why do I say that? Because otherwise you risk that pressing an extra “F8” restarts the macro! For this macro it’s not a big deal, but later on with more complex macros it could cause you a headache.
Keep this in mind: you cannot undo any macro command with Excel’s “Undo” button!
Related Pro Tip:
If you are designing a program in the VBE and you run your code many times for testing purposes, you should always consider using a “Cleaner” macro.
It will save you a LOT of time when you don’t have to “Undo” the actions manually. As you can’t rely on Excel “Undo” function, and you need a blank sheet every time, this is a must have!
Why can’t we use the “Undo” function on macros? Because they are all being processed by the Visual Basic Editor, therefore they are not stored in Excel’s memory. If you want to undo macro actions, you have to use another macro for it, that does the opposite – or works as a cleaner.
#3 – Your turn to use “Step Into”
By now you should be able to run the exponentiation macro. Try it out, I’m sure you are going to like watching the values appearing on the sheet!
I will give you some useful tips that can make your life easier:
- Stepping into a macro always results in slower code execution. The cause of this is that the processor always stops to wait for the “F8” keystroke. If you run a macro normally, the processing happens significantly faster.
- Do not click on the sheet while you are stepping a macro! It complicates things, because if you change the active cell, you get totally different results from what you would expect. The best option is not to do anything in Excel, except pay attention to what’s happening.
- If you bump into any unwanted events while stepping into the macro, and you want to correct it right away, you should first stop and reset the macro! You can do this using the button circled in red:
#4 – Summary
You have been introduced to the macro stepping function – one of the most valuable functions in the Visual Basic Editor.
I’m not kidding, this is my favorite tool when I need to debug my code. It is reliable, makes the code easy to follow, and the process just flows in front of your eyes!
The importance of stepping is this: when you don’t understand why your code doesn’t do what you expect from it, just run it step-by-step, and you will surely find the reason!
Don’t be a fool and think that this is a beginners thing only – professional developers use it every day to debug their programs too.
#5 – About the Author
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.
Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!
Related Posts:
- How to do 10 Hours work in 10 Seconds – using this Powerful Excel skill
- Excel Tutorial: How to use VBA to delete filtered rows (fast!!) … but not delete hidden rows
- Excel VBA Tutorial – #2 Macro recording (including the difference between absolute and relative)
- VBA Case Study #4 – Invoice Generator Macro
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.