Initialize Static, Dynamic and Variant Arrays
For complete information on arrays, see Arrays
This code demonstrates three ways to initialize and display arrays.
Program Code
Option Explicit
Option Base 1
Public Sub ArrayInitializationMethods()
Dim i As Integer
Dim strType As Variant
Dim rngToLoad1 As Range
Dim rngToLoad2 As Range
Set rngToLoad1 = Range("A3:A5")
Set rngToLoad2 = Range("C5:E5")
' ********************************************
' Static Array
' ********************************************
Dim strStaticArray(1 To 3) As String
' ********************************************
' Variant Array
' ********************************************
Dim varArray As Variant
' ********************************************
' Dynamic Array
' ********************************************
Dim strDynamicArray() As String
' ********************************************
' Initialize Static Array
' ********************************************
strStaticArray(1) = "Cat"
strStaticArray(2) = "Dog"
strStaticArray(3) = "Rabbit"
' ********************************************
' Initialize Variant Array
' ********************************************
varArray = Array("Cat", "Dog", "Rabbit")
' ********************************************
' Initialize Dynamic Array
' ********************************************
For i = 1 To 3
ReDim Preserve strDynamicArray(1 To i)
strDynamicArray(i) = strStaticArray(i)
Next i
' ********************************************
' Display Arrays Using Subscripts
' ********************************************
For i = 1 To 3
Debug.Print strStaticArray(i)
Next i
' ********************************************
' Display Arrays Using Lower and Upper Bounds
' ********************************************
For i = LBound(strDynamicArray) To UBound(strDynamicArray)
Debug.Print strDynamicArray(i)
Next i
' ********************************************
' Display Arrays Using Collections
' ********************************************
For Each strType In strDynamicArray
Debug.Print strType
Next strType
' ********************************************
' Load Worksheet From Array
' ********************************************
rngToLoad1 = Application.Transpose(strDynamicArray)
rngToLoad2 = strDynamicArray
End Sub