Excel VBA Rapid Window – 5 Superior Methods to Use it!

‘Rapid window’ generally is a great tool to make use of when working with VBA in Excel.

The Rapid window permits you to rapidly do issues akin to:

Get details about Excel recordsdata (akin to sheet depend in a workbook or identify of the workbook)Get Debug.Print info quicklyRun a line of code in secondsRun a macro (or name a perform)Get details about variables (or set the worth or variables)

Whilst you don’t want to make use of rapid window to work with VBA in Excel, understanding find out how to use it effectively could make you much more environment friendly.

On this tutorial, I’ll cowl all that you might want to know concerning the rapid window and find out how to finest use it (defined with examples).

Let’s get began!

What’s an Rapid Window in Excel VBA?

Rapid Window is part of the VB Editor which you need to use to do some fast stuff.

To present you an instance, if in case you have a workbook with some hidden worksheets and also you wish to rapidly know the depend of whole worksheets, you are able to do that in a couple of seconds with the Rapid window.

Rapid Window does much more (as you’ll see later on this tutorial). However to start with, let’s simply consider rapid window as a device that will help you velocity up your work in Excel VBA.

The place to Discover the Rapid Window?

While you open the Excel VB-Editor, you could already see the rapid window as part of the VB Editor. And should you don’t see it already, you possibly can simply make it present up.

 

Beneath are the steps to open the VB Editor and make the Rapid Window seen:

Click on the ‘Developer’ tab within the Excel ribbon (should you don’t see the developer tab, click on right here to discover ways to get it)Within the Code Group, click on on ‘Visible Primary’. It will open the VB EditorWithin the VB Editor, click on on the ‘View’ possibility within the menuClick on on Rapid Window. It will make the rapid window present up within the VB Editor.

When you’re extra snug with shortcuts, under are some to hurry up the above steps:

To open the VB Editor – ALT + F11 (this works even should you don’t have the Developer tab within the ribbon)To indicate the rapid window – Management + G (use this one the VB Editor is open)

Examples of Utilizing Rapid Window in Excel VBA

Now that you understand the place to search out the rapid window, let’s see some superior examples the place you need to use it when working with Excel VBA.

Get Info In regards to the Recordsdata/Workbooks

The ‘Rapid Window’ is a spot the place you possibly can ask questions concerning the workbooks and it offers you the reply immediately.

For instance, suppose you’ve a workbook and also you wish to know what number of sheets are there within the workbook, you possibly can kind the under code within the rapid window and hit the enter key.

?ActiveWorkbook.Sheets.Depend

It will immediately inform you the entire variety of sheets within the energetic workbook.

This may be helpful when you’ve a workbook that has lots of sheets and you may’t depend it manually (or don’t wish to), or when you’ve a workbook the place there are hidden sheets and also you wish to know the entire depend.

The ? (query mark) is meant for use earlier than the question in order that VBA can perceive that you simply’re asking a query. When you don’t use this query mark, rapid window is not going to provide the element/reply.

Now, it is a actually easy instance the place Rapid Window provides you some info and saves time.

Beneath are some extra examples:

To get the identify of the energetic workbook

?ActiveWorkbook.Identify

To get the identify of the energetic sheet

?Activesheet.Identify

To get the trail of the Workbook (the handle the place it’s saved)

?ActiveWorkbook.Path

So should you want one thing about an object (akin to Workbook, sheets, charts, shapes, vary, and so forth.), you need to use rapid window to rapidly get this info.

Get Debug.Print Info

In Excel VBA, Debug.Print is used to indicate the worth of a variable immediately within the rapid window.

For instance, the under code would immediately present the message “Good Morning” within the rapid window.

Sub DisplayMessage()
Debug.Print “Good Morning”
Finish Sub

You should utilize the Debug.Print line in your code to rapidly get some information within the rapid window or to debug your code.

For instance, if you wish to get the names of all of the sheets in a workbook, you need to use the under code:

Sub GetSheetNames()
For Every sh In ActiveWorkbook.Sheets
Debug.Print sh.Identify
Subsequent sh
Finish Sub

The above code goes by every sheet within the energetic workbook and offers the identify within the rapid window.

Debug.Print is a helpful method to debug your code. For instance, should you’re working a loop and wish to see what number of occasions the loop was run, you possibly can merely place a Debug.Print line that merely reveals the incrementing numbers when every loop runs.

When you’re utilizing Debug.Print to debug the code, bear in mind to take away it while you’re carried out.

Run a macro (or a perform)

Whereas there are numerous methods to run a macro in Excel, one of many fast methods is utilizing the rapid window.

To run a macro, all you might want to do is enter the identify of the macro within the rapid window and hit the enter key (the cursor needs to be on the finish of the macro identify for this to work).

Beneath is an instance, the place as-soon-as you enter the identify of the macro within the rapid window, it runs the code (the place the code shows the message “Good Morning” within the rapid window).

This may be helpful when you’ve a code and wish to test whether or not it’s performing as anticipated or not. You possibly can have a number of Debug.Print strains within the code and run the macro from the rapid window.

It’ll immediately present you the values within the rapid window and you may test whether or not the whole lot is working fantastic or not.

You can even use the rapid window to execute customized features as effectively.

For instance, within the under instance, a perform is created to test whether or not a quantity is even or odd. You possibly can run this perform from the rapid window by typing the perform with the argument it takes. You could use the query mark on this case as you’re asking it t return a worth saved within the perform.

Execute a Line of Code (or A number of Strains of Code)

With rapid window, you may also run a line of code (or a number of strains of codes).

