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.

Static Array Value Assigning

Let us see the example how to set values to static array.

'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

VBA Arrays other articles

You may also like the related VBA Array articles.

VBA Arrays in Excel