How to Use VBA Array Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

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

One dimentional array example

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

Two dimensional code example

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

Static or fixed array example

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

Dynamic array example

VBA Code Breakdown

Sub DynamicArrayExample()
  • This line defines the name of the macro.
    Dim myArray() As Variant
  • This line declares a dynamic array named “myArray” that will store an unspecified number of elements of any data type.
    myArray = Array("apple", "banana", "cherry", "date", "elderberry")
  • This line initializes the array “myArray” with five-string values.
    numRows = UBound(myArray)
  • This line assigns the number of rows in the array to the variable “numRows” by using the UBound function to get the upper bound of the array.
For i = 0 To numRows
        ActiveSheet.Cells(5 + i, 2).Value = myArray(i)
     Next i
  • This loop writes the values in “myArray” to cells in the second column of the current worksheet, starting at row 5.
Dim response As VbMsgBoxResult
    response = MsgBox("Your arrays are now present. Do you want to Expand your array dynamically?", _
    vbYesNoCancel, "Confirmation")
  • This line displays a message box with a confirmation message and three buttons: Yes, No, and Cancel. It assigns the user’s response to the variable “response”.
Select Case response
        Case vbYes
  • This line begins a Select Case statement that checks the value of “response”.
Dim sourceRange2 As String
    sourceRange = InputBox("Enter a Fruit Name", "Value Enter")
    sourceRange2 = InputBox("Enter another Fruit Name", "Value Enter")
  • These lines prompt the user to enter two fruit names, and store them in variables “sourceRange” and “sourceRange2”.
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
  • These lines resize the “myArray” array by two elements and assign the new values entered by the user. Then, it writes the updated array values to cells in the third column of the current worksheet, starting at row 5.
Case vbNo
            MsgBox "You Decided to Quit"
            Case vbCancel
            MsgBox "You clicked Cancel."
    End Select
  • These lines display message boxes with appropriate messages depending on which button the user clicked. If the user clicked “No”, the macro will display a message saying “You Decided to Quit”. If the user clicked “Cancel”, the macro will display a message saying “You clicked Cancel.”.
End Sub
  • This line marks the end of the macro.

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

initiation-of-the-static-2-dimensional-array

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

Array initialization


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

VBA code to return order id using the array function

VBA Code Breakdown

Sub Store_Id_that_satisfy_condition()
    Dim orderRange As Range

declared as a Range object to store a range of cells.

    Dim resultArray() As Variant

is declared as a Variant array to store the results.

    Dim resultIndex As Long

is declared as a Long integer to keep track of the index in resultArray.

Dim cell As Range
    Set orderRange = Range("E5:E13")
  • The “orderRange” variable is assigned to refer to the range of cells from E5 to E13.
For Each cell In orderRange.Rows
        Do While cell.Value = "Pending"
  • A loop is initiated to iterate through each cell in the rows of “orderRange“.
  • Inside the loop, there is a Do-While loop that continues as long as the value of the current cell is “Pending“. This loop is used to process only the cells with the “Pending” value.
            If cell.Offset(0, -1).Value = "Appetizer" Then
  • Within the Do-While loop, an If statement checks if the cell one column to the left (Offset(0, -1)) has the value “Appetizer“.
ReDim Preserve resultArray(1 To resultIndex + 1)
                resultArray(resultIndex + 1) = cell.Offset(0, -2).Value
  • If the condition is true, the “resultArray” is resized using ReDim Preserve to accommodate an additional element, and the value of the cell two columns to the left (Offset(0, -2)) is assigned to the next index of “resultArray”.
                resultIndex = resultIndex + 1
  • The “resultIndex” variable is incremented by 1 to keep track of the index in “resultArray“.       
End If
            Exit Do
  • The Do-While loop is then exited using Exit Do.
 Loop
    Next cell
  • The code moves to the next cell in “orderRange” and repeats the process for each 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

VBA Array Function Inside Another Function

VBA Code Breakdown

Sub Array_inside_array():
  • This line defines the start of a subroutine (sub) named “Array_inside_array”.
Dim outerArray() As Variant:
  • This line declares a dynamic Array named “outerArray” to store the outer Array structure.
