Programming Fundamentals Using VBA - Dynamic Arrays
(Page 9 of 9 )
Many programmers consider the concept of a dynamic array in VBA a slight programming fiction. Essentially, it is still a static array, but you do not declare the size until the program is running. So the only issue is when the size is being declared.
You start off by declaring an empty array. For example:
Dim intMyScores() As Integer
Then you use a keyword, ReDim, to redeclare the size of the array while the program is running and it is known what the size will be.
Let’s redo our previous example to demonstrate a dynamic array:
Sub arrayTest()
Dim i As Integer
Dim intMyScores() As Integer
Dim intArraySize As Integer
intArraySize = InputBox("How many scores are you entering?", "Array Size")
ReDim intMyScores(intArraySize)
For i = 1 To intArraySize
intMyScores(i) = InputBox("Enter number " & i, "Static Array Test")
Next
For i = 0 To intArraySize
Debug.Print "For array element " & i & " the number is " & _
intMyScores(i)
Next
End Sub
You will notice that we first declare intMyScores as an empty array. Then, as shown, we use the ReDim keyword to redefine it as a static array, with the upper bound being controlled by intArraySize, which is entered by the user.
From there on, we just use intArraySize to control the loops.
If you work through this example, you will see a contradiction. If you enter 5 as the number of scores that you want to input, you end up inputting 6 because the index starts at 0. This is a frequent mistake of beginning programmers.
With just a little bit of recoding, you can reassign the first element as 1 instead of 0. This would help keep things a bit more coordinated. Take a look at the following code:
Sub arrayTest()
Dim i As Integer
Dim intMyScores() As Integer
Dim intArraySize As Integer
intArraySize = InputBox("How many scores are you entering?", "Array Size")
ReDim intMyScores(1 To intArraySize)
For i = 1 To intArraySize
intMyScores(i) = InputBox("Enter number " & i, "Static Array Test")
Next
For i = 1 To intArraySize
Debug.Print "For array element " & i & " the number is " & intMyScores(i)
Next
End Sub
If you run this in the Immediate window, you should see the result shown here:
Just as intArray is the upper bound of our array, the 1 is now the lower bound of the array. We then have the two For…Next loops starting at 1. (Remember, there is no element 0 now.)
There is another technique for starting the array at index position 1. In the general declarations section, you could type either Option Base 0 or Option Base 1. This will set the default lower bound of the arrays in the module. The only two options are 0 and 1.
Out of Bounds What happens if you make a programming error and end up trying to access more elements in the array than you declared using either Dim or ReDim?
VBA will not catch this until the program is actually running, not during the coding. This sort of error is called a runtime error. If that happens, you will get a message like this:
If you click Debug, it will show you where the program thinks the offending line of code is.
Be very careful! The line it brings you to is the point at which the code “crashed.” However, many times the crash happens due to things going bad earlier in the program. For instance, you may have set a variable to a wrong size or reference; or perhaps Dim or ReDim declared a wrong value. Finding the problem may require a bit of detective work on your part.
Making the Array Smaller What happens if you declare an upper bound of 10, but only put four elements in the array?
Remember, the 10 declares the position of the last element of your array. However, unlike trying to use more elements, there is no rule that states you have to use all of the positions. Only using four elements would not cause a problem of any sort. But there is a potential problem in the background.
As a beginner, this is not a huge concern, but a major issue in software design is resource control. A computer has a finite memory capacity. When you declare that your array will have 10 elements, VBA puts a reserve on memory. This means that the rest of the memory is sitting there unused. This is a tremendous waste of resources.
Your first impulse may be to use ReDim to change the size declaration. This causes, however, another rather serious problem. When we used ReDim in the earlier example, the array still had no elements. If you use ReDim with a populated array, it will wipe it out and start anew. Odds are that is not the most desirable solution.
VBA helps us out by combining ReDim with a second keyword as follows:
ReDim Preserve intMyScores(4)
The Preserve keyword reallocates the memory and retains the elements intact.
Erasing Arrays You will sometimes have situations in which you want to keep the array declared, but erase the elements within it. You can easily do that with the keyword Erase, as shown here:
Erase intMyScores
This clears the contents but keeps the declaration.
Depending on the type of array, different things may happen. If it is a numeric array, the elements are set to 0. However, the elements of a string array are set to “ ”. This is an empty string. If it is a Boolean array, each element is set to False.
IsArray How do you know if a variable is an array? VBA provides a handy little function to test a variable. Let’s take a look at the following code.
Sub arrayTest()
Dim intScores1 As Integer
Dim intScores2(4) As Integer
Debug.Print "Is intScores1 an array: " & IsArray(intScores1)
Debug.Print "Is intScores2 an array: " & IsArray(intScores2)
End Sub
This code returns the results shown here:
You can see that IsArray is a Boolean function. It returns a value of either True or False. In the first case above, the result is false because we did not declare it as an array. In the second case, it is true because we did.
Summary
We have covered a lot of territory here. We looked at variables, as well as the various types of structures used in VBA. Yet, even with all of this, our study of the basic VBA structures is far from complete. We will be adding to our discussion as we progress through this book.
In the remaining chapters of this section, we will study the VBA Editor and these structures in even greater detail.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter six of the book Access VBA Programming, written by Charles E. Brown and Ron Petusha (McGraw-Hill/Osborne, 2004; ISBN: 0072231971). Check it out at your favorite bookstore today. Buy this book now.
|
|
