Why and How to declare Array

Array declare same as any other variables by using the Dim, Static, Private, or Public statements. Data type also same for examples Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, or Variant. The only different array is the size can be specify or dynamic for example in variables we use Dim iNum1 as integer, Dim iNum2 as integer,... and etc, but with array we use Dim iNum(5) as integer then we can use in our code iNum(0),iNum(1),... until 5. Array can be devided into:

• Fixed Array
  Example: Dim iNum(10) as integer
                  Dim iNum(1 to 10) as integer (Override default for LBound)
  
• Rectangular Array or 2 Dimension Array
  Example: Dim iNum(3,4) as integer
                  Dim MyArray(1 To 10, 5 To 15, 10 To 20) (Override default for LBound)
• Dynamic Array
  Example: Dim iNum() as integer
  Note: We must use Redim iNum(1) or Redim Preserve iNum(1) in code
  

Default starting number is 0 unless we use Option Base statement at module level, meaning on top of module we put Option Base 1. For Example Dim iNum(3) as integer then we can use iNum(0), iNum(1), iNum(2) and iNum(3) but with Option Base 1 we only can use iNum(1), iNum(2) and iNum(3). If your code still assigned 0 to array then error code "Run-time error '9': Subscript out of range" will appeared.

VBA Vode: (Without Option Base 1)

Option Explicit
Sub Examples_ArrayFixed()
    Dim i As Integer
    Dim IntNum(5) As Integer
    
    IntNum(0) = 20
    IntNum(1) = 15
    IntNum(2) = 23
    IntNum(3) = 18
    IntNum(4) = 28
    IntNum(5) = 35
    
    For i = LBound(IntNum) To UBound(IntNum)
        Debug.Print IntNum(i)
    Next
End Sub

Note: Starting number is 0 (Lower Bound)

VBA Vode: (With Option Base 1)

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_ArrayFixed()
    Dim i As Integer
    Dim IntNum(5) As Integer

    IntNum(1) = 15
    IntNum(2) = 23
    IntNum(3) = 18
    IntNum(4) = 28
    IntNum(5) = 35
    
    For i = LBound(IntNum) To UBound(IntNum)
        Debug.Print IntNum(i)
    Next
End Sub

Note: Starting number is 1 (Lower Bound)

VBA Vode: (Example for Rectangular Array)

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_RectangularArray()
    Dim i As Integer, j As Integer
    Dim IntNum(2, 3) As Integer

    IntNum(1, 1) = 15
    IntNum(1, 2) = 23
    IntNum(1, 3) = 18
    IntNum(2, 1) = 28
    IntNum(2, 2) = 35
    IntNum(2, 3) = 15
    
    For i = LBound(IntNum, 1) To UBound(IntNum, 1) 'Row Index
        For j = LBound(IntNum, 2) To UBound(IntNum, 2) 'Column Index
            Debug.Print IntNum(i, j)
        Next j
    Next i
End Sub

Note: Both Starting point for Row and Column index =1

VBA Vode: (Example for Dynamic Array with Redim)

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_DynamicArray()
    Dim i As Integer
    Dim IntNum() As Integer

    ReDim IntNum(1): IntNum(1) = 15
    ReDim IntNum(2): IntNum(2) = 23
    ReDim IntNum(3): IntNum(3) = 18
    ReDim IntNum(4): IntNum(4) = 28
    ReDim IntNum(5): IntNum(5) = 35
    
    For i = LBound(IntNum) To UBound(IntNum)
        Debug.Print IntNum(i) 'Answer = 0,0,0,0,35
    Next i
End Sub

Note: The answer for you get is all 0 except the last one which is IntNum(5) = 35. Meaning every time we Redim the array the previous data store in previous array will be deleted.

VBA Vode: (Example for Dynamic Array with Redim Preserve)

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_DynamicArray()
    Dim i As Integer
    Dim IntNum() As Integer

    ReDim Preserve IntNum(1): IntNum(1) = 15
    ReDim Preserve IntNum(2): IntNum(2) = 23
    ReDim Preserve IntNum(3): IntNum(3) = 18
    ReDim Preserve IntNum(4): IntNum(4) = 28
    ReDim Preserve IntNum(5): IntNum(5) = 35
    
    For i = LBound(IntNum) To UBound(IntNum)
        Debug.Print IntNum(i) 'Answer = 15,23,18,28,35
    Next i
End Sub

Note: To keep your Array data after Redim we must use Redim Preserve statement.

Microsoft Reference-Declaring-arrays
Microsoft Reference-Option-base-statement
Other Reference-Array

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code