VBA: How to Determine an Array Size

Shares & Saves

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.

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

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

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

  • 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

Your Black Friday/Cyber Monday Survival Guide