Today let’s take a look at using the Immediate Window in the VBA debugging environment. This environment can be used as a type of scratchpad or sanbox areas and has many uses as well as debugging code.
If you cannot see the Immediate Window, you just need to enable it. It really simple.
- Open the Visual Basic Editor
- 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 Immediate Window.
Other Ways To Run A Macro
- Hit F5
- Hit the Run Command Button In the VB Editor
If you want to download the example workbook from this blog post you can do so right here.
Don’t forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.