79

5 Ways to Use the VBA Immediate Window in Excel

The VBA Immediate Window is an awesome tool that allows you to get immediate answers about your Excel files, and quickly execute code.  It is built into the Visual Basic Editor, and has many different uses that can be very helpful when writing macros, debugging code, and displaying the results of your code.

VBA Immediate Window Excel Worksheets Count

Every Excel user can benefit from the Immediate Window, even if you’re not writing macros.  This post will explain 5 different uses for the Immediate Window.  Once you understand the capabilities of this tool, you will find yourself using it all the time.

Where is the Immediate Window?

The Immediate window is located in the Visual Basic Editor window.

Open Visual Basic Editor and Immediate Window Excel Mac

The fastest way to get to the Immediate Window is to:

  1. Press Alt+F11 (hold Alt key, then press F11 key) from anywhere in Excel.  The Visual Basic Editor window will open.  (Mac version is Fn+Alt+F11)
  2. Pressing Ctrl+G opens the Immediate Window and places the text cursor in it.  Begin typing your code.  (Mac version is Ctrl+Cmd+G)

When you open the VB Editor (Alt+F11) you might see the Immediate Window automatically appear in the bottom right corner.  This is its default location.  If it’s not there you can press Ctrl+G or View menu > Immediate Window.

This Blank Box is Magical!

When you click inside the Immediate Window you will just see a blank box with the text cursor flashing.  At first glance this doesn’t look too exciting, but the Immediate window can be a very powerful and useful tool.

What Does the Immediate Window Do

Think of it like a blank cell in a worksheet.  It’s pretty boring until you add a formula to it, right?  Well the Immediate Window is very similar, so let’s look at 5 examples that will help you get the most out of this magical box.

#1 – Get Info About The Active Workbook

The simplest use for the Immediate window is to quickly get information about the workbook that you currently have open and active in the background.  You can evaluate any line of VBA code in the Immediate Window, and it will immediately give you the result.

For example, to find out how many sheets are in the active workbook, type the following line of code in the Immediate window and then press the Enter key.

?Activeworkbook.Worksheets.Count

The answer will be displayed on the next line of the Immediate window, directly under the code.

VBA Immediate Window Excel Worksheets Count

Ask a question, any question…

Putting the question mark (?) at the beginning of the statement tells the Immediate window that we are asking it a question, and expecting a result.

The following screencast shows a few examples of how we can use the Immediate window to get the value, number format, and fill color of the active cell.

Excel VBA Immediate Window Question Mark Examples

Notice that the Immediate Window displays the intellisense as I type.  Intellisense is the drop-down menu that displays the properties, methods, and members of the object I’m referencing.  This makes it very fast and easy to type code in the Immediate Window.

You can download the free sample workbook that contains a few more useful examples.

VBA Immediate Window Examples.xlsm (109.9 KB)

#2 – Execute a Line of VBA Code

You don’t have to write a whole macro if you just need to perform one line of code to your workbook.

Remove the question mark at the front of the statement and the Immediate Window will execute or perform that line of code.

VBA Immediate Window Execute Line of Code in Excel
Selection.HorizontalAlignment = xlCenterAcrossSelection
The image above shows how to format the selected cells with the Center Across Selection alignment.

You can also use the following line of code to make a worksheet “very hidden”.

Worksheets(“Sheet1”).Visible = xlVeryHidden

Another example is to hide the contents of a cell by making its font color the same as its fill (background) color.

Range(“A1”).Font.Color = Range(“A1”).Interior.Color
Hide Cell Contents with Font and Interior Color

I use this line of code in Tab Hound’s Table of Contents tool to hide some settings stored in cell A1.  Even if the user changes the fill color of the sheet, the contents in cell A1 will still be hidden after the code is run.

#3 – Run a Macro

You can run a macro from the Immediate Window by typing the name of the macro (procedure), and pressing Enter.

Of course you can also do this by pressing the F5 key or the Run Macro button in the VB Editor, but what if your macro contains arguments?

