How to Use VBA Array Function in Excel

The Array Function is a very useful tool in Excel to store data and limit the use of the variable, which is basically saving a lot of memory usage of the computer. So array is already a very desirable and practical tool not only in the field of VBA but across most of the coding languages out there. In this article, we have shown in Excel VBA, how we can use the VBA Array Function in Excel.

Below we have shown a sample method where we have shown how we can use VBA Array to Store Value that Satisfies the Conditions.

Download Practice Workbook

Download the following workbook to practice by yourself.

VBA Array Function Overview

Syntax:

Dim Array_Name() as datatype
random_variable= Array(“Element1”,”Element2”,”Element3”)

Example:

Fruit_name_variable= Array(“Apple”,” Banana”,” Berry”)

Types of Array in Excel

There are several Array types to choose from. In terms of dimension, you can choose one dimension and two dimensions. On the other hand in terms of flexibility, you can choose whether the code is static or fixed. All of those code types are given below with examples.

One Dimensional Array

Sub OneDimensionalArrayExample()
    Dim myArray(1 To 5) As Variant
    Dim i As Long
    myArray(1) = "Apple"
    myArray(2) = "Banana"
    myArray(3) = "Orange"
    myArray(4) = "Mango"
    myArray(5) = "Grapes"
    For i = 1 To 5
        Debug.Print "Element " & i & ": " & myArray(i)
    Next i
End Sub

Two Dimensional Array

Below we presented an example of a two-dimensional array example.

Sub TwoDimensionalArrayExample()
    Dim myArray(1 To 3, 1 To 2) As Variant
    Dim i As Long, j As Long
    myArray(1, 1) = "Apple"
    myArray(1, 2) = 5
    myArray(2, 1) = "Banana"
    myArray(2, 2) = 3
    myArray(3, 1) = "Orange"
    myArray(3, 2) = 2
    For i = 1 To 3
        For j = 1 To 2
            Debug.Print "Element (" & i & ", " & j & "): " & myArray(i, j)
        Next j
    Next i
End Sub

Fixed Array 

Below we presented a code that contains a static array, in the static array we need to enter the values in the array one by one.

Sub Static2DArrayExample()
    Dim myArray(1 To 3, 1 To 2) As Variant
    Dim i As Long, j As Long
    myArray(1, 1) = "Apple"
    myArray(1, 2) = 5
    myArray(2, 1) = "Banana"
    myArray(2, 2) = 3
    myArray(3, 1) = "Orange"
    myArray(3, 2) = 2
    For i = 1 To 3
        For j = 1 To 2
            Debug.Print "Element (" & i & ", " & j & "): " & myArray(i, j)
        Next j
    Next i
End Sub

Dynamic Array

Below we are presenting a two-dimensional array with dynamic functionality.

Sub dynamic_array_initiation()
Dim sourceRange1 As Range
Set sourceRange1 = Application.InputBox("Select a range", "Range selection", Type:=8)
Dim numRows As Long, numCols As Long
numRows = sourceRange1.Rows.Count
numCols = sourceRange1.Columns.Count
Dim old_Dynamic_Array() As Variant
ReDim old_Dynamic_Array(1 To numRows, 1 To numCols)
Dim i As Long, j As Long
For i = 1 To numRows
For j = 1 To numCols
old_Dynamic_Array(i, j) = sourceRange1.Cells(i, j).Value
Next j
Next i
x = UBound(old_Dynamic_Array, 1)
Y = UBound(old_Dynamic_Array, 2)
Dim response As VbMsgBoxResult
response = MsgBox("Your Selecred Cells array dimension is (" & x & "," & Y & "). Do you want to Expand your Selection?", vbYesNoCancel, "Confirmation")
Select Case response
Case vbYes
Dim sourceRange2 As Range
Set sourceRange2 = Application.InputBox("Select a range", "Range selection", Type:=8)
Dim numRows1 As Long, numCols1 As Long
numRows1 = sourceRange2.Rows.Count
numCols1 = sourceRange2.Columns.Count
Dim new_Dynamic_Array() As Variant
ReDim new_Dynamic_Array(1 To numRows1, 1 To numCols1)
Dim k As Long, l As Long
For k = 1 To numRows1
For l = 1 To numCols1
new_Dynamic_Array(k, l) = sourceRange2.Cells(k, l).Value
Next l
Next k
x1 = UBound(new_Dynamic_Array, 1)
y1 = UBound(new_Dynamic_Array, 2)
MsgBox "Your Old Array dimension was (" & x & "," & Y & "). Your new array dimension is (" & x1 & "," & y1 & ")"
Case vbNo
MsgBox "You Decided to Quit"
Case vbCancel
MsgBox "You clicked Cancel."
End Select
End Sub

