VBA-Excel: Array Functions – LBound and UBound()

Description:

 LBound and UBound() Functions returns the starting index ( Lower Bound) and Ending index (Upper Bound) of an array.

Format:

LBound(arrArrayName [, dimension])

UBound(arrArrayName [, dimension])

Arguments:

• arrArrayName
  • Manda­tory
  • Type: Array
  • Array whose lower or upper bound needs to found

• dimension
  • Optional
  • Type: Numeric
  • If an array is one dimensional then no need to provide this argument but if it is Two or Multi dimensional array then you need to provide the dimension as well for which you want lower or upper bound. (Same way you can find the LBound and UBound of Single dimensional Dynamic array or Two dimensional Dynamic array)

Example:

Function FnLowerUpperBound()

    Dim arrOneDArray(1 To 5)

    Dim arrTwoDArray(1 To 5, -2 To 10)

    Dim arrMultiDArray(0 To 4, -2 To 4, 2 To 7)

  Dim strString

  strString = "LBound of arrOneDArray is: " & LBound(arrOneDArray) & vbCrLf

  strString = strString & "LBound of 2nd dimension of arrTwoDArray is: " & LBound(arrTwoDArray, 2) & vbCrLf

  strString = strString & "LBound of 3rd dimension of arrMultiDArray is: " & LBound(arrMultiDArray, 3) & vbCrLf

  MsgBox strString

  strString = "UBound of arrOneDArray is: " & UBound(arrOneDArray) & vbCrLf

  strString = strString & "UBound of 2nd dimension of arrTwoDArray is: " & UBound(arrTwoDArray, 2) & vbCrLf

  strString = strString & "UBound of 3rd dimension of arrMultiDArray is: " & UBound(arrMultiDArray, 3) & vbCrLf

  MsgBox strString

End Function

Also Read:

1. VBA-Excel : 3D-Ranges - Working with Multiple WorkSheets At The Simultaneously
2. VBA-Excel: String Functions – String()
3. VBA-Excel: Date-Time Functions – Hour(), Minute(), Second()
4. VBA Excel - Cells, Ranges and Offset: Refer Range by using A1 Notations
5. VBA Excel – Looping Through a Range of Cells