Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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.

View the array returned by the function in the Locals window

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.

Write the returned array of the function to a worksheet

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 (30,31,32,33,34)

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.

VBA function returned five exponents of the given number as an array

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.

Sample data sheet

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.


Array of type variant returned by the function

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.

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

Result of Cells.Clear method

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.

Clear a range of an excel sheet using VBA

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 keeping the formats

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.

Only formats are cleared from the specified range

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.

Outline

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
Outline was cleared using ClearOutline method

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.

All the shapes are deleted from the sheet

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
Everything was cleared from the worksheet

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

Contact Form

Name

Email *

Message *