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, primarily members of the Microsoft...
How to Transfer Excel VB Data From Range to Array
An array is a data structure that enables you to work with sets of data values in a single structure rather than...
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 Remove Duplicate Array Rows in Excel VBA
Removing duplicate data from an array using Visual Basic for Applications (VBA) in Excel can be confusing if you're not an experienced...
How to Sort a String Array in VBA
Sorting a string array in Visual Basic for Applications (VBA) is not as simple as in other, more recent programming languages. In...
How to Delete Worksheets Using VBA
In Microsoft Excel, you can remove a worksheet by right-clicking on its tab and selecting "Delete" from the context menu. A small...
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...
The Average Function in VBA
Visual Basic for Applications is a modified version of the Visual Basic programming language for use in scripting Microsoft programs, such as...