What Visual Basic's Office Return Means For Mac

Posted on

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.

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

Return

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.

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

(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 with your suggestions, or any questions.

Alan Elston - November 28, 2018 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” ). Alan Elston - November 26, 2018 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. 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. April 2, 2018 Hi Sam, Thank you for your support, and for joining the course! I have a, 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.

What Visual Basics Office Return Means For Mac Free

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. I hope that helps.

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

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.

What Visual Basics Office Return Means For Mac Download

Something in the registry get changed? Seeing that MS isn’t address these issues, thought it worth the email on your thoughts.

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 Fornext will be write in several rows like in Immediate windows. If I use the Fornext 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.

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

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

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! I can’t tell you how much I’ve learned from Jan Karel’s site at.

Hi I have a macro which had been working fine, which had the following code: With Range('Contact').Locked = False.Copy.PasteSpecial Paste:=xlPasteValues.Interior.ColorIndex = 36 End With where the range 'Contact' is on the active sheet, and the active sheet is unprotected. For some reason, the macro started throwing up an error box, titled 'Microsoft Visual Basic', showing only '400' (with OK & Help buttons). I've fixed the problem, changing the 1st two lines to the following: Range('Contact').Select With Selection but, I'd be interested in understanding why this has occurred. 400 errors are, by definition, unspecified and (for all intents and purposes) mysterious.

Your code should not fail here, as far as I can tell. You may even find that changing it back, it will now work again. The only way I know of to troubleshoot 400 errors is to step through the code line by line - and watch for the moment when it crashes. From on outside perspective, the fact that you are unlocking a cell in order to do something with it suggests that there's an issue with worksheet protection. You say the sheet is unprotected - but if so, why must the cell be unlocked? I suspect there are other factors at play - though I can't say what or how, or why your code fix has made a difference. Another try, without selection, might be.

Code: Dim r As Range Set r = Range('Contacts') With r '// code End WithI'm sure this is unsatisfactory as a response. If you're still interested in why, then give more details - what's this about with unlocking the cell, what's in the range and what's the purpose of the code? Is it in a larger procedure and if so what else is going on? Is the sheet being used with protection, and how? Is it a shared workbook? Also, are you sure this is the line with the problem? Have you stepped through the code?

I would suggest starting a new thread, but on a brief glance, you're probably missing a backslash where indicated above Caleecoi have added the backslash and still getting the same problem. Sub NextInvoice Range('E4').Value = Range('E4').Value + 1 Range('B13:D26').ClearContents End Sub Sub SaveInvoiceWithNewName Dim NewFN As Variant ' Copy Invoice to a new workbook ActiveSheet.Copy NewFN = 'C: Documents Invoice' & Range('E4').Value & '.xlsx' ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close NextInvoice End Sub.