VBA One-Dimensional Array in Excel

VBA One-Dimensional Array in Excel. The One(1)-Dimensional Array uses only one index(Subscript). The one-dimensional array consists of list of items of same data type. It consists of either single row or column data. We read values from an array or into an array using index value. The one dimensional array can be created in static array or dynamic array. An array can be resized with ReDim statement. Static array is never empty and dynamic array can be empty if we use erase statement.

Table of Contents:

Overview
Syntax of the VBA One-Dimensional Array in Excel
Example on VBA Static 1Dimensional Array in Excel
Example on VBA Dynamic 1Dimensional Array in Excel
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax of the VBA one 1Dimensional Array

Here is the Syntax of the 1Dimensional Array Function in Excel VBA.

Dim ArrayName(IndexNumber) As DataType

where IndexNumber: These are mandatory arguments. It represents index or subscript value.
and DataType: It represents the type of an array variables.

Example on VBA Static 1Dimensional Array in Excel

Let us see the example VBA macro code on Static 1 Dimensional Array in Excel

'VBA Static 1-Dimensional Array
Sub VBA_Static_One_Dimensional_Array()

    'Declare Variable
    Dim aColor(0 To 2) As String
    'or
    'Dim aColor(2) As String
    
    'Define an Array values
    aColor(0) = "Blue"
    aColor(1) = "Green"
    aColor(2) = "Yellow"
        
    'Display Output on the Screen
    MsgBox "The first array element: " & aColor(0) & vbCrLf & vbCrLf & _
    "The second array element: " & aColor(1) & vbCrLf & vbCrLf & _
    "The third array element: " & aColor(2), vbInformation, "VBAF1"
    
End Sub

Here is the screenshot of above macro vba code.

Example on VBA Dynamic 1 Dimensional Array in Excel

Let us see the example VBA macro code on dynamic 1 Dimensional Array in Excel.

'VBA Dynamic 1Dimensional Array
Sub VBA_Dynamic_One_Dimensional_Array()

    'Declare Variable
    Dim aType() As String
    
    'Initialize an array size
    ReDim aType(1)
    
    'Define an Array values
    aType(0) = "Static"
    aType(1) = "Dynamic"
        
    'Display Output on the Screen
    MsgBox "The first array element: " & aType(0) & vbCrLf & vbCrLf & _
    "The second array element: " & aType(1), vbInformation, "VBAF1"
    
End Sub

Let us see the screen shot of above macro procedure.