A macro cannot be run from within the procedure if it contains arguments.  However, you can call the macro from the Immediate Window.

The example below is a simple macro that enters the current date (Now) in the cell , and changes the font color to blue (Color = vbBlue).  This macro requires two arguments to be passed to it to run, the worksheet name and cell address where the date stamp will be entered.

Run VBA Macro from Immediate Window with Arguments Excel

For a macro like this you will typically be calling it from another macro and specifying the arguments in the macro that is calling it.  But if you just want to test the macro that contains arguments, you can use the Immediate Window to call it.

This is great for writing and debugging code.  You might not want to run the entire stack of procedures (macros) in the code, but you can use the Immediate Window to only call that specific macro you’re working on.

The example above shows how you can specify the arguments after the macro name.  For arguments that are string variables (text), you will need to wrap the variable in quotation marks.

As you can see in the image, the intellisense is available in the Immediate Window, which makes it easy to specify the arguments for the macro.

The code in the image above is included in the free sample file you can download below.

VBA Immediate Window Examples.xlsm (109.9 KB)

#4 – View Debug.Print Info

Have you ever seen VBA code on the internet that contains a line similar to the following?

Debug.Print xxxxx

With that “xxxxx” being some variable that the code is calculating or producing.

Debug.Print is telling VBA to print that information in the Immediate Window.  This can be useful when you want to see the value of a variable in a certain line of your code, without having to store the variable somewhere in the workbook or show it in a message box.  It is especially useful when you are writing or debugging code.

The example below is a macro that loops through all the sheets in the workbook and checks to see if each sheet is empty (not used).  If the sheet is empty then it is listed in the Immediate Window using Debug.Print.

Excel VBA Immediate Window Debug Print List All Empty Sheets Example

The ultimate goal of this macro may be to delete all empty (blank) sheets in the workbook, but we might want to test the code first before actually deleting any sheets.  The Debug.Print line creates a list of empty sheets in the Immediate Window, and we can then manually check each of those sheets to make sure they are really blank.

#5 – Get or Set a Variable’s Value

The Immediate Window can also be used to get answers about the procedure (macro) that is currently running.  If you are stepping through your code (F8) or add a break point (F9) or add a STOP line in your code, then the code will pause.  When the code is paused you can use the Immediate Window to get information about any variables or objects that are referenced in the code.

The following example is a macro that creates a list of all the sheets in the active workbook.  There are plenty of different ways to write this code, but in this example I use the “lSht” variable in a For Next loop to loop through the worksheets and then add the sheet name to the active sheet.

Excel VBA Macro List All Sheets - Evaluate Variable Immediate Window

I added a break point (F9) in the code to pause the code when that line is executed.  With the code paused, the Immediate Window can be used to evaluate or change variables.  In the image above I used the question mark to check the value of the lSht variable.  Then I used the variable to get the sheet name of the sheet that is currently being processed in the loop.

Finally I changed the lSht variable to 5 by using the equals sign (lSht = 5).  This will effectively skip some of the sheets in the loop because I changed the variable from 2 to 5.

This is a simple example, but the Immediate Window can come in handy when stepping through code.

Undock the Immediate Window

Pete asked a great question in the comments below.  He wanted to know how I made the Immediate Window float on top of the Excel application in some of the screenshots.

This screencast shows you how you can undock the Immediate Window.

Docking the VBA Immediate Window

Here are the step-by-step instructions:

  1. Left-click and hold on the top bar of the immediate window.
  2. Drag it out of the VB Editor window. The immediate window becomes a free floating window that you can put on top of Excel.
  3. To re-dock it, double-click on the top bar of the immediate window.

You can do this with any of the windows in the VB Editor including the Project, Properties, Locals, and Watch windows.  Many developers will setup their VB Editor differently than the default layout.  Some prefer to have the project and properties windows on the right side instead of the left.  The VB Editor window gives you a lot of flexibility to customize your view.

Download

You can download the workbook that contains the code for all these examples in the link below.

