VBA: How to Determine an Array Size

Written by deborah kulish Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
VBA: How to Determine an Array Size
Find a VBA array size using the UBound and LBound functions. (Photos.com/AbleStock.com/Getty Images)

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.

Skill level:
Moderately Challenging


  1. 1

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

  2. 2

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

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

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.