Microsoft Office Tutorials and References
In Depth Information
5.4.9.3 The UBound function
A dynamic array is declared with empty parentheses, as in:
Dim FileName() as String
Dynamic arrays can be sized (or resized) using the ReDim statement, as in:
ReDim FileName(1 to 10)
This same array can later be resized again, as in:
ReDim FileName(1 to 100)
Note that resizing an array will destroy its contents unless we use the Preserve keyword, as in:
ReDim Preserve FileName(1 to 200)
However, when Preserve is used, we can only change the upper bound of the array (and only
the last dimension in a multidimensional array).
5.4.9.3 The UBound function
The UBound function is used to return the current upper bound of an array. This is very useful in
determining when an array needs redimensioning. To illustrate, suppose we want to collect an
unknown number of filenames in an array named FileName . If the next file number is
iNextFile , the following code checks to see if the upper bound is less than iNextFile ; if so,
it increases the upper bound of the array by 10, preserving its current contents, to make room for
the next filename:
If UBound(FileName) < iNextFile Then
ReDim Preserve FileName(UBound(FileName) + 10)
End If
Note that redimensioning takes time, so it is wise to add some "working room" at the top to cut
down on the number of times the array must be redimensioned. This is why we added 10 to the
upper bound in this example, rather than just 1. (There is a trade-off here between the extra time it
takes to redimension and the extra space that may be wasted if we do not use the entire
redimensioned array.)
5.4.10 Variable Naming Conventions
VBA programs can get very complicated, and we can use all the help we can get in trying to make
them as readable as possible. In addition, as time goes on, the ideas behind the program begin to
fade, and we must rely on the code itself to refresh our memory. This is why adding copious
comments to a program is so important.
Another way to make programs more readable is to use a consistent naming convention for
constants, variables, procedure names, and other items. In general, a name should have two
properties. First, it should remind the reader of the purpose or function of the item. For instance,
suppose we want to assign Chart variables to several Excel charts. The code:
Dim chrt1 As Chart, chrt2 as Chart
Set chrt1 = Charts("Sales")
Set chrt2 = Charts("Transactions")
Search JabSto ::




Custom Search