Comment Block of Code in VBA

Did you know that you can comment out block of code in VBA? Yes you can. But VBA’s method is little different from other programming languages. Because other languages like Java, PHP etc. have specific syntax to comment a block of code. But in VBA you can comment and uncomment blocks of codes using two buttons. VBA doesn’t have a dedicated syntax to comment a block of code.

In VBA you can comment a single line of code using the ' (apostrophe mark).

'This is a comment

Then if you want to comment out multiple lines, you have to add apostrophe marks in front of all of those lines.

'This is a comment line 1
'This is a comment line 2
'This is a comment line 3

But other programming languages have a dedicated syntax to comment multiple lines. For example, this is how you can comment a block of code in Java.

So if you need to comment multiple lines in your VBA program, adding apostrophes manually is not easy. Luckily as I said above Visual Basic Editor has two buttons to comment and uncomment the lines of codes. Here is how you can do it.

First check whether your Visual Basic Editor already has “Edit” tools in the Toolbar.

If they are available in the editor then click here to jump to the next step. If they are not available, follow these steps to add them to the Toolbar.

Click on the “View” menu.

Take the mouse pointer on top of the “Toolbars”. Then a list of names of toolbars will appear to the right. Put a tick in front of the “Edit”.

Then “Edit” tools will be available in the VBA editor.

Once “Edit” tools are available, you can use these two buttons to comment and uncomment a block of codes.

Now let’s look at how to comment and uncomment a block using these two buttons. First select the section you want to comment out.

Then click the “Comment Block” button.

Similarly if you want to uncomment a section, first select that section and then click on the “Uncomment Block” button.

Fill a Listbox From an Array

In this lesson you will learn how to fill a Listbox from an array. Listboxes are a very useful control type available in the VBA toolbox. You can use the listboxes for search and view functions of the VBA programs. First let’s fill the listbox using a multidimensional array. After that we can try it with a one dimensional array. This is the sample Excel sheet I’m going to use for this lesson.

And here is the listbox we are going to fill with the array.

So we have data in three columns and fourteen rows. First, we are going to add this data to an array. After that we can add the array to our listbox in the VBA form. You might wonder why we need to add the data to an array. Because data can be directly added to the listbox from the worksheet using VBA. Yes it is possible. But here our objective is to learn how to add data to a listbox from an array. Because there are situations where we need to add the data which is not available in worksheets to listboxes using VBA. For example you might add data from a worksheet to one array. Then search through the elements of that array and create another array from the search results. Now you may want to show this second array in a listbox. So then you have to fill a listbox directly from an array using VBA. There can be various examples like this. But to make this lesson easy to understand, I’m going to use this simple example.

Here what we are going to do is we are going to show the data in the listbox when the VBA form is loaded. So we can add the VBA code to the UserForm_Initialize event.

Private Sub UserForm_Initialize()


End Sub

First we need to declare a few variables. Let’s assume the name of the worksheet is “Data”.

Dim WS_Data As Worksheet
Dim AllData(1 To 14, 1 To 3) As String
Dim i As Integer
Dim j As Integer

Here I have declared an array of the type string. Because I have only string type data in the Excel range. So you need to declare your array according to the type of data you have. If the data contains different data types then you should declare the array as type variant.

Next, assign the Excel sheet to the WS_Data variable.

Set WS_Data = Worksheets("Data")

Now we can add the data from the worksheet to the array. There are a few different ways to do this. Here is one method.

For i = 1 To 14
     For j = 1 To 3
         AllData(i, j) = WS_Data.Cells(i, j).Value
     Next j
Next i

This post explains a quicker way to create an array from an Excel range.
Quick Multidimensional Array from Excel Range

Next step is to set the column count. If you don't set the column count then the listbox will have only one column. Assume the name of the listbox is “lstNameEmailList”. In the conventional naming method we add “lst” in the beginning of the listbox name.

lstNameEmailList.ColumnCount = 3

Now we can assign width for each column. This step is optional. If you skip this step all the columns will have the same width.

lstNameEmailList.ColumnWidths = "100;100;250"

Finally we can add the array to the listbox using the “List” method as follows.

lstNameEmailList.List() = AllData

Below is the full VBA code of the UserForm_Initialize event.

Private Sub UserForm_Initialize()

     Dim WS_Data As Worksheet
     Dim AllData(1 To 14, 1 To 3) As String
     Dim i As Integer
     Dim j As Integer

     Set WS_Data = Worksheets("Data")

     For i = 1 To 14
         For j = 1 To 3
             AllData(i, j) = WS_Data.Cells(i, j).Value
         Next j
     Next i

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "100;100;250"

     lstNameEmailList.List() = AllData