The perfect half about that is which you can merely open the rapid window, run the code and shut (in contrast to a macro which you must put in a module window after which execute).

A use-case the place this may be helpful is when wish to rapidly unhide all of the sheets within the workbook.

Beneath is the code which you can place within the rapid window to unhide all of the sheets within the workbook. Place the cursor on the finish of the road and as quickly as you hit the enter key, it can unhide all of the sheets.

For every Sheet in Thisworkbook.Sheets: Sheet.Seen=True: Subsequent Sheet

Word that though this appears like a single line of code, it’s made up of three elements.

Every half is separated with a : (colon), which acts like a line break. This lets you use the For Subsequent loop within the rapid window to undergo every sheet and alter the seen property to TRUE (which unhides any hidden sheet).

Get Variable Values

As you begin creating advanced VBA codes, you would need to depend on variables to do the job.

A variable is one thing that holds a worth and this worth can change when the code is working. You should utilize a right away window to test these variable values.

Beneath is an easy code which provides the sum of the primary 10 constructive integers and reveals the consequence within the rapid window.

Sub AddFirstTenNumbers()
Dim Var As Integer
Dim i As Integer
Dim ok As Integer
For i = 1 To 10
ok = ok + i
Subsequent i
Debug.Print i, ok
Finish Sub

As quickly as you run this code, you will notice the under consequence within the rapid window. This immediately tells you that the code is working fantastic and is giving the anticipated consequence.

In case it isn’t giving the anticipated outcomes, you possibly can debug the code.

Lots of people use a message field whereas debugging the code. Whereas it really works fantastic, I discover it slightly intrusive because it takes me to the Excel workbook and reveals a message field. However, utilizing rapid window to debug is so much smoother and retains me within the Visible Primary editor itself.

Word: When you’re working with a fancy code and have a number of variables that you might want to monitor, it’s higher to make use of a Watch Window. A Watch Window permits you to add a variable after which you possibly can see how that variable modifications in real-time (or as you step by the code).

Set Variable Worth when Debugging

It is a little superior use of the Rapid window, however should you use it, it may be an enormous time saver when debugging the code.

You should utilize the rapid window to set the worth of a variable whilst you run the code within the debug mode (the place you set a breakpoint so the code doesn’t execute totally – however as much as a particular line).

For instance, if in case you have a loop that runs for 10 occasions and also you wish to test what occurs when the loop runs for the eighth time, you don’t must step by the loop seven occasions to get to the eighth iterations. You possibly can merely add a breakpoint earlier than the loop and alter the loop variable to eight. Now, while you run the code, it can present you what occurs when the loops run for the eighth time.

This turns into much more helpful while you’re writing advanced codes and wish to debug particular loops.

Suppose you’ve a code as proven under:

Sub UnhideSheets()
For i = 1 To Worksheets.Depend
Debug.Print Sheets(i).Identify
Subsequent i
Finish Sub

The above code merely lists the sheet names of all of the sheets within the workbook within the rapid window.

When you don’t wish to record the names of all of the sheets, however solely the sheets after the 10th sheet, you possibly can place a breakpoint within the second line of the loop (in order that nothing after that line is executed) after which change the variable ‘i’ to 11.

To alter the variable identify in rapid window, enter the next line:

i=11

It will be sure that the primary 10 occasions of the loop are ignored and your variable worth is about to 11.

Docking/Undocking the Rapid Window

You possibly can have rapid window docked to the VB Editor – which implies that it stays at one place within the VB Editor and strikes and sizes with it.

And you may also have it un-docked, which implies it’s impartial of the VB Editor and might be moved as a standalone window.

Beneath is how one can change the docking settings for the rapid window:

Click on the Instruments possibility within the menu within the VB EditorClick on on ‘Choices’Within the Choices dialog field, click on on the final tab – DockingCheck the Rapid Window choice to make it dockable and uncheck it to make it un-dockable.Click on OK

Alternatively, if the rapid window is seen, you may also right-click and alter the dockable property of it.

Dockable implies that the rapid window would turn out to be part of the present window by putting itself in sure locations, such because the left of the window or on the backside. This permits the docked home windows to maneuver and measurement collectively.

Rapid Window is Not Exhibiting – Listed here are Some Methods to Get It

There could possibly be a number of causes for this occurring.

The most typical motive that you simply don’t see a right away window is that it has not made seen. It is probably not seen by default and to make it seem, you might want to go to the View possibility within the menu and click on on the Rapid Window possibility. It will ensure it’s seen within the VB Editor.

You can even use the keyboard Management + G to make the rapid window present up. Use this keyboard shortcut while you’re within the VB Editor.

One more reason you could not see the rapid window is that it’s been minimized is now so closed which you can’t see it (as reported right here). Simply do a fast scan and test should you see a small blue/purple sq. lurking someplace. You possibly can hover the cursor over it and you will notice some choice to resize it.

When you can’t see the rapid window in any respect, right here is one thing that has labored for lots of people (this typically occurs due to display decision modifications):

Open the VB EditorPress Ctrl-G to provide focus to the rapid window (or click on the View possibility within the menu after which click on on rapid window)Maintain the ALT key after which press the spacebar key. It will present drop-down choices for the rapid window.Press M (for Transfer)Use the arrow keys to maneuver the windowOnce you determine the place it’s, make it larger

I obtained this from a discussion board right here and in addition observed the identical being steered as an answer for VBA rapid window not exhibiting up.

One other factor you possibly can strive is making the rapid window undockable (go to Instruments –> Choices –> Docking and uncheck the rapid window possibility). This has labored for some folks.

You may additionally like the next articles: