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
Other People Are Reading
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 shortcut 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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for