VBA Immediate Window Examples.xlsm (109.9 KB)

Conclusion

The Immediate Window is a very versatile tool that can really help when writing and debugging macros.  It’s a great way to get some quick answers about your file or application.  If you are new to VBA, the Immediate Window will be very useful as you start learning and writing code.

What do you use the Immediate Window for?  Please leave a comment below with your suggestions, or any questions.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 79 comments
Sandeep Kothari - June 3, 2018

Superb!

Reply
Daniel Agüero - May 27, 2018

Jon, Thanks a lot for your information.
I think it is very interesting and useful.

Greetings from Santiago of Chile. 27-05-2018

Reply
Richard - May 25, 2018

Hi Jon,
I have been writing a 2D interpolation macro and have been struggling with a particular bit (nothing to do with the maths). If I pass a range which is a part of a row i.e. it has only one dimension then fine .Value2(1) = 1, .Value(2) = 2. If I pass a 2D range and then pick a row or column with the .Rows() method then the resulting range still has 2D even though one dimension only has one element (e.g. .Value2(1,1) = 1, .Value(1,2) = 2 etc). How do I take a range and pick a row or column such that the result only has one dimension? I hope that makes sense.
Thanks very much
Richard

Reply
Sam Sammito - April 2, 2018

Hi Jon,
It’s great to see that for the most part every time I google info on VBA code you come up. I made a good decision to enroll in your VBA Pro course. I’m looking to find the row and column of the cell that my loop ends in, not it’s value. is there a way to do that?
Thanks

Reply
    Jon Acampora - April 2, 2018

    Hi Sam,
    Thank you for your support, and for joining the course!

    I have a post & video series on finding the last used row and column, and there is also training on this inside the course.

    You can use the Row or Column property of any of those methods to return the row and column numbers. The methods also return a reference to a cell, and you can set a variable to it. You can also use the Address property to return the address of the cell.

    As an example, the following example would return the address of the last used row in column A to a variable.

    sLastCell = Cells(Rows.Count, 1).End(xlUp).Address

    That line of code is going to run on the Activeworkbook and Activeworksheet, because it does not specify a workbook or sheet.

    Here is a video with more info on those critical assumptions.

    I hope that helps.

    Reply
te verde - August 12, 2017

Do you have any video of that? I’d love to find out more details.

Reply
    Jon Acampora - April 2, 2018

    Thanks for the suggestion! I’ll add it to the list for future videos. 🙂

    Reply
Bing - May 12, 2017

Hi Jon,

When I tried to undocking the immediate window back, for some reason, the immediate window covered the whole screen and VBA editor is gone! I tried to open from different modules but still can get both VAB editor and immediate window showing up at the same screen.

Appreciate your time and your help.

Thanks,
Bing

Reply
santhosh - April 10, 2017

I have created a GUI using VBA for an EXCEL sheet. and I have created a command button in EXCEL sheet to launch GUI where I can give inputs and see outputs of the calculation.

Now the problem is when I am launching the GUI using the command button I have created all input text boxes are empty. is there any way I can see all input values which I have given previously as soon as launch the GUI.

Thanks and Regards
Santhosh.

Reply
pankaj - March 25, 2017

thanks

Reply
Manoucher - March 22, 2017

So many thank jon,
for many years i use immediate window in VBA but i just use it for print debug now i realized how to manipulate variable in debugging and step ruining of subroutines. i found it helpful.
By the way do you know a way for add some data in a code a force the VBA to read data and set some variables.

Manouchehr

Reply
Krishna - March 21, 2017

Hi Jon,
I need to display a msgbox with some text like “Total count is” and some value which is a count from one of the collections in the macro. How can I achieve that?

Here is my code:

Public Sub CreateCollCustomers()
Dim collCountries As New Collection

Dim sheetRead As Worksheet
Set sheetRead = ThisWorkbook.Worksheets(“Customers”)

Dim iRow As Long

For iRow = 1 To 5000
If sheetRead.Cells(iRow, 2) = “United States” Then
collCountries.Add sheetRead.Cells(iRow, 1)
End If
Next iRow
MsgBox (“Total number of customers from US is ” + collCountries.Count)
‘Debug.Print collCountries.Count

Dim i As Long
For i = 1 To collCountries.Count
Debug.Print collCountries(i)
Next i
MsgBox collCountries.Count
End Sub

Reply
    Jon Acampora - March 29, 2017

    Hi Krishna,

    You can concatenate or join a string of text before the Count using the ampersand.

    MsgBox "Total count is " & collCountries.Count

    I hope that helps.

    Reply
Ray - February 2, 2017

Great Explanation Jon easy to follow.
but my Imideate window does not work, it may have to do with the fact that my (Righthand mouse key) copy or paste do not work eather . Do you have a Idea how I can get them back Working?

Ray

Reply
    Jon Acampora - February 4, 2017

    Hi Ray,
    There could be a lot of things causing that. It might be an issue with the operating system or hardware. It’s really difficult for me to say.

    Reply
Shane - February 1, 2017

trying to execute code, an uncertain how, pressed F8 to run through Private Sub and not working can someone help?

How to you run a Private Sub, and execute each line???? It is not allowing me to Step into by pressing F8

can this be called from immediate window?

Private Sub upload_fed_expenses_batch()

Reply
    Jon Acampora - February 4, 2017

    Hi Shane,
    If you are using a laptop then you might need to press the Fn key with F8. Here is an article on the best keyboards for Excel with more info on that.

    You can also step through the code by pressing the Step Into command on the Debug menu. Keyboard shortcut is Alt,d,i.

    I hope that helps.

    Reply
raja - November 26, 2016

thanks so much. I never thought that imm. window is so useful. Fine explanation.
I need to print 11 clmns. wide 8rows down section on the same a4 sheet .need a vba code for this. pl.help.
thank you so much for your great service to the excel enthusiasts.!

Reply
    Jon Acampora - November 28, 2016

    Thanks Raja! You can use the Resize property of the Range object for that.

    Range("A4").Resize(8,11)

    I hope that helps.

    Reply
Big Al - November 4, 2016

Expertly explained for a Excel VBA simpleton like me who is looking to expand on the basics.

Jon….. YOU DA MAN!!

any other useful sources you can give a beginner like me would be greatly appreciated.

Reply
    Jon Acampora - November 14, 2016

    Thanks Big Al! I really appreciate your support! 🙂

    Reply
Tom R - October 27, 2016

Jon, notice all the forum complaining about excel macros running very slow on win * & 10… I also have that issue, now. I’ve done all the VBA commands to run faster to ‘false’ , cleaned up my registry, repaired Office 2016 countless times, removed pre-fetch… etc…

