Assigning Values to an Array in Excel VBA

VBA Assigning Values to an Array in Excel. An array values are assigned to an element based on an array index or subscript. Defining values for both static and dynamic array types are same. Let us see how to assign values to static and dynamic array.

Table of Contents:

Overview
Static Array Value Assigning
Dynamic Array Value Assigning
Instructions to Run VBA Macro Code
Other Useful Resources

Static Array Value Assigning

Let us see the example how to set values to static array. The array name is aArrayName and (0 To 2) means it stores 3 values.

'VBA Assigning Values to an Array
Sub VBA_Assigning_Values_to_Array()

    'Declare an Array variable
    Dim aArrayName(0 To 2) As Integer

    'Set the value of array index 0
    aArrayName(0) = 5

    'Set the value of array index 1
    aArrayName(1) = 10

    'Set the value of array index 2
    arrMarks(2) = 15

End Sub

Dynamic Array Value Assigning

Let us see the example how to set values to dynamic array. In the below example we used ReDim and Preserve keywords. ReDim statement is helps to define the array size during run time. And Preserve keyword helps to retain all existing elements in an array.

'VBA Assigning Values to an Array
Sub VBA_Assigning_Values_to_Array_Ex2()

    'Declare an Array variable
    Dim aArrayName() As String
    ReDim aArrayName(0 To 2) As String

    'Set the value of array index 0
    aArrayName(0) = "East"

    'Set the value of array index 1
    aArrayName(1) = "West"

    'Set the value of array index 2
    arrMarks(2) = "South"
    
    ReDim Preserve aArrayName(3) As String
    'Set the value of array index 3
    arrMarks(3) = "North"

End Sub