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: Delete Blank Rows from Excel Work Sheet
2. VBA Excel – Looping Through a Range of Cells
3. VBA-Excel: Date-Time Functions – DateValue()
4. VBA-Excel: String Functions – Space()
5. VBA-Excel : Strings Functions – Instr