VBA Function UBound

Ubound function is to identify highest number for array sequence normally specify by user. For example Dim myArry(10) then Ubound = 10, another example Dim myArray(5 to 10) then Ubound = 10. In case dynamic array Dim myArray() then Ubound depend on Redim statement assigned in our code. Usually we Ubound pair with Lbound function to loop array from lowest to highest value.

Syntax : Ubound(arrayname,Dimension) , Returns : Numeric

Example of Dimension
MyArray(10) = Single Dimension
MyArray(5 to 10) = Single Dimension
MyArray(2 to 10, 5 to 12) = 2 Dimension (Key in 1 or 2 for dimension)
MyArray(2 to 10, 5 to 12, 4 to 10) = 3 Dimension (Key in 1, 2 or 3) and etc

VBA Vode:

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_UBoundArray()
    
    Dim myArray1(10)
    Dim myArray2(5 To 8)
    Dim myArray3(2 To 5, 3 To 15)
    
    Debug.Print UBound(myArray1) 'Answer = 10
    Debug.Print UBound(myArray2) 'Answer = 8
    Debug.Print UBound(myArray3, 1) 'Answer = 5
    Debug.Print UBound(myArray3, 2) 'Answer = 15

End Sub

Note: 

For single dimension array we don't to specify dimension in syntax.

Microsoft Reference-Ubound-function
Other Reference-Ubound-function

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

VBA Function LBound

Lbound function is to identify lowest number for array sequence by default is 0, with Option Base 1 on top of module then become 1 otherwise specify. For example Dim myArry(10) then Lbound = 0, with Option Base 1 then LBound = 1, another example Dim myArray(5 to 10) then Lbound = 5. Usually we Lbound pair with Ubound function to loop array from lowest to highest value.

Syntax : Lbound(arrayname, Dimension) , Returns : Numeric

Example of Dimension
MyArray(10) = Single Dimension
MyArray(5 to 10) = Single Dimension
MyArray(2 to 10, 5 to 12) = 2 Dimension (Key in 1 or 2 for dimension)
MyArray(2 to 10, 5 to 12, 4 to 10) = 3 Dimension (Key in 1, 2 or 3) and etc

VBA Vode:

Option Explicit
Option Base 1 ' Set default array subscripts to 1.
Sub Examples_LBoundArray()
    
    Dim myArray1(10)
    Dim myArray2(5 To 10)
    Dim myArray3(5 To 10, 3 To 15)
    
    Debug.Print LBound(myArray1) 'Answer = 1 (Option Base 1)
    Debug.Print LBound(myArray2) 'Answer = 5
    Debug.Print LBound(myArray3, 1) 'Answer = 5
    Debug.Print LBound(myArray3, 2) 'Answer = 3

End Sub

Note: 

For single dimension array we don't have to specify dimension in syntax.

Microsoft Reference-Lbound-function
Other Reference-Lbound-function

Practice makes perfect. Thank You.

macro enabled excel
excel macro
vba coding
vba code

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