End Sub

Now data will be shown in the listbox when the userform is loaded.

So we learnt how to fill a listbox from a multidimensional array in VBA. Next let’s try to fill this listbox with a one dimensional array.

Assume we have a one dimensional array like this.

Dim OneEmployeeInfo(1 To 3) As String

OneEmployeeInfo(1) = "Cathrine"
OneEmployeeInfo(2) = "Wintour"
OneEmployeeInfo(2) = "cathrinewintour@example.com"

We can add this array to our listbox easily inside the UserForm_Initialize event as follows.

Private Sub UserForm_Initialize()

     Dim OneEmployeeInfo(1 To 3) As String

     OneEmployeeInfo(1) = "Cathrine"
     OneEmployeeInfo(2) = "Wintour"
     OneEmployeeInfo(3) = "cathrinewintour@example.com"

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "100;100;250"

     lstNameEmailList.List() = OneEmployeeInfo

End Sub

Below is the result you will get when the VBA form is shown.

Here the data is listed in one column even though I have set the column count as 3. I faced a similar problem when I developed a VBA application recently. That program had a class module function which returns an array. Sometimes it returns a multidimensional array and sometimes a one dimensional array. Then the data of this returned array was shown to the user through a listbox. But as in above, listbox showed the data in a single column when the class module returned a one dimensional array. But I wanted to show the data horizontally when there is one row.

So how can we solve this problem? How to show the data horizontally in multiple columns instead of in a one column? For that you have to convert the one dimensional array to a multidimensional array. You can follow below steps to convert a one dimensional array to a multidimensional array in VBA.

Private Sub UserForm_Initialize()

     Dim OneEmployeeInfo(1 To 3) As String

     OneEmployeeInfo(1) = "Cathrine"
     OneEmployeeInfo(2) = "Wintour"
     OneEmployeeInfo(3) = "cathrinewintour@example.com"

     Dim OneEmp_Multidimensional_Arr(1 To 1, 1 To 3) As String

     For i = 1 To 3
         OneEmp_Multidimensional_Arr(1, i) = OneEmployeeInfo(i)
     Next i

     lstNameEmailList.ColumnCount = 3

     lstNameEmailList.ColumnWidths = "100;100;250"

     lstNameEmailList.List() = OneEmp_Multidimensional_Arr

End Sub

Now when we show the VBA form using the form.show method, the listbox will be filled with the data like this.

Want to learn more about arrays? Then check these posts.
Fixed Size Arrays in VBA
Multidimensional Arrays in VBA
Dynamic arrays in VBA
Calculate With Arrays

How to Return an Array From VBA Function

Today I’m going to show you how to return an array from a VBA function. VBA functions can return arrays of any data type. But sometimes we need to return arrays with elements of different data types. Luckily we can do it using the variant data type. I will show how to return an array of type variant in an example. Let’s start with a simple example. Assume we want to create a VBA function which returns 3 random numbers between 1 and 100. The VBA function can return these 3 numbers as an array. We can use the inbuilt function called Rnd to generate a random number. Then we can do some additional calculations and use the VBA Int function to get a number between 1 and 100. Here is how you can do it.

Function ThreeRandomNumbers() As Integer()

     Dim ResultArr(2) As Integer

     ResultArr(0) = Int(Rnd * 100) + 1
     ResultArr(1) = Int(Rnd * 100) + 1
     ResultArr(2) = Int(Rnd * 100) + 1

     ThreeRandomNumbers = ResultArr

End Function

Rnd function return values similar to this.

0.8626193
0.7904852
0.3735362

So I multiplied those numbers by 100.

86.26193
79.04852
37.35362

And the VBA Int function returns the integer part of the number. So finally we get random numbers like below.

86
79
37

Rnd function returns values equal or greater than 0 and less than 1. Then Int(Rnd * 100) will output numbers from 0 to 99. Therefore we have to add 1 to get a random number between 1 and 100.

Int(Rnd * 100) + 1 => Generates integer values between 1 and 100

Now we have a VBA function which can return an array. Let’s see how we can call this function within a subroutine. Data type of the array returned by our function is integer. So we need an array of the type integer inside our subroutine. Then we can assign the function’s return value to that array.

Sub Test1()

     Dim RandomNumbers() As Integer

     RandomNumbers = ThreeRandomNumbers()

