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.
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.
The fastest way to get to the Immediate Window is to:
- 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)
- 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.
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.
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.
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.
#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.
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
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.
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.
#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.
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.
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.
Here are the step-by-step instructions:
- Left-click and hold on the top bar of the immediate window.
- Drag it out of the VB Editor window. The immediate window becomes a free floating window that you can put on top of Excel.
- 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.
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.
Great tutorial, Jon.
Thanks Jeff!
[…] If you do any programming in Excel, read Jon Acampora’s tips for using the Immediate window. […]
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
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/
I always knew Jan Karel would trot out his colon party trick sooner or later. 🙂
Seriously though, great trick Jan Karel.
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
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.
Hi Steffan,
That’s a great tip about the number format! I will definitely be using that one in the future. Thanks for sharing!
My pleasure.
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.
Nice Tip !
Here’s one I used today that removes the page break lines on the sheet.
ActiveSheet.DisplayPageBreaks = False
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
very good tips
Thanks Abhishek!
goooooooooooooooooooooood
Thank yooooooooooooooooooooooooooou! 🙂
How to empty the window via VBA code would interesting as well, since running a macro several times will always append Debug.Print information.
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!
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
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.
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.
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
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!
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)
GREATTTTTTTT tutorial,
I am already sure I’ll really save some of my time in the future
Thanks Skratt!
[…] 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 […]
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! 🙂
Excelente aporte….
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.
W
I hope that helps. Thanks!
Double-click header row to dock/undock 🙂
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 as always 🙂
Its great illustration of Immediate Window usage. Thanks for sharing
Thanks Rocky!
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.
You can also use the constant VbNewLine to add a new line within the string. I hope that helps get you started. Thanks!
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
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
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….
Hi Sumanth,
I’m not sure which window you are referring to.
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.
?myMind.isBlown
True
?tooFunny.whoIs
Eric Ruiz
Thanks Eric! 🙂
I have to learn Excel 2010 macros.
Hi Lavanya,
Checkout my free 3-part video series on getting started with macros & VBA.
Great info…thank you so much!!!
Really very useful information.. helps a lot.. Thank u so much.. for ur continues support.. 🙂
Thank you Sureshkumar! 🙂
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.
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.
Thanks Big Al! I really appreciate your support! 🙂
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.!
Thanks Raja! You can use the Resize property of the Range object for that.
Range("A4").Resize(8,11)I hope that helps.
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()
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.
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
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.
What works is selecting the text in the IW and drag it with your mouse to a .rtf file (not a simple .txt). From there you can paste it back in others files. So no copy-paste.
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
Hi Krishna,
You can concatenate or join a string of text before the Count using the ampersand.
I hope that helps.
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
thanks
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.
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
Do you have any video of that? I’d love to find out more details.
Thanks for the suggestion! I’ll add it to the list for future videos. 🙂
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.
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,
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
Jon, Thanks a lot for your information.
I think it is very interesting and useful.
Greetings from Santiago of Chile. 27-05-2018
Superb!
This is a great set of tips. Thanks a lot Jon!
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!
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
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” )
Thanks very much for the last tip Alan; I normally have the immediate window closed, now I know how to open it automatically when needed.
Clear explanations about a topic. That’s very convenient and quick to understand.
Thank you very much.
how to find path in immediate window.
with workbook name
how to find path in immediate window.
through workbook name
Setting a variable’s new value is a cool trick Jon. I used to use the Watch window and have the code stop at a certain value of my variable but now I can set that value straight away. Good catch.
What about if the Immediate’s window is not longer attached to an edge of the VBA window and now acts like the code windows (can either be full-screen or can cascade the window)?
I did something and now my Immediate window isn’t always visible along the bottom like my Watch window still is, so when I want to keep it open and step through my code I have to manually line up and size the windows and if it jumps to another module or class then that window pops up overlaying all of the other windows.
I cannot drag it to the bottom or sides of the window, that does nothing.
Great tips! The VBA Immediate Window is such a powerful tool. I especially loved the example on debugging; it really made things clearer for me. Thanks for sharing these insights!