Dim innerArray() As Variant:
  • This line declares a dynamic Array named “innerArray” to store the inner Array structure.
Dim Name As Variant, Age As Variant, country As Variant:
  • This line declares variables “Name,” “Age,” and “country” as Variant types to store individual values.
Dim i As Long, j As Long:
  • This line declares variables “i” and “j” as Long integer types to be used as loop counters.
Name = Array("John", "Robert", "Smith"):
  • This line assigns an Array of names to the “Name” variable.
country = Array("USA", "Canada", "England"):
  • This line assigns an Array of countries to the “country” variable.
Age = Array(29, 35, 58):
  • This line assigns an Array of ages to the “Age” variable.
ReDim outerArray(1 To 3):
  • This line resizes the “outerArray” to have three elements.
For i = LBound(outerArray) To UBound(outerArray):
  • This line starts a loop that iterates from the lower bound to the upper bound of the “outerArray”.
ReDim innerArray(1 To 3):
  • This line resizes the “innerArray” to have three elements.
innerArray(1) = Name(i - 1):
  • This line assigns the name value from the “Name” Array to the first element of the “innerArray”.
innerArray(2) = Age(i - 1):
  • This line assigns the age value from the “Age” Array to the second element of the “innerArray”.
innerArray(3) = country(i - 1):
  • This line assigns the country value from the “country” Array to the third element of the “innerArray”.
outerArray(i) = innerArray:
  • This line assigns the “innerArray” to the current index of the “outerArray“.
Next i:
  • This line denotes the end of the first loop.
For i = LBound(outerArray) To UBound(outerArray):
  • This line starts another loop that iterates from the lower bound to the upper bound of the “outerArray“.
ActiveSheet.Cells(4 + i, 2).Value = outerArray(i)(1):
  • This line assigns the value of the first element of the “innerArray” at the current index of the “outerArray” to a cell in the ActiveSheet.
ActiveSheet.Cells(4 + i, 3).Value = outerArray(i)(2):
  • This line assigns the value of the second element of the “innerArray” at the current index of the “outerArray” to a cell in the ActiveSheet.
ActiveSheet.Cells(4 + i, 4).Value = outerArray(i)(3):
  • This line assigns the value of the third element of the “innerArray” at the current index of the “outerArray” to a cell in the ActiveSheet.
Next i:
  • This line denotes the end of the second loop.
End Sub:
  • This line marks the end of the subroutine “Array_inside
  • After pressing the Run command, you will see that the values now showing in the worksheets.

VBA code to put values in the worksheet using array inside another array


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

VBA code to store string value in the array

VBA Code Breakdown

Sub StoreStringValuesInArray():
  • This line defines the start of a subroutine (sub) named “StoreStringValuesInArray”.
Dim inputRange As Range:

This line declares a variable named “inputRange” as a Range object.

Dim cell As Range:
  • This line declares a variable named “cell” as a Range object.
Dim dataArray() As Variant:
  • This line declares a dynamic Array named “dataArray” to store the string values.
Dim sentence As String:
  • This line declares a variable named “sentence” as a String to store the combined sentence.
Dim outputRange As Range:
  • This line declares a variable named “outputRange” as a Range object.
Dim i As Long:
  • This line declares a variable named “i” as a Long integer to be used as a loop counter.
Set inputRange = Application.InputBox("Select the input range:", Type:=8):
  • This line displays an input box and prompts the user to select a range. The selected range is assigned to the “inputRange” variable.
ReDim dataArray(1 To inputRange.Rows.Count, 1 To 1):
  • This line resizes the “dataArray” to match the number of rows in the selected range.
i = 1:
  • This line initializes the “i” variable to 1.
For Each cell In inputRange:
  • This line starts a loop that iterates through each cell in the selected range.
dataArray(i, 1) = cell.Value:
  • This line assigns the value of the current cell to the corresponding element in the “dataArray”.
i = i + 1:
  • This line increments the “i” variable by 1.
Next cell:
  • This line denotes the end of the loop.
