Hello, Excellers welcome to another #MacroMonday #Excel blog post in my 2021 Excel Macro series. Today let’s take a look at using the Immediate Window in the Excel VBA debugging environment.
This environment can be used as a type of scratchpad or sandbox area and has many uses as well as debugging code.
You will need to first Enable The Developer Tab if it is not already enabled. You can watch my YouTube Video for instructions.
If you cannot see the Immediate Window, you just need to enable it. It is really simple.
- Open the Visual Basic Editor
- View
- Immediate Window
Or if you are mad for shortcuts, then you can do the following
- ALT+11 to open the Visual Basic Editor
- CTRL+G to open the Immediate Window and place the cursor in it
You are all set. The window looks quite bland and basic at first glance but it is really really useful for example you can
Get Some Information About Your Current Excel Workbook
We can ask questions in the Immediate Window and expect an answer, we just need to use a questions mark ‘?’. For example by typing in the window ?Worksheet.Count we are correctly told that there are 4 worksheets in the current workbook. Cool.
Or how about finding out what the contents are of an active cell on my worksheet?. Just type ?Activecell.Value
Execute A Line Of VBA Code And Get Immediate Results
You can also just execute one piece of VBA code from the Immediate Window. Just remove the ? from the beginning of your statement and the code will run. Let’s take a look at an example. We can make all of the hidden worksheets visible by typing this code in the Immediate Window.
This piece of code will loop through all of the worksheets and set their visible property to TRUE. GreatJob!. Hopefully this gives you a tatse of the power of the immediate window.
Run A Macro From the Immediate Window
So, as stated at the top of this blog post let’s run a macro from the Immediate Window. It’s really simple again, we just need to type the name of the macro right there in the Immediate Window. Here is the macro to again unhide all of the worksheets in my workbook or making then visible (however you prefer to say it!). My macro is called Unhide_Multiple_worksheets.
All I need to do is type the name of the macro into the Immediate Window, hit return and the macro will run. Want t0 try another?.
Cool, how about this one that will take the contents of Cell B3 and change the font to Cabibri and Font Size 26. Same way again we can type the name of the macro and runs it right from inside the window.
Other Ways To Run A Macro
- Hit F5
- Hit the Run Command Button In the VB Editor
Don’t forget to sign up for the Excel at Excel Newsletter for 3 free Excel tips on the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
How To Excel At Excel – Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST.