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
- "Excel VBA Programming for Dummies"; John Walkenbach; 2004
How to Initialize an Array in VBA
Visual Basic for Applications (VBA) is a version of Microsoft' Visual Basic programming language used within applications, ... VBA: How to Determine...
How to Add to an Array in VBA
Visual Basic for Applications (VBA) is a programming language used in login scripts. Administrators use VBA to program settings for users when...
How to Sort a String Array in VBA
Sorting a string array in Visual Basic for Applications (VBA) ... How to Sort Tabs Alphabetically With VBA in Excel; How to...
The Average Function in VBA
Visual Basic for Applications was Microsoft's first attempt to allow its MS Office software to have ... Create one by passing an...
How to Remove Duplicate Array Rows in Excel VBA
Removing duplicate data from an array using Visual Basic for Applications (VBA) in Excel can be ... How to Sort a String...
How to Delete Worksheets Using VBA
Excel VBA comes with a "Sheets" function that you can use to create, rename, ... For n = 0 To UBound(wSheets) On...
What Is UBound and LBound in Visual Basic?
Incorporating arrays in your Visual Basic (VB) application can become very handy if you know how to use them. VB is a...
How to Transfer Excel VB Data From Range to Array
When writing macros in Visual Basic for Applications ... “Microsoft Excel Programming”; Denise Etheridge; 2010 ... The Function of an Excel VBA...