Debug.Print UBound(dataArray):
  • This line prints the upper bound (number of elements) of the “dataArray” to the Immediate window for debugging purposes.
sentence = Join(Application.WorksheetFunction.Transpose(dataArray), " "):
  • This line uses the Join function to combine the elements of the “dataArray” into a sentence, separated by a space. The resulting sentence is assigned to the “sentence” variable.
Debug.Print sentence:
  • This line prints the “sentence” variable to the Immediate window for debugging purposes.
Set outputRange = Worksheets("Store String Values to Array").Range("D5"):
  • This line sets the “outputRange” variable to refer to cell D5 on the worksheet named “Store String Values to Array”.
outputRange.Value = sentence:
  • This line assigns the value of the “sentence” variable to the “outputRange” cell.
End Sub:
  • This line marks the end of the subroutine “StoreStringValuesInArray”.
  • After pressing the Run command, we will see an input box that asks for an input range.
  • Then click OK.

inoutbox asking for the input range which will use as array element

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

Joined sentence after joining saved array element


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

Extend the array size and add element using Array function

VBA Code Breakdown

Sub StoreStringValuesInArray():
  • This line defines the start of a subroutine (sub) named “StoreStringValuesInArray”.
Dim inputRange As Range:
  • This line declares a variable named “inputRange” as a Range object.
Dim cell As Range:
  • This line declares a variable named “cell” as a Range object.
Dim dataArray() As Variant:
  • This line declares a dynamic Array named “dataArray” to store the string values.
Dim sentence As String:
  • This line declares a variable named “sentence” as a String to store the combined sentence.
Dim outputRange As Range:
  • This line declares a variable named “outputRange” as a Range object.
Dim i As Long:
  • This line declares a variable named “i” as a Long integer to be used as a loop counter.
Set inputRange = Application.InputBox("Select the input range:", Type:=8):
  • This line displays an input box and prompts the user to select a range. The selected range is assigned to the “inputRange” variable.
ReDim dataArray(1 To inputRange.Rows.Count, 1 To 1):
  • This line resizes the “dataArray” to match the number of rows in the selected range.
i = 1:
  • This line initializes the “i” variable to 1.
For Each cell In inputRange:
  • This line starts a loop that iterates through each cell in the selected range.
dataArray(i, 1) = cell.Value:
  • This line assigns the value of the current cell to the corresponding element in the “dataArray”.
i = i + 1:
  • This line increments the “i” variable by 1.
Next cell:
  • This line denotes the end of the loop.
Debug.Print UBound(dataArray):
  • This line prints the upper bound (number of elements) of the “dataArray” to the Immediate window for debugging purposes.
sentence = Join(Application.WorksheetFunction.Transpose(dataArray), " "):
  • This line uses the Join function to combine the elements of the “dataArray” into a sentence, separated by a space. The resulting sentence is assigned to the “sentence” variable.
Debug.Print sentence:
  • This line prints the “sentence” variable to the Immediate window for debugging purposes.
Set outputRange = Worksheets("Store String Values to Array").Range("D5"):
  • This line sets the “outputRange” variable to refer to cell D5 on the worksheet named “Store String Values to Array”.
outputRange.Value = sentence:
  • This line assigns the value of the “sentence” variable to the “outputRange” cell.
End Sub:
  • This line marks the end of the subroutine “StoreStringValuesInArray”.
  • After pressing the Run command, we can see that there is an input box asking for the Range of cell, we enter B6:B8.

Select the range of cell for the input range

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

New List with the Added new element in the aray


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

VBA code to split string values into letters

VBA Code Breakdown

Sub SplitStringToLetters():
  • This line defines the start of a subroutine called “SplitStringToLetters”.
Dim inputString As String:
  • This line declares a variable called “inputString” as a String data type. It will store the input string.
Dim letters() As String:
  • This line declares an Array variable called “letters” to store individual letters from the input string.
Dim i As Long:
  • This line declares a variable called “i” as a Long data type.
inputString = InputBox("Enter a string:"):
  • This line displays an input box prompt asking the user to enter a string. The entered string is assigned to the “inputString” variable.
