VBA: How to Determine an Array Size

Found This Helpful

Using arrays in Visual Basic for Applications, or VBA, procedures efficiently manages large amounts of data, and saves memory and execution time. Arrays are a collection of elements of the same variable type and referred to by the same variable name. If you know the number of elements for a specific variable, you can set the array to that number. However, if you are unsure what array size to use, then use a dynamic array. Dynamic arrays resize automatically, depending on the amount of the data. You can determine the size of an array using the UBound and LBound functions in your procedure.


  1. Open the Excel file containing the procedure for which you want to determine the array size.

  2. Open the VBA Editor to view your procedure. A short-cut is to press "ALT-F11" in your Excel workbook.

  3. Locate in your VBA procedure where to add the UBound and LBound functions. Usually these functions would come after the array in the procedure.

  4. Enter the UBound and LBound functions by typing the following:

    MsgBox UBound(ArrayName) - LBound(ArrayName) + 1

    VBA displays the array size in a message box.




  • "Excel VBA Programming for Dummies"; John Walkenbach; 2004
  • Photo Credit Photos.com/AbleStock.com/Getty Images

You May Also Like


Read Article

iPhone Screen Tips You Can Really Use