Sub DynamicArrayExample()

    Dim myArray() As Variant

    myArray = Array("apple", "banana", "cherry", "date", "elderberry")

    numRows = UBound(myArray)

For i = 0 To numRows
        ActiveSheet.Cells(5 + i, 2).Value = myArray(i)
     Next i

Dim response As VbMsgBoxResult
    response = MsgBox("Your arrays are now present. Do you want to Expand your array dynamically?", _
    vbYesNoCancel, "Confirmation")

Select Case response
        Case vbYes

Dim sourceRange2 As String
    sourceRange = InputBox("Enter a Fruit Name", "Value Enter")
    sourceRange2 = InputBox("Enter another Fruit Name", "Value Enter")

ReDim Preserve myArray(UBound(myArray) + 2)
    myArray(UBound(myArray) - 1) = sourceRange
    myArray(UBound(myArray)) = sourceRange2
    numRows1 = UBound(myArray)
    For j = 0 To numRows1
        ActiveSheet.Cells(5 + j, 3).Value = myArray(j)
     Next j

Case vbNo
            MsgBox "You Decided to Quit"
            Case vbCancel
            MsgBox "You clicked Cancel."
    End Select

End Sub

VBA Array Initialization

Below we presented a simple 2-dimensional example array code initialization.

Sub Initialize_Static_Array()
Dim data(1 To 3, 1 To 3) As String
data(1, 1) = "Name"
data(1, 2) = "Age"
data(1, 3) = "Gender"
data(2, 1) = "John"
data(2, 2) = "32"
data(2, 3) = "Male"
data(3, 1) = "Jane"
data(3, 2) = "28"
data(3, 3) = "Female"
numRows = UBound(data, 1)
numCols = UBound(data, 2)
For i = 1 To numRows
For j = 1 To numCols
ActiveSheet.Cells(3 + i, 1 + j).Value = data(i, j)
Next j
Next i
End Sub

• And this is how we can actually initialize a 2D array in Excel.

Excel VBA Array Function: 6 Suitable Examples

Below we have provided 6 separate examples of Array-Functions used in Excel. In order to avoid any kind of compatibility issues, try to use the Excel 365 edition.

Example 1. Using VBA Array to Store Value-Satisfing Conditions

In this example, we are going to use the Array function to check whether the dataset is now satisfying the condition we put inside the code. If the dataset has found some value that satisfies the condition, it will save the value in the array and then we can show the values in the worksheet.

• For this need, we need to open the VBA code editor following the helper article.
• Then paste the following code in the code editor.
• In the code, we have set up the “Pending” in the order status and “Appetizer”

Sub Store_Id_that_satisfy_condition()
    Dim orderRange As Range
    Dim resultArray() As Variant
    Dim resultIndex As Long
    Dim cell As Range
    Set orderRange = Range("E5.E13")
    For Each cell In orderRange.Rows
        Do While cell.Value = "Pending"
            If cell.Offset(0, -1).Value = "Appetizer" Then
                ReDim Preserve resultArray(1 To resultIndex + 1)
                resultArray(resultIndex + 1) = cell.Offset(0, -2).Value
                resultIndex = resultIndex + 1
            End If
            Exit Do
        Loop
    Next cell
