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