End Sub

Add a breakpoint at End Sub and run the subroutine. Then you will see the result in the Locals window like this.

Also you can print these values to an Excel sheet as well. If the name of the worksheet is “Sheet1” then you can write the array to the worksheet as follows.

Sub Test2()

     Dim WS As Worksheet
     Dim RandomNumbers() As Integer
     Dim i As Integer

     Set WS = Worksheets("Sheet1")
     RandomNumbers = ThreeRandomNumbers()

     For i = 0 To 2
          WS.Range("A1").Offset(i, 0).Value = RandomNumbers(i)
     Next i

End Sub

Here is the result of the Test2 subroutine.

Next let’s look at another example where we need to pass arguments to the VBA function. Assume we need to find 5 powers/exponents of a given number. For an example if the given number is 3 then the VBA function should return 1,3,9,27 and 81 (3⁰,3¹,3²,3³,3⁴)

Here we have to pass the given number as an argument to the function. Then the function can return 5 exponents of the number as an array.

Function FiveExponents(GivenNumber As Integer) As Integer()

     Dim ResultArr(4) As Integer

     ResultArr(0) = GivenNumber ^ 0
     ResultArr(1) = GivenNumber ^ 1
     ResultArr(2) = GivenNumber ^ 2
     ResultArr(3) = GivenNumber ^ 3
     ResultArr(4) = GivenNumber ^ 4

     FiveExponents = ResultArr

End Function

.

Now we can call this function within a subroutine like this.

Sub Test3()

     Dim WS As Worksheet
     Dim Exponents() As Integer
     Dim i As Integer

     Set WS = Worksheets("Sheet1")
     Exponents = FiveExponents(5)

     For i = 0 To 4
          WS.Range("A1").Offset(i, 0).Value = Exponents(i)
     Next i

End Sub

Below is the outcome of the above subroutine.

Above functions output arrays of integer data type. So the returned array consists of only integers. But sometimes we need VBA functions which should return arrays with elements of various data types. They may contain values of data types such as integer, string, boolean etc. Here is an example.

This worksheet contains order information of a shop. Assume we need a VBA function which takes an order id as an argument and then returns all the other information of that order as an array. It is a type of function we need when searching data. So here various columns have various data types. Therefore we need to declare the data type of the return value of the function as the variant.

This is the function developed for the above requirement.

Function GetOrderInformation(OrderId As String) As Variant

     Dim WS As Worksheet
     Dim WS_LastRow As Long
     Dim i As Long
     Dim j As Integer
     Dim ResultArr(6) As Variant

     Set WS = Worksheets("Order Details")

     WS_LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     For i = 2 To WS_LastRow
          If StrComp(WS.Range("A" & i).Value, OrderId, vbTextCompare) = 0 Then
               For j = 0 To 6
                    ResultArr(j) = WS.Range("A" & i).Offset(0, j).Value
               Next j
               Exit For
          End If
     Next i

     GetOrderInformation = ResultArr

End Function

First we find the last row of the worksheet. Then use For Next Statement to iterate through the rows. Strcomp function is used to find the matching row for the given order id. If a matching row is found then all the information of that row is written to an array.

Here is how you can call the above function inside a subroutine.

Sub Test4()

     Dim OrderInfo() As Variant

     OrderInfo = GetOrderInformation("209-2752429-9545")

End Sub

Add a breakpoint at End Sub and run the macro. Then you can view the OrderInfo array in the Locals window.

Also read
Quickly Write Multidimensional Array to Excel Range
Re-size Dynamic Arrays
Calculate With Arrays
How to use preserve keyword in arrays
Transposing an Array in VBA

Clear Sheet Using VBA

In this post you will learn how to clear a sheet using VBA. Excel sheets can contain various elements and objects. So I will show you how you can clear these various items using VBA. You will learn how to clear a specific range or an entire sheet. Also you will learn how to clear things from an active sheet or a specific sheet. Inbuilt clear methods don't allow us to delete shapes. So at the end I will show how to create our own VBA code to delete objects such as charts, shapes etc. To explain about these various methods I will use this sample Excel sheet.

This Excel sheet consists of text, values, hyperlink, chart and a star. Also some formattings are applied to the sheet such as colors and borders.

There are various ways to clear Excel sheets. But not all the methods can be used to clear all the things from the sheet. So let’s see how we can use these different methods and their outcomes.

Here is the first method you can use to clear an Excel sheet using VBA.

