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.

  • 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.


  • Photo Credit Photos.com/AbleStock.com/Getty Images
Promoted By Zergnet



  • "Excel VBA Programming for Dummies"; John Walkenbach; 2004

You May Also Like

Related Searches

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!