For i = 1 To UBound(resultArray)
MsgBox resultArray(i)
Next i
End Sub

Sub Store_Id_that_satisfy_condition()
    Dim orderRange As Range

    Dim resultArray() As Variant

    Dim resultIndex As Long

Dim cell As Range
    Set orderRange = Range("E5:E13")

For Each cell In orderRange.Rows
        Do While cell.Value = "Pending"

            If cell.Offset(0, -1).Value = "Appetizer" Then

ReDim Preserve resultArray(1 To resultIndex + 1)
                resultArray(resultIndex + 1) = cell.Offset(0, -2).Value

                resultIndex = resultIndex + 1

End If
            Exit Do

 Loop
    Next cell

For i = 1 To UBound(resultArray)
MsgBox resultArray(i)
Next i

• In the video given below, If the User presses the Run command button, the values that satisfy the conditions are going to be saved in the resultArray array.
• Then the element of that array then showed in the message box serially, as shown in the video down below.

Example 2. Nested Array Function

In this example, we are going to show how you can put an Array function inside another Array function.

• Open the Code Editor in the VBA.
• After pasting the code, press the Run command button.

Sub Array_inside_Array()
    Dim outerArray() As Variant
    Dim innerArray() As Variant
    Dim Name As Variant
    Dim Age As Variant
    Dim country As Variant
    Dim i As Long, j As Long
    Name = Array("John", "Robert", "Smith")
    country = Array("USA", "Canada", "England")
    Age = Array(29, 35, 58)
    ReDim outerArray(1 To 3)
    For i = LBound(outerArray) To UBound(outerArray)
        ReDim innerArray(1 To 3)
        innerArray(1) = Name(i - 1)
        innerArray(2) = Age(i - 1)
        innerArray(3) = country(i - 1)
        outerArray(i) = innerArray
    Next i
    For i = LBound(outerArray) To UBound(outerArray)
        ActiveSheet.Cells(4 + i, 2).Value = outerArray(i)(1)
        ActiveSheet.Cells(4 + i, 3).Value = outerArray(i)(2)
        ActiveSheet.Cells(4 + i, 4).Value = outerArray(i)(3)
    Next i
End Sub

Sub Array_inside_array():

Dim outerArray() As Variant:

Dim innerArray() As Variant:

Dim Name As Variant, Age As Variant, country As Variant:

Dim i As Long, j As Long:

Name = Array("John", "Robert", "Smith"):

country = Array("USA", "Canada", "England"):

Age = Array(29, 35, 58):

ReDim outerArray(1 To 3):

For i = LBound(outerArray) To UBound(outerArray):

ReDim innerArray(1 To 3):

innerArray(1) = Name(i - 1):

innerArray(2) = Age(i - 1):

innerArray(3) = country(i - 1):

outerArray(i) = innerArray:

Next i:

For i = LBound(outerArray) To UBound(outerArray):

ActiveSheet.Cells(4 + i, 2).Value = outerArray(i)(1):

ActiveSheet.Cells(4 + i, 3).Value = outerArray(i)(2):

ActiveSheet.Cells(4 + i, 4).Value = outerArray(i)(3):

Next i:

End Sub:

• After pressing the Run command, you will see that the values now showing in the worksheets.

Example 3. Store String Values in Array

In this example, we are going to show how we can store string values in the Array. And then we can put the stored Array data into the worksheet.

• in the article editor window, paste the code given below,