Sub ClearSheet_Example1()

     Cells.Clear

End Sub

In this method we haven’t specified a sheet. So the clear method will be applied to the active sheet. If you run the above VBA code, contents, formats, comments and hyperlinks will be deleted from the active sheet. But the shapes and charts will not be deleted. If the source data of the chart is in the same sheet, then the chart will be blanked. This is what happened to our sample Excel sheet after running above code. (In our sample excel sheet, source data of the chart was in the same sheet.)

Next let’s look at how to modify the above VBA code to clear a specific sheet from a workbook. Assume we have a workbook with multiple worksheets and we need to clear the sheet called “Order Details”. You can modify the above subroutine to clear the “Order Details” sheet as follows.

Sub ClearSheet_Example2a()

     Worksheets("Order Details").Cells.Clear

End Sub

If you want, you can declare a variable of type worksheet and then assign this worksheet to the variable and clear it as well.

Sub ClearSheet_Example2b()

     Dim WS As Worksheet

     Set WS = Worksheets("Order Details")

     WS.Cells.Clear

End Sub

Also the Clear method can be used to clear a specific range of a worksheet. Following subroutine will clear the range A6:B10 of the activesheet.

Sub ClearSheet_Example3()

     Range("A6:B10").Clear

End Sub

This is what happened to our sample worksheet after running the above macro.

In the above examples we cleared everything except shapes and charts. However if you want you can clear only particular things from an Excel sheet or a range. Here is the list of things you can clear using VBA, without clearing everything.

• Comments
• Contents
• Formats
• Hyperlinks
• Notes
• Outlines

Now let’s look at how to clear each of these things separately.

Following subroutine will clear all the comments of the active sheet.

Sub ClearComments()

     Cells.ClearComments

End Sub

You can use the below macro to clear the contents from range A8:B12.

Sub ClearContents()

     Range("A8:B12").ClearContents

End Sub

Only contents are removed from that area keeping the table formats.

Also we can clear only formats from the whole sheet or specific range of a sheet. This next macro will clear all the formats from the table (range A1:B10) of the “Data” sheet.

Sub ClearFormats()

     Worksheets("Data").Range("A1:B10").ClearFormats

End Sub

Here is the result of the above subroutine.

Sometimes you may want to remove the hyperlinks of the entire sheet or from a range of cells using VBA. For that you can use the ClearHyperlinks method as follows.

Sub ClearHyperlinks()

     Cells.ClearHyperlinks

End Sub

Above macro will remove all the hyperlinks of the activesheet. However note that the formattings and the text of the hyperlinks will not be removed. Only the link will be removed.

Here is how to clear all the notes of the Excel sheet using VBA.

Sub ClearNotes()

     Cells.ClearNotes

End Sub

Next let’s look at how to clear the outline from an Excel sheet. Assume we have an outline like this in our worksheet.

Here we have grouped rows from 2 to 10 using an outline. We can remove this outline automatically using the ClearOutline method as follows. Name of the worksheet is “Data”.

Sub ClearOutline()

     Worksheets("Data").Cells.ClearOutline

End Sub

So far we learnt various ways to clear sheets. But none of them were able to delete the objects such as shapes, charts etc. Inbuilt clear methods don’t clear these objects from the sheet. We need to create our own code to delete these items. Next let’s look at how to delete these objects from a worksheet automatically using VBA. To do this we need to iterate through each and every shape using a For Each loop and delete them. Here is the code to delete all the shapes from the active sheet.

Sub DeleteAllObjects()

     Dim Sh As Shape

     For Each Sh In ActiveSheet.Shapes
         Sh.Delete
     Next

End Sub

If we run this subroutine in our sample Excel sheet, the result will look like this.

Check this post if you want to learn more about manipulating shapes using VBA.

Shapes

We can also add the Clear method to this subroutine. Then everything will be cleared from the sheet.

Sub ClearEverything()

     Dim Sh As Shape

     For Each Sh In ActiveSheet.Shapes
         Sh.Delete
     Next

     Cells.Clear

End Sub

Above macro will clear everything in the activesheet. You can also modify the above VBA code to clear everything from a specific sheet as well. Assume we want to clear everything from a sheet called “Data”. For that we can easily modify the above subroutine as follows.

Sub ClearEverything_SpecificSheet()

     Dim WS As Variant
     Dim Sh As Shape

     Set WS = Worksheets("Data")

     For Each Sh In WS.Shapes
         Sh.Delete
     Next

     WS.Cells.Clear

End Sub