ReDim letters(1 To Len(inputString)):
  • This line resizes the “letters” Array to match the length of the “inputString” variable. The Array will store each letter of the string.
For i = 1 To Len(inputString):
  • This line starts a loop that iterates from 1 to the length of the “inputString”.
letters(i) = Mid(inputString, i, 1):
  • This line assigns the individual letter at the current position (i) of the “inputString” to the corresponding element of the “letters” Array.
Next i:
  • The “Next i” line indicates the end of the loop and moves to the next iteration.
For i = 1 To UBound(letters):
  • This line starts a loop that iterates from 1 to the upper bound of the “letters” Array.
Debug.Print letters(i):
  • This line prints the value of the current element (i) in the “letters” Array to the Immediate window in the VBA editor. It will display each letter on a new line.
Next i:
  • The “Next i” line indicates the end of the loop and moves to the next iteration.
End Sub:
  • The “End Sub” line marks the end of the subroutine.
  • 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.

Inoutbox asking for the string value to be separated

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

output in the immediate window showing the string value in separated letter


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

VBA Code to calculate the sum and average of the array element

VBA Code Breakdown

Sub CalculateSumAndAverage():
  • This line defines the start of a subroutine called “CalculateSumAndAverage”.
Dim inputRange As Range: 
  • This line declares a variable called “inputRange” as a Range object. The Range object represents a cell, a range of cells, or multiple ranges in a worksheet.
Dim dataValues As Variant: 
  • This line declares a variable called “dataValues” as a Variant data type. The Variant data type can store any type of data.
Dim dataArray() As Variant:
  • This line declares an Array variable called “dataArray” to store the values from the input range. The Array is declared without a specific size.
Dim sumResult As Double: 
  • This line declares a variable called “sumResult” as a Double data type. We used the Double data type to store decimal numbers.
Dim averageResult As Double: 
  • This line declares a variable called “averageResult” as a Double data type.
Dim i As Long: 
  • This line declares a variable called “i” as a Long data type. Here we used the Long data type to store integers.
On Error Resume Next:
  • This line enables error handling and instructs the program to continue execution if an error occurs.
Set inputRange = Application.InputBox("Select the input range:", Type:=8): 
  • This line prompts the user to select a range on the worksheet using an input box. We assigned the selected range to the “inputRange” variable.
On Error GoTo 0: 
  • This line disables the error handling, so any errors that occur after this line will generate an error message.
If inputRange Is Nothing Then Exit Sub: 
  • This line checks if the “inputRange” variable is empty (i.e., if the user canceled the input box). If it is empty, the subroutine is exited.
dataValues = inputRange.Value: 
  • This line assigns the values from the selected range to the “dataValues” variable.
ReDim dataArray(1 To inputRange.Rows.Count, 1 To inputRange.Columns.Count):
  • This line resizes the “dataArray” Array to match the size of the selected range.
dataArray = dataValues: 
  • This line copies the values from the “dataValues” variable to the “dataArray” Array.
For i = LBound(dataArray, 1) To UBound(dataArray, 1):
  • This line starts a loop that iterates over the rows of the “dataArray” Array.
sumResult = sumResult + dataArray(i, 1): 
  • This line adds the value in the current row of the first column of the “dataArray” Array to the “sumResult” variable.
Next i: 
  • This line indicates the end of the loop and moves to the next iteration.
averageResult = sumResult / (UBound(dataArray, 1) - LBound(dataArray, 1) + 1): 
  • This line calculates the average by dividing the “sumResult” variable by the number of rows in the “dataArray” Array.
ActiveSheet.Cells(5, 6).Value = sumResult: 
  • This line assigns the value of “sumResult” to the cell in the 5th row and 6th column of the active worksheet.
ActiveSheet.Cells(8, 6).Value = averageResult:
  • This line assigns the value of “averageResult” to the cell in the 8th row and 6th column of the active worksheet.
End Sub: 
  • This line marks the end of the subroutine.
  • Then in the input box, enter the range that you are going use for the creation of Array.
  • Click OK after this.

Inputbox to ask for the input range of the array

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

Sum and Average of the Marks values from the input ranges


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo