The VBA Immediate Window is a great tool that can help any Excel user, even if you are not writing macros. Learn how to get answers about your Excel file, quickly run macros, debug your code, and more. Free file to download contains VBA code samples.

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.

86 comments

Your email address will not be published. Required fields are marked *

  • I just remembered another small observation… Once in a while suggesting Ctrl+G to get the Immediate window has caused some confusion. I can understand that Ctrl+G is valid to say as one sees “Ctrl” and “G” typically on the keyboard. But once in a while somebody has taken this as meaning Ctrl+Shift+G, ( or Ctrl+Shift+g), So I tend to say Ctrl+g instead. ( This also helps me to remember the VBA code ( ran from the VB Editor ) to chuck up the Immediate window, which is Application.SendKeys keys:=”^g” )

  • Hello Jon Acampora,
    This is a very nice clear and concise Blog. I would like to pass it on to people when I try to explain the immediate window.
    Some comments:
    _1) You also have some interesting comments. On some other similar Blog sites , I can get a “deep link”, that is to say a URL that takes me to a specific comment. A suggestion for the future, if it were possible, would be for you to introduce such a feature: ( At other sites, you typically get that “deep link” if you click on the date next to the person’s name who commented ) (Jan Karel Pieterse added this feature to his existing comments a few years back quite quickly, so maybe he might have an idea how to do it )
    _2) Just out of interest what I find the Immediate Window very useful for:
    _2a) Sometime I try to build up some complicated formula strings in VBA to then past into a cell or use within the VBA Evaluate(“ “). Often there are some nasty long combinations of quotes , strings, VBA variables etc.. getting the syntax correct can be assisted greatly I find by pasting my string into the Immediate window ( Debug.Print MyStringVariable ). If I have my syntaxes correctly, then in the immediate window I will see the correct final formula string. I find that much more efficient then pasting into the cell whilst developing the formula, as I can easily see a mistake in the string and correct it. Often by pasting into a cell it is more difficult to see the mistake.
    _2b) I often need to translate formulas from German to English Excel. Using the Immediate window with simple commands like these is a nice way to get a formula translation which I can then copy from the Immediate Window
    ?range(“C15”).Formula
    ?range(“C15”).FormulaLocal

    Alan

    • Howdy! Quick question that’s entirely off topic. Do you know how to make your site mobile friendly? My weblog looks weird when browsing from my apple iphone. I’m trying to find a template or plugin that might be able to correct this problem. If you have any suggestions, please share. Thank you!

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

    Greetings from Santiago of Chile. 27-05-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

  • 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

    • 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.

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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()

  • 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.!

  • 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.

  • 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

    • 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.

  • 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!

    • 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.

  • 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….

  • 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

  • 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

    • 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!

  • 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.

    • 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!

      • 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! 🙂

  • 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?

    • 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! 🙂

  • […] 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 […]

  • 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.

      • 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

          • 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

        • 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)

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

      • I thought this was great code and works flawlessly when Manually executed:

        Sub ResetImmediate()
        Dim icount As Integer
        ‘Debug.Print String(5, “*”) & ” Hi there mom. ” & String(5, “*”) & vbTab & “Smile”
        Application.VBE.Windows(“Immediate”).SetFocus
        Application.SendKeys “^g ^a {DEL} {HOME}”
        DoEvents
        End Sub

        The problem is that when “Runnning” the code it doesn’t work at all. Any suggestions.

        • I also have been getting inconsistent results with VBA coding to control the immediate window.
          At the moment I am having some success with procedures of the following form. In addition to the coding forms given , I also find that I may need to add a code line such as
          DoEvents: DoEvents
          after things like message boxes or shapes. ( That code line, DoEvents: DoEvents , is not a typo: often you need a double DoEvents to ensure that the immediate window, or the message box or shape appears, or disappears as it should. )
          Some times you may need the double DoEvents before or after things, or some combination thereof. But it will take some experimenting, as sometimes a DoEvents or two in the wrong place can have the opposite effect and stop a previously working code from working!!!!
          http://www.eileenslounge.com/viewtopic.php?f=30&t=29579#p229156
          After a bit of practice I am finding now that I can usually find some combination of workarounds that work constantly for a given procedure. But it can take a bit of time to hit that winning combination!!!
          These codes I give below must go in a normal code module, or otherwise you must add a bit at the start of the string after the
          procedure:=
          In other words like this
          procedure:=”TraShit”
          would need to be changed to
          procedure:=”Sheet1.TraShit”
          if the procedure , Public Sub TraShit() , was in the worksheet code module with CodeName of “Sheet1”

          Alan

          Sub ResetImmediate1()
          Debug.Print “Don’t blink, or you will miss this!.. ( hopefully 🙂 )”
          ‘Application.VBE.Windows(“Direktbereich”).SetFocus
          ‘Application.SendKeys “^g ^a {DEL} {HOME}”
          Application.OnTime earliesttime:=Now, procedure:=”TryAgen”
          End Sub
          Sub ResetImmediate2()
          Application.SendKeys Keys:=”^g”
          Debug.Print “Don’t blink, or you will miss this!.. ( hopefully 🙂 )”
          ‘Application.VBE.Windows(“Direktbereich”).SetFocus
          ‘Application.SendKeys “^g ^a {DEL} {HOME}”
          Application.OnTime earliesttime:=Now, procedure:=”TryAgen”
          Application.OnTime earliesttime:=Now + TimeValue(“0:00:01″), procedure:=”TraShit”
          End Sub
          Public Sub TraShit()
          On Error Resume Next
          Application.VBE.Windows(“Immediate”).Close ‘ English Excel
          Application.VBE.Windows(“Direktbereich”).Close ‘ German Excel
          End Sub
          Public Sub TryAgen()
          Application.SendKeys “^g ^a {DEL} ” ‘ {HOME}”
          End Sub

  • 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?

    • 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

  • 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.

    • 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).

      • 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.

    • 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/

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly