VBA One-Dimensional Array in Excel

One - Dimensional Array

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.

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
    '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.

VBA One dimensional array

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.

VBA Dynamic 1-dimensional array

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

Leave a Comment