How to Clear the Immediate Window Using VBA

A VBA program can have multiple subroutines and functions. If you are a programmer you will need to test these subroutines and functions while developing the VBA code. Then the immediate window is a helpful tool you can use to test your components. If it is a subroutine, then you can print your variables using debug.print method to ensure whether they are calculated correctly. If it is a function you can print the return value in the immediate window.

From one of our previous posts we learnt how to output data in VBA using a few different ways. In that post you can find how to output data to Immediate window using VBA.

Output data in Excel VBA

So today I'm going to show you how to clear the immediate window automatically using VBA. You may know how to clear it manually. If you don't know, follow these simple steps. First select everything in the immediate window. Then press the delete key on the keyboard to delete everything. If the immediate window is not visible you can use shortcut keys “Ctrl + G” to view the immediate window. Also if the cursor is in a different window than the immediate window, and if you press the above shortcut keys, then the cursor will move to the immediate window giving you access to the immediate window.

Now let’s look at how we can clear the immediate window using VBA. We can use the Application.Sendkeys method to clear the immediate window using VBA. Application.Sendkeys method lets us send keystrokes to the active application. So here is the order of keystrokes we need to send to clear the immediate window.

Ctrl + G
Ctrl + A
Delete

Ctrl + G will activate the immediate window. Then Ctrl + A will select everything in the immediate window. And Delete keystroke will delete the content from the immediate window. However in the Application.Sendkeys method, these keys are represented by one or more characters. Ctrl is represented by ^ character. You can simply use g and a keys to represent G and A respectively. and the delete key is represented by {DEL} characters. Check this documentation from Microsoft to see the codes of other available keys.

Application.SendKeys method (Excel)

So now you can rewrite the order of keystrokes in the Application.Sendkeys notation as follows.

^a^g{DEL}

Then you can develop the macro to clear the Immediate window using VBA as follows.

Sub ClearImmediateWindow()

     Application.SendKeys "^g^a{DEL}"

End Sub

To test this macro, First I need to output something in the Immediate window. I will use the below macro to print “Hello, World!” hundred times in the immediate window.

Sub WriteHelloWorld()

     Dim i As Integer

     For i = 1 To 100
         Debug.Print "Hello, World!"
     Next i

End Sub

This is how the Immediate window looks like when run the above macro.

Now let's run the ClearImmediateWindow subroutine to see how it clears the immediate window.

However, using the Application.Sendkeys method is not advisable. Because unexpected things can happen if a user interacts with the Excel application while running the program. For example a whole code in your modules can be deleted if accidentally selected a module instead of the immediate window. So this is not a robust code to use in a VBA application. I suggest not to use the Application.Sendkeys method if there are any other options.

How to Create a Column Chart Automatically Using VBA

Column charts are a chart type where data are represented from rectangles. In these charts, data are represented by vertical bars. Some people refer to these charts also as bar charts. But there is a difference between bar charts and column charts. If you interchange the x axis and y axis of a column chart then you will get a bar chart. Column charts make data easy to understand. Users will be able to understand the data at a glance when they are represented in column charts rather than in a written format. These charts are very helpful when we need to compare values of different categories. Column charts are more flexible than other chart types because you can plot lots of categories in one chart.

So in this lesson I’m going to show you how to insert a column chart using VBA. I will use this sample Excel sheet to show you how to do this. This worksheet lists sales data of each month of a company.

Assume the name of the worksheet is “Sales Data”. Then we can create a column chart automatically using the AddChart2 method available in VBA.

Sub CreateColumnChart()

     Dim WS As Worksheet

     Set WS = Worksheets("Sales Data")

     WS.Shapes.AddChart2(-1, xlColumn).Select
     ActiveChart.SetSourceData Source:=WS.Range("'Sales Data'!$A$1:$B$13")

End Sub

This is the result of the above subroutine.

In the above VBA code I have used single quotes for the worksheet name. It is because we have a space character in the worksheet name. But if you don’t have a space character in your worksheet name then you can write it without quotes. For example if your worksheet name is “Data” then you can rewrite that line as follows.

ActiveChart.SetSourceData Source:=WS.Range("Data!$A$1:$B$13")

Also you can revise the above code using the Worksheet.Name property as well. This is how you can do it.

Sub CreateColumnChart_Ex2()

     Dim WS As Worksheet

     Set WS = Worksheets("Sales Data")

     WS.Shapes.AddChart2(-1, xlColumn).Select
     ActiveChart.SetSourceData Source:=WS.Range("'" & WS.Name & "'" & "!$A$1:$B$13")

End Sub

I prefer this method because it is very easy to reuse or modify this code. For example if you want to use this for a different worksheet, then you need to change the worksheet name only in one line.

The AddChart2 method has several parameters. But all of them are optional. First parameter of the AddChart2 method is the style. In the above example, we used -1 as the first parameter of the AddChart2 method. If we set -1 as the style then we get the default style of the chart type specified in the second parameter. But we can create charts with various styles by changing this number. Here are some charts available in my Excel version.

Sub CreateColumnChart_Style209()

     Dim WS As Worksheet

     Set WS = Worksheets("Sales Data")

     WS.Shapes.AddChart2(209, xlColumn).Select
     ActiveChart.SetSourceData Source:=WS.Range("'" & WS.Name & "'" & "!$A$1:$B$13")

End Sub

Also Read
How to Add or Edit Chart Title Using VBA
Swap Axis of an Excel Chart Without Changing Excel Sheet Data
How to find the name of an active chart using VBA

Sub CreateColumnChart_Style208()

     Dim WS As Worksheet

     Set WS = Worksheets("Sales Data")

     WS.Shapes.AddChart2(208, xlColumn).Select
     ActiveChart.SetSourceData Source:=WS.Range("'" & WS.Name & "'" & "!$A$1:$B$13")

End Sub

Not all the styles available in every Excel version. So you should first find the style number of your preferred chart before developing the code. You can easily find the style number by using the record macro option available in Excel application. Start recording a macro and then create a column chart with a style you prefer. Then find the style number from the code generated. Check this post if you want to learn more about the record macro option available in the Excel application.

How to Record a Macro in Excel