Sub StoreStringValuesInArray()
    Dim inputRange As Range
    Dim cell As Range
    Dim dataArray() As Variant
    Dim sentence As String
    Dim outputRange As Range
    Dim i As Long
    Set inputRange = Application.InputBox("Select the input range:", Type:=8)
    ReDim dataArray(1 To inputRange.Rows.Count, 1 To 1)
    i = 1
    For Each cell In inputRange
        dataArray(i, 1) = cell.Value
        i = i + 1
    Next cell
    Debug.Print UBound(dataArray)
    sentence = Join(Application.WorksheetFunction.Transpose(dataArray), " ")
    Debug.Print sentence
    Set outputRange = Worksheets("Store String Values to Array").Range("D5")
    outputRange.Value = sentence
End Sub

Sub StoreStringValuesInArray():

Dim inputRange As Range:

Dim cell As Range:

Dim dataArray() As Variant:

Dim sentence As String:

Dim outputRange As Range:

Dim i As Long:

Set inputRange = Application.InputBox("Select the input range:", Type:=8):

ReDim dataArray(1 To inputRange.Rows.Count, 1 To 1):

i = 1:

For Each cell In inputRange:

dataArray(i, 1) = cell.Value:

i = i + 1:

Next cell:

Debug.Print UBound(dataArray):

sentence = Join(Application.WorksheetFunction.Transpose(dataArray), " "):

Debug.Print sentence:

Set outputRange = Worksheets("Store String Values to Array").Range("D5"):

outputRange.Value = sentence:

End Sub:

• After pressing the Run command, we will see an input box that asks for an input range.
• Then click OK.

• After clicking OK, we will notice that the string saved is in the range of cells B5:B8.
• Then the elements in the range of B5:B8 are now joined together and placed in cell D5.

Example 4. Extend or Add an Element to the Array

Here, we will show how you can extend and add elements to an existing Array.

• in the code editor paste the given code below. Then press the Run command.

Sub ExtendArray()
    Dim my_Array() As Variant
    Dim temp_Array() As Variant
    Dim element As String
    Dim inputRange As Range
    Dim outputRange As Range
    Dim i As Long
    On Error Resume Next
    Set inputRange = Application.InputBox("Select the input range:", Type:=8)
    On Error GoTo 0
    If inputRange Is Nothing Then Exit Sub
    my_Array = inputRange.Value
    element = ActiveSheet.Cells(6, 4).Value
    temp_Array = my_Array
    ReDim my_Array(LBound(temp_Array) To UBound(temp_Array) + 1, 1 To 1)
    For i = LBound(temp_Array) To UBound(temp_Array)
        my_Array(i, 1) = temp_Array(i, 1)
    Next i
    my_Array(UBound(my_Array), 1) = element
    Set outputRange = inputRange.Offset(0, 4).Resize(UBound(my_Array) - LBound(my_Array) + 1, 1)
    outputRange.Value = my_Array
    For i = LBound(my_Array) To UBound(my_Array)
        Debug.Print my_Array(i, 1)
    Next i
End Sub

Sub StoreStringValuesInArray():

Dim inputRange As Range:

Dim cell As Range:

Dim dataArray() As Variant:

Dim sentence As String:

Dim outputRange As Range:

Dim i As Long:

Set inputRange = Application.InputBox("Select the input range:", Type:=8):

ReDim dataArray(1 To inputRange.Rows.Count, 1 To 1):

i = 1:

For Each cell In inputRange:

dataArray(i, 1) = cell.Value:

i = i + 1:

Next cell:

Debug.Print UBound(dataArray):

sentence = Join(Application.WorksheetFunction.Transpose(dataArray), " "):

Debug.Print sentence:

Set outputRange = Worksheets("Store String Values to Array").Range("D5"):

outputRange.Value = sentence:

End Sub:

• After pressing the Run command, we can see that there is an input box asking for the Range of cell, we enter B6:B8.

• After then we will see that the new list in now showing in the range of cell F6:F9

Example 5. Split String and Store Characters in an Array

An array could be a very effective tool to split the string and then save the split values in the array elements.

• in the code editor paste the given code below. Then press the Run command.

