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 of VBA 1-Dimensional Array in Excel
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 use Macro Code
Related VBA Arrays Articles

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 argumnts. It represents index or subscript value.
and DataType: It represnts the type of an array variables.

Example on VBA Static 1Dimensional Array in Excel

Let us see the example VBA macro code on Static 1Dimensional 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 1Dimensional Array in Excel

Let us see the example VBA macro code on dynamic 1Dimensional 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.

Instructions to use Macro

Here are the instructions to use above macro in Visual basic editor.

Open Visual Basic Editor(VBE) by clicking Alt +F11
Go to code window by clicking F7
Copy above specified macro or procedure
Paste above copied code in code window
Run macro by clicking F5 or Run command
You can see output on the screen
Find above specified output screenshot.