my question is:
when running a macro, (processing 100,000s rows) when the cursor is in the ‘active worksheet window’ i.e.(tab) the macro speed is ‘slow’ when I move the cursor off the ‘tab’ to the ‘Windows Tray line’ (below statusbar outside of excel window) the macro speed increases by 5-6x. (i.e. in the ‘Tray’ portion of the screen I get 6,000 rows a min processed, in the active window I get ~800 rows. is there some ‘window state’ that excel use to recognize where the cursor is and due to all the new options, SKYPE, YAMMER, O365, etc. something in the registry get changed?

seeing that MS isn’t address these issues, thought it worth the email on your thoughts….

TIA Tom

Reply
    Jon Acampora - November 1, 2016

    Hey Tom,
    That is very interesting behavior. I don’t have any experience with that issue yet. The forums are probably going to be the best place to post the question or look for solutions. You can use Windows API calls in VBA to determine the window state and cursor position. My best guess would be that the Quick Analysis or Screen Animations have something to do with it. Hopefully MS will address the issue.

    Reply
Sureshkumar R - October 20, 2016

Really very useful information.. helps a lot.. Thank u so much.. for ur continues support.. 🙂

Reply
Thangavel D - September 26, 2016

Great info…thank you so much!!!

Reply
Eric Ruiz - August 25, 2016

?myMind.isBlown
True

Reply
Sabareesh - August 25, 2016

Hi Jon

I want to move all the output in the immediate window to a text file.

Debug.Print M.SubMatches(1) ‘ write to immediate
Print #n, M.SubMatches(1) ‘ write to file
My Immediate Output:

Avatar
Mission
Expendables

My Text file has the last value only.

Expendables

I want to move all the values displayed in immediate window to a text file.

Please Help!

Thanks!

Reply
    Jon Acampora - August 26, 2016

    Hi Sabareesh,

    You will need to concatenate or join the string that is being created by your SubMatches macro. If it is an array then you can use the Join function to the items by a delimiter. I hope that helps.

    Reply
sumanth - August 14, 2016

how to pull text from another window?
i also tried getwindowtext(),
but it gets window title
not the window inside text

can u please help me….

Reply
    Jon Acampora - August 14, 2016

    Hi Sumanth,
    I’m not sure which window you are referring to.

    Reply
Jim - August 10, 2016

HELLO RON:
I’M 92 OF AGE AND SLOWING DOWN AND TRYING TO LEARN VBA. I’M HUNG UP WITH A PROBLEM THAT AFTER ALT F11 THE DISPLAY OF THE EDITOR DISAPERS IN LESS THAN ONE SECOND!?
JIM

Reply
    Jon Acampora - August 14, 2016

    Hi Jim,
    Sorry, I’m not sure what would be causing that. You might want to try opening it from the Developer tab in the ribbon by pressing the Visual Basic button.
    Thanks,
    Jon

    Reply
Georg - July 21, 2016

I have a problem:

The contens of the Imediate windows is as table from debug.print with From….next loop.

It is possible to copy a content of Immediate window to the variable as a string by code VBA in Excell?

I would like to use this copy/string in to the code as a contens of e-mail.
I have the code to send e-mail from module VBA Excell but I don’t know how to do that the contens of e-mail as score of For…next will be write in several rows like in Immediate windows. If I use the For…next in code to send e-mail the next row overwrite existing row and in e-mail I see only one row with last data of loop.

Thanks for help

Best Regards

George

Reply
    Jon Acampora - July 31, 2016

    Hi George,
    You can concatenate or add to the string in the loop. The following code would produce the string 12345.

    For i = 1 to 5
       sText = sText & i
    Next i
    

    You can also use the constant VbNewLine to add a new line within the string. I hope that helps get you started. Thanks!

    Reply
Rocky - June 21, 2016

Its great illustration of Immediate Window usage. Thanks for sharing

Reply
Malina - May 31, 2016

Brilliant as always 🙂

Reply
Michael Fate - May 25, 2016

I realize this is an old post, but I thought it would be helpful to chime in on the docking of the immediate window. Your instruction was straightforward, but now I can’t get the immediate window to dock again. Or to be more precise, I can’t get it to dock at the bottom like it was before I undocked it.

There is a registry hack to correct this but it sure would be nice of I didn’t have to do the hack to get it back into position.

Otherwise, thanks for the great information on the immediate window and it’s many wonderful uses.

Reply
    Jon Acampora - May 30, 2016

    Hi Michael,
    You should be able to get the Immediate Window to redock at the bottom by dragging it over the bottom of the VB Editor screen. When you left-click and hold the Immediate Window and drag it over the very bottom of the VB Editor window, the dotted outline will expand horizontally across the bottom. Release the left mouse button to dock it. Here is a quick screencast that shows it.

    WDock Immediate Window to Bottom of VB Editor Screen cast

    I hope that helps. Thanks!

    Reply
    GARCON - August 16, 2016

    Double-click header row to dock/undock 🙂

    Reply
      Jon Acampora - August 20, 2016

      Great suggestion Garcon! Double-clicking the header will re-dock to the last position that the window was docked to. So if the Immediate window was docked to the right or left side, it will re-dock to that location when it is double clicked. Just wanted to clarify for anyone else reading this. Thanks for the comment! 🙂

      Reply
Andres - Perú - May 2, 2016

Excelente aporte….

Reply
Mjo - December 9, 2015

Brilliant tutorial – immediate window has always confused me, will certainly start using it now. Running macros with arguments and single lines of code will be so helpful!

Can you ask questions (tip 1) whist in break mode?

Reply
    Jon Acampora - December 9, 2015

    Thanks Mjo! Yes, you can ask questions while in break mode. You can also use the Locals window to see the values assigned to your variables while in break mode. Please let me know if you have any other questions.

    Thanks! 🙂

    Reply
VBA code to compare two cells for same value not working 100% - December 1, 2015

[…] document.write(''); First off, make sure your variables are referring to the correct cell… It looks like CellA is referring to cell "D31" and CellB is "G31", but you're changing cell "G32" to "Pick a value…" Secondly, to walk through the code, make sure your cursor is in the macro you're wanting to run, and hit F8. Each time you hit it, it will run the highlighted code, then move to the next line. Once the process sets the variables, you can mouse over the variable (the actual text) and it will show you what it's set to. You can also play around with code in the immediate window to see exactly what the code will do. This is a great website that explains some very handy methods for using the Immediate Window: 5 Ways to Use the VBA Immediate Window – Excel Campus […]

Reply
Skratt - October 8, 2015

GREATTTTTTTT tutorial,

I am already sure I’ll really save some of my time in the future

Reply
Yogesh Kumar - September 1, 2015

May I know , how to print excel row data line by line ?
I want to print a row data in one line and next row data should be print in next line in immediate window or Is there any way to print entire excel sheet data in tabular form in immediate window. Please let me know if this is possible. Thank you in advance.

Reply
    Jon Acampora - September 1, 2015

    Hi Yogesh,

    Yes, it is possible. You will need to loop through the rows and print each row, or load the range to an array and print the array.

    Reply
      Yogesh Kumar - September 1, 2015

      Can you please give me some tips that how to loop all the rows and print each row or load the range to an array and print the array.

      Thanks in advance

      Reply
        Jon Acampora - September 2, 2015

        Hi Yogesh,
        I have a free video training series on macros & VBA that covers loops. Here is a link to the first video. In the second video we cover how to write a For Loop. Please let me know if you have any other questions.
        Thanks again!

        Reply
          Yogesh Kumar - September 6, 2015

          Thank You Very Much. Tutorial video is very helpfull to read data line by line. This video helped me to raed excel rows line by line but I am wondering that is there any way to read only even number columns from a workbook ? So please help me.

          Sub show1()

          Dim Arr() As Variant
          Arr = Range(“A1:J12”)
          Dim R As Long
          Dim C As Long
          For R = 1 To UBound(Arr, 1)
          strnewRow = “”
          For C = 1 To UBound(Arr, 2)
          strnewRow = strnewRow & ” ” & Arr(R, C)
          Next C
          Debug.Print strnewRow

          Next R
          End Sub

          Reply
        Skratt - October 8, 2015

        Thanks YOGESH for sharing,

        that’s something that has bothered me a bit recently as I started to work with array and had issues to debug (not having an IT education)

        Reply
German Rauhut - July 8, 2015

How to empty the window via VBA code would interesting as well, since running a macro several times will always append Debug.Print information.

Reply
    Jon Acampora - July 8, 2015

    Great question German! Unfortunately it’s not a very straightforward process. There are some workarounds using sendkeys and also this solution posted on Daily Dose of Excel. Read the comments on that page before using the code. Some people have issues with it. I agree that it would be a “nice to have” feature though. Thanks!

    Reply
Lucas Gouveia Benvenutti - June 2, 2015

goooooooooooooooooooooood

Reply
    Jon Acampora - June 3, 2015

    Thank yooooooooooooooooooooooooooou! 🙂

    Reply
ABHISHEK - February 20, 2015

very good tips

Reply
Pete Crich - January 23, 2015

In your illustrations you have the immediate window sat on top of a sheet, how do you do this or is just to illustrate what happens when you do the code?

Reply
    Jon Acampora - January 24, 2015

    Hi Pete, That’s a great question! Yes, you can have the immediate window sit on top of the Excel window. You just need to drag it out of the VB Editor window. I added a section to the blog post above with step-by-step instructions and a screencast video so you can see it in action.

    Please let me know if you have any other questions.
    Thanks!

    This also

    Reply
Jon Acampora - October 24, 2014

Here’s one I used today that removes the page break lines on the sheet.
ActiveSheet.DisplayPageBreaks = False

Reply
steffan - October 2, 2014

Q mark equaling a “question” for the immediate window: The Q mark is actually a shortcut for “Debug.Print” and functions this way in many programming languages.

For hiding the contents of a cell: Rather than making the text color the same as the fill color, a custom number format like “;;;” will hide the contents of a cell without the need to adjust it at any point, even if font color and fill color are tweaked. Another bonus to this type of formatting is that cells containing data points formatted this way won’t show up on a chart either, so it’s possible to use this to hide points or even series names based upon conditions.

Reply
    Jon Acampora - October 8, 2014

    Hi Steffan,
    That’s a great tip about the number format! I will definitely be using that one in the future. Thanks for sharing!

    Reply
      steffan - October 9, 2014

      My pleasure.

      Reply
    Jan Karel Pieterse - October 9, 2014

    Minor quibble: Some spreadsheet designers consider it bad practise to hide things in an Excel model; they make it hard to audit the spreadsheet also make it easier to break things (after all, you don’t see there is something in the cell, so why not use the cells for something else).

    Reply
      steffan - October 9, 2014

      I certainly agree with the auditing comment. I strive to be clear about what’s going on in most reports that I make, because I’ve been frustrated before by a transformation that occurs on a hidden worksheet or something to that effect. But on the other hand, Excel is used to emulate the look and functionality of a lot of different BI tools these days, and sometimes the only way to get to a report described by your audience is to bend the normal thing a bit.

      For my own part, I will usually have a “read me” tab in any workbook that has macros, data connections, hidden cells, hidden rows, hidden columns or hidden sheets that describes the location and function of any of these things, especially if I’m using them in an unusual way.

      Reply
    Skratt - October 8, 2015

    Nice Tip !

    Reply
Jan Karel Pieterse - September 29, 2014

I’d like to add an item to your list:

#5: execute a small macro

Type this into the immediate window and hit enter:

For Each sh In Worksheets:Debug.Print sh.Name:Next

Reply
    Jon Acampora - September 29, 2014

    That’s a great one Jan Karel!

    Jan Karel’s suggestion allows you to run a multi-line macro in the immediate window by using the colon “:” as a line separator.

    In a normal macro his code would look like the following:

    For Each sh in Worksheets
    Debug.Print sh.Name
    Next

    However, you can condense it down to one line using the colon between lines.

    Here is another example that would unhide all the sheets in the workbook.

    For Each sh In Sheets:sh.Visible=True:Next

    Thanks for this awesome suggestion Jan Karel! And thanks for stopping by! I can’t tell you how much I’ve learned from Jan Karel’s site at http://www.jkp-ads.com/

    Reply
      Jeff Weir - September 29, 2014

      I always knew Jan Karel would trot out his colon party trick sooner or later. 🙂

      Seriously though, great trick Jan Karel.

      Reply
        Jan Karel Pieterse - September 30, 2014

        Thanks guys. I use that a lot when I need to get some quick and dirty things done, like getting a list of all worksheet names or aligning a bunch of shapes horizontally

        Reply
Excel Roundup 20140929 « Contextures Blog - September 28, 2014

[…] If you do any programming in Excel, read Jon Acampora’s tips for using the Immediate window. […]

Reply
Jeff Weir - September 23, 2014

Great tutorial, Jon.

Reply

Leave a Reply: