VBA: How to Determine an Array Size

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

Watch Video

#eHowHacks: How to Use Earbuds as a Selfie Remote