Sub SplitStringToLetters()
    Dim inputString As String
    Dim letters() As String
    Dim i As Long
    inputString = InputBox("Enter a string:")
    ReDim letters(1 To Len(inputString))
    For i = 1 To Len(inputString)
        letters(i) = Mid(inputString, i, 1)
    Next i
    For i = 1 To UBound(letters)
        Debug.Print letters(i)
    Next i
End Sub

Sub SplitStringToLetters():

Dim inputString As String:

Dim letters() As String:

Dim i As Long:

inputString = InputBox("Enter a string:"):

ReDim letters(1 To Len(inputString)):

For i = 1 To Len(inputString):

letters(i) = Mid(inputString, i, 1):

Next i:

For i = 1 To UBound(letters):

Debug.Print letters(i):

Next i:

End Sub:

• After running the code, there will be an input box asking for the string value.
• We enter Exceldemy in the input box, then press OK.

• After pressing OK we will notice that the string entered in the inputbox earlier is now separated into letters.

Example 6. Calculating SUM and Average of an Array

In this method, we will show how you can calculate the SUM and AVERAGE of the values stored in the Array in Excel.

• In the code editor, paste the following code

Sub CalculateSumAndAverage()
    Dim inputRange As Range
    Dim dataValues As Variant
    Dim dataArray() As Variant
    Dim sumResult As Double
    Dim averageResult As Double
    Dim i As Long
    On Error Resume Next
    Set inputRange = Application.InputBox("Select the input range:", Type:=8)
    On Error GoTo 0
    If inputRange Is Nothing Then Exit Sub
    dataValues = inputRange.Value
    ReDim dataArray(1 To inputRange.Rows.Count, 1 To inputRange.Columns.Count)
    dataArray = dataValues
    For i = LBound(dataArray, 1) To UBound(dataArray, 1)
        sumResult = sumResult + dataArray(i, 1)
    Next i
    averageResult = sumResult / (UBound(dataArray, 1) - LBound(dataArray, 1) + 1)
    ActiveSheet.Cells(5, 6).Value = sumResult
    ActiveSheet.Cells(8, 6).Value = averageResult
End Sub

Sub CalculateSumAndAverage():

Dim inputRange As Range: 

Dim dataValues As Variant: 

Dim dataArray() As Variant:

Dim sumResult As Double: 

Dim averageResult As Double: 

Dim i As Long: 

On Error Resume Next:

Set inputRange = Application.InputBox("Select the input range:", Type:=8): 

On Error GoTo 0: 

If inputRange Is Nothing Then Exit Sub: 

dataValues = inputRange.Value: 

ReDim dataArray(1 To inputRange.Rows.Count, 1 To inputRange.Columns.Count):

dataArray = dataValues: 

For i = LBound(dataArray, 1) To UBound(dataArray, 1):

sumResult = sumResult + dataArray(i, 1): 

Next i: 

averageResult = sumResult / (UBound(dataArray, 1) - LBound(dataArray, 1) + 1): 

ActiveSheet.Cells(5, 6).Value = sumResult: 

ActiveSheet.Cells(8, 6).Value = averageResult:

End Sub: 

• Then in the input box, enter the range that you are going use for the creation of Array.
• Click OK after this.

• After clicking OK, we can see that the Array has been created and the SUM and AVERAGE of the values are evaluated.
• The average and sum of the values are now present in the cell F5 and F8.

Advantages of Using Array Function

The Array function in programming languages like VBA (Visual Basic for Applications) provides several advantages:

• Convenience: The Array function allows you to quickly create an Array by providing the values directly within the function call. This saves you from explicitly declaring and populating each element of the Array individually.
• Compactness: Using the Array function makes your code more concise and readable. Instead of declaring and initializing an Array on separate lines, you can achieve the same result in a single line of code.
• Flexibility: You can use the Array function to create Arrays of any data type, including strings, numbers, dates, and objects. It allows you to mix and match different data types within a single Array.
• Dynamic sizing: You can easily adjust the size of the Array by adding or removing elements within the Array function. This provides flexibility in managing Arrays without explicitly resizing them using ReDim statements.
• Ease of modification: If you need to modify the elements of an Array, you can do so directly within the Array function call. This eliminates the need to locate and modify individual lines of code where the Array is populated.
• Efficiency: The Array function can improve code execution efficiency, especially when initializing Arrays with a fixed set of values. It reduces the number of lines of code and minimizes the processing required to populate the Array.

Overall, the Array function simplifies the process of creating and initializing Arrays, making your code more compact, readable, and efficient. It provides convenience, flexibility, and ease of modification, saving you time and effort in Array initialization.

Things to Remember

When using the Array function, there are a few important things to keep in mind:

• Data Types: Ensure that the values you provide to the Array function are of the appropriate data type. Mixing incompatible data types may lead to unexpected results or errors. For example, if you intend to create an Array of numbers, make sure all the values are numeric.
• Array Indexing: Arrays in many programming languages are zero-indexed, meaning the first element is accessed with index 0. Keep this in mind when working with Arrays, as accessing elements using incorrect indexes may result in out-of-bounds errors.
• Array Bounds: Be mindful of the size and dimensions of your Array. Ensure that the Array has sufficient capacity to hold all the elements you intend to store. Attempting to access or assign values beyond the Array bounds will result in runtime errors.
• Array Length: If you need to determine the length or size of an Array, use the appropriate method or property provided by the programming language. Avoid assuming a fixed length or hardcoding values, as it may lead to errors if the Array size changes.
• Memory Considerations: Arrays consume memory, so be mindful of memory usage, especially when working with large Arrays or in memory-constrained environments. Avoid unnecessary duplication or inefficient use of memory by carefully managing the size and scope of Arrays.
• Error Handling: When working with Arrays, it’s essential to handle any potential errors gracefully. Use appropriate error-handling techniques, such as try-catch blocks or error-checking conditions, to handle exceptions that may occur during Array operations.
• Array Mutability: Understand whether the Array is mutable or immutable. Some programming languages allow modifying the elements of an Array, while others treat Arrays as immutable, requiring you to create a new Array if you need some changes.
• Array Functions vs. Array Declarations: Distinct programming languages may have different Array functions or syntax. Be familiar with the specific Array functions and declaration methods for the language you are using to ensure proper usage.

By keeping these considerations in mind, you can effectively utilize Array functions and avoid common pitfalls associated with Array manipulation and usage.

Frequently Asked Questions

1. Getting the length of an Array

Any Array in the Excel VBA has elements, and this number of elements can be evaluated easily. For this, the user needs to use the below function line which will determine the length of the Array directly.

length = UBound(myArray) - LBound(myArray) + 1

If you use this line of code inside the code editor then the length of it will store the code in the length variable.You can use that variable value in any part of the code.

2. Sorting in VBA Array

Sorting in Excel VBA required a little workaround. You need to convert your Array to the worksheet, and then apply the regular sort function to it. We have shown the example code snippet below,

ws.Sort.SortFields.Add Key:=ws.Range("A1"), Order:=IIf(ascending, xlAscending, xlDescending)

Here the ws mean the worksheet which we are working on on now.

3. Increase the Length of the Array

We can easily redefine the declared previously using the Redim Preserve command. Here we have a small code snippet where we defined myArray, which is (1 by 3) dimensionalized.

Using ReDim will dimensionalize the existing Array, but doing it will remove the elements of the Array made earlier. If we use the ReDim Preserve, then we can preserve the Array elements created earlier. We have shown the example code snippet here.

Dim myArray(1 to 3) As Variant 
ReDim Preserve myArray(1 To newLength)

Conclusion

Here we have shown 6 separate examples, where we show separate datasets to show, in Excel VBA, how we can use the Array function. User needs to understand their own problem and then follow those example. If you need any more assistance regarding excel related problems, you can visit our site Exceldemy.