| Chapter 7 | STORING MULTIPLE VALUES IN EXCEL VBA PROGRAMS |
| A QUICK INTRODUCTION TO WORKING WITH ARRAYS |
In previous chapters, you worked with many VBA procedures that used variables to hold specific information about an object, property, or value. For each single value that you wanted your procedure to manipulate, you declared a variable. But what if you have a series of values? If you had to write a VBA procedure to deal with larger amounts of data, you would have to create enough variables to handle all of the data. Can you imagine the nightmare of storing in your program currency exchange rates for all the countries in the world? To create a table to hold the necessary data, you’d need at least three variables for each country: country name, currency name, and exchange rate. Fortunately, Visual Basic has a way to get around this problem. By clustering the related variables together, your VBA procedures can manage a large amount of data with ease. In this chapter, you’ll learn how to manipulate lists and tables of data with arrays.
An array is a special type of variable that represents a group of similar values that are of the same data type (String, Integer, Currency, Date, etc.). The two most common types of arrays are one-dimensional arrays (lists) and two-dimensional arrays (tables). A one-dimensional array is sometimes referred to as a list. A shopping list, a list of the days of the week, and an employee list are examples of one-dimensional arrays or, simply, numbered lists. Each element in the list has an index value that allows accessing that element. For example, in the following illustration we have a one-dimensional array of six elements indexed from 0 to 5:
You can access the third element of this array by specifying index (2). By default, the first element of an array is indexed zero. You can change this behavior by using the Option Base 1 statement or by explicitly coding the lower bound of your array as explained further in this chapter.
All elements of the array must be of the same data type. In other words, one array cannot store both strings and integers. Following are two examples of one-dimensional arrays: a one-dimensional array called cities that is populated with text (String data type—$) and a one-dimensional array called lotto that contains six lottery numbers stored as integers (Integer data type—%).
A one-dimensional array: cities$ |
|
cities(0) |
Baltimore |
cities(1) |
Atlanta |
cities(2) |
Boston |
cities(3) |
Washington |
cities(4) |
New York |
cities(5) |
Trenton |
A one-dimensional array: lotto% |
|
lotto(0) |
25 |
lotto(1) |
4 |
lotto(2) |
31 |
lotto(3) |
22 |
lotto(4) |
11 |
lotto(5) |
5 |
As you can see, the contents assigned to each array element match the Array type. If you want to store values of different data types in the same array, you must declare the array as Variant. You will learn how to declare arrays in the next section.
A two-dimensional array may be thought of as a table or matrix. The position of each element in a table is determined by its row and column numbers. For example, an array that holds the yearly sales for each product your company sells has two dimensions (the product name and the year). The following is a diagram of an empty two-dimensional array.
You can access the first element in the second row of this two-dimensional array by specifying indexes (1, 0). Following are two examples of a two-dimensional array: an array named yearlyProductSales@ that stores yearly product sales using the Currency data type (@) and an array named exchange (of Variant data type) that stores the name of the country, its currency, and the U.S. dollar exchange rate.
A two-dimensional array: yearlyProductSales@ |
|
Walking Cane (0,0) |
$25,023 (0,1) |
Pill Crusher (1,0) |
$64,085 (1,1) |
Electric Wheelchair (2,0) |
$345,016 (2,1) |
Folding Walker (3,0) |
$85,244 (3,1) |
A two-dimensional array: exchange |
||
Japan (0,0) |
Japanese Yen (0,1) |
108.83 (0,2) |
Australia (1,0) |
Australian Dollar (1,1) |
1.28601 (1,2) |
Canada (2,0) |
Canadian Dollar (2,1) |
1.235 (2,2) |
Norway (3,0) |
Norwegian Krone (3,1) |
6.4471 (3,2) |
Europe (4,0) |
Euro (4,1) |
0.816993 (4,2) |
In these examples, the yearlyProductSales@ array can hold a maximum of 8 elements (4 rows * 2 columns = 8) and the exchange array will allow a maximum of 15 elements (5 rows * 3 columns = 15).
Although VBA arrays can have up to 60 dimensions, most people find it difficult to picture dimensions beyond 3-D. A three-dimensional array is an array of two-dimensional arrays (tables) where each table has the same number of rows and columns. A three-dimensional array is identified by three indexes: table, row, and column. The first element of a three-dimensional array is indexed (0, 0, 0).
Because an array is a variable, you must declare it in a similar way that you declare other variables (by using the keywords Dim, Private, or Public). For fixed-length arrays, the array bounds are listed in parentheses following the variable name. If a variable-length, or dynamic, array is being declared, the variable name is followed by an empty pair of parentheses.
The last part of the array declaration is the definition of the data type that the array will hold. An array can hold any of the following data types: Integer, Long, Single, Double, Variant, Currency, String, Boolean, Byte, or Date. Let’s look at some examples:
Array Declaration (one-dimensional) |
Description |
|
Declares a 6-element array, indexed 0 to 5 |
|
Declares a 6-element array, indexed 1 to 6 |
|
Declares a 10-element array, indexed 2 to 11 |
|
Declares a 10-element array, indexed −3 to 6 (the lower bound of an array can be 0, 1, or negative) |
|
Declares a variable-length array whose bounds will be determined at runtime (see examples later in this chapter) |
Description |
|
|
Declares a two-dimensional array (five rows by three columns) |
|
Declares a two-dimensional array (four rows by two columns) |
|
Declares a two-dimensional array (three rows indexed 1 to 3 by seven columns indexed 1 to 7) |
Array Declaration (three-dimensional) |
Description |
|
Declares a three-dimensional array (the first dimension has three elements, the second dimension has six elements indexed 1 to 6, and the third dimension has five elements) |
When you declare an array, Visual Basic automatically reserves enough memory space. The amount of the memory allocated depends on the array’s size and data type. When you declare a one-dimensional array named lotto with six elements, Visual Basic sets aside 12 bytes—2 bytes for each element of the array (recall that the size of the Integer data type is 2 bytes, and hence 2 * 6 = 12). The larger the array, the more memory space is required to store the data. Because arrays can eat up a lot of memory and impact your computer’s performance, it’s recommended that you declare arrays with only as many elements as you think you’ll use.
|
What Is an Array Variable? |
An array is a group of variables that have a common name. While a typical variable can hold only one value, an array variable can store a large number of individual values. You refer to a specific value in the array by using the array name and an index number. |
|
Subscripted Variables |
The numbers inside the parentheses of the array variables are called subscripts, and each individual variable is called a subscripted variable or element. For example, cities(5) is the sixth subscripted variable (element) of the array cities(). |
By default VBA assigns zero (0) to the first element of the array. Therefore, number 1 represents the second element of the array, number 2 represents the third, and so on. With numeric indexing starting at 0, the one-dimensional array cities(5) contains six elements numbered from 0 to 5. If you’d rather start counting your array’s elements at 1, you can explicitly specify a lower bound of the array by using an Option Base 1 statement. This instruction must be placed in the declaration section at the top of a VBA module before any Sub statements. If you don’t specify Option Base 1 in a procedure that uses arrays, VBA assumes that the statement Option Base 0 is to be used and begins indexing your array’s elements at 0. If you’d rather not use the Option Base 1 statement and still have the array indexing start at a number other than 0, you must specify the bounds of an array when declaring the array variable. The bounds of an array are its lowest and highest indices. Let’s take a look at the following example:
Dim cities(3 To 6) As Integer
The foregoing statement declares a one-dimensional array with four elements. The numbers enclosed in parentheses after the array name specify the lower (3) and upper (6) bounds of the array. The first element of this array is indexed 3, the second 4, the third 5, and the fourth 6. Notice the keyword To between the lower and the upper indexes.
After you declare an array, you must assign values to its elements. This is often referred to as “initializing an array,” “filling an array,” or “populating an array.” The three methods you can use to load data into an array are discussed in this section.
Assume you want to store the names of your six favorite cities in a one-dimensional array named cities. After declaring the array with the Dim statement:
Dim cities(5) as String
or
Dim cities$(5)
you can assign values to the array variable like this:
cities(0) ="Baltimore"
cities(1) = "Atlanta"
cities(2) = "Boston"
cities(3) = "San Diego"
cities(4) = "New York"
cities(5) = "Denver"
VBA’s built-in function Array returns an array of Variants. Because Variant is the default data type, the As Variant clause is optional in the array variable declaration:
Dim cities() as Variant
or
Dim cities()
Notice that you don’t specify the number of elements between the parentheses.
Next, use the Array function as shown here to assign values to your cities array:
cities = Array("Baltimore", "Atlanta", "Boston", "San Diego", "New York", "Denver")
When using the Array function for array population, the lower bound of an array is 0 or 1 and the upper bound is 5 or 6, depending on the setting of Option Base (see the previous section titled “Array Upper and Lower Bounds”).
The easiest way to learn how to use loops to populate an array is by writing a procedure that fills an array with a specific number of integer values. Let’s look at the example procedure here:
Sub LoadArrayWithIntegers()
Dim myIntArray(1 To 10) As Integer
Dim i As Integer
'Initialize random number generator
Randomize
'Fill the array with 10 random numbers between 1 and 100
For i = 1 To 10
myIntArray(i) = Int((100 * Rnd) + 1)
Next
'Print array values to the Immediate window
Debug.Print myIntArray(i)
Next
End Sub
The foregoing procedure uses a For…Next loop to fill myIntArray with 10 random numbers between 1 and 100. The second loop is used to print out the values from the array. Notice that the procedure uses the Rnd function to generate a random number. This function returns a value less than 1 but greater than or equal to 0. You can try it out in the Immediate window by entering:
x=rnd
?x
Before calling the Rnd function, the LoadArrayWithIntegers procedure uses the Randomize statement to initialize the random-number generator. To become more familiar with the Randomize statement and Rnd function, be sure to follow up with the Excel online help.
Having learned the basics of array variables, let’s write a couple of VBA procedures to make arrays a part of your new skill set. The procedure in Hands-On 7.1 uses a one-dimensional array to programmatically display a list of six North American cities.
|
Please note files for the “Hands-On” project may be found on the companion CD-ROM. |
|
1. Open a new workbook and save it as C:\VBAPrimerExcel_ByExample\Chap07_ExcelPrimer.xlsm.
2. Switch to the Microsoft Visual Basic Editor window and rename the VBA project Arrays.
3. Insert a new module into the Arrays (Chap07_ExcelPrimer.xlsm) project, and rename this module StaticArrays.
4. In the StaticArrays module, enter the following FavoriteCities procedure:
' start indexing array elements at 1
Option Base 1
Sub FavoriteCities()
'now declare the array
Dim cities(6) As String
'assign the values to array elements
cities(1) = "Baltimore"
cities(2) = "Atlanta"
cities(3) = "Boston"
cities(4) = "San Diego"
cities(5) = "New York"
cities(6) = "Denver"
'display the list of cities
MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) & cities(3) & Chr(13) & cities(4) & Chr(13) & cities(5) & Chr(13) & cities(6)
End Sub
Before the FavoriteCities procedure begins, the default indexing for an array is changed. Notice that the position of the Option Base 1 statement is at the top of the module window before the Sub statement. This statement tells Visual Basic to assign the number 1 instead of the default 0 to the first element of the array. The array cities() of String data type is declared with six elements. Each element of the array is then assigned a value. The last statement uses the MsgBox function to display the list of cities. When you run this procedure in Step 5, the city names will appear on separate lines in the message box, as shown in Figure 7.7. You can change the order of the displayed data by switching the index values.
FIGURE 7.1. You can display the elements of a one-dimensional array with the MsgBox function.
5. Position the insertion point anywhere within the procedure code and press F5 to run the FavoriteCities procedure.
6. On your own, modify the FavoriteCities procedure so that it displays the names of the cities in the reverse order (from 6 to 1).
Now that you know how to programmatically produce a list (a one-dimensional array), it’s time to take a closer look at how you can work with tables of data. The following procedure creates a two-dimensional array that will hold the country name, currency name, and exchange rate for three countries.
|
1. In the StaticArrays module, enter the following procedure:
Sub Exchange()
Dim t As String
Dim r As String
Dim Ex(3, 3) As Variant
t = Chr(9) ' tab
r = Chr(13) ' Enter
Ex(1, 1) = "Japan"
Ex(1, 2) = "Yen"
Ex(1, 3) = 104.57
Ex(2, 1) = "Mexico"
Ex(2, 2) = "Peso"
Ex(2, 3) = 11.2085
Ex(3, 1) = "Canada"
Ex(3, 2) = "Dollar"
Ex(3, 3) = 1.2028
MsgBox "Country " & t & t & "Currency" & t & "per US$" & r & r & Ex(1, 1) & t & t & Ex(1, 2) & t & Ex(1, 3) & r & Ex(2, 1) & t & t & Ex(2, 2) & t & Ex(2, 3) & r & Ex(3, 1) & t & t & Ex(3, 2) & t & Ex(3, 3) & r & r & "* Sample Exchange Rates for Demonstration Only", , "Exchange"
End Sub
2. Run the Exchange procedure.
When you run the Exchange procedure, you will see a message box with the exchange information presented in three columns, as shown in Figure 7.7.
FIGURE 7.2. The text displayed in a message box can be custom formatted.
The arrays introduced thus far in this chapter were static. A static array is an array of a specific size. Use a static array when you know in advance how big the array should be. The size of the static array is specified in the array’s declaration statement. For example, the statement Dim Fruits(9) As String declares a static array called Fruits that is made up of 10 elements (assuming you have not changed the default indexing to 1). But what if you’re not sure how many elements your array will contain? If your procedure depends on user input, the number of user-supplied elements might vary every time the procedure is executed. How can you ensure that the array you declare is not wasting memory? After you declare an array, VBA sets aside enough memory to accommodate the array. If you declare an array to hold more elements than what you need, you’ll end up wasting valuable computer resources. The solution to this problem is making your arrays dynamic.
A dynamic array is an array whose size can change. You use a dynamic array when the array size is determined each time the procedure is run. A dynamic array is declared by placing empty parentheses after the array name:
Before you use a dynamic array in your procedure, you must use the ReDim statement to dynamically set the lower and upper bounds of the array. For example, initially you may want to hold five fruits in the array:
Redim Fruits(1 To 5)
The ReDim statement redimensions arrays as the code of your procedure executes and informs Visual Basic about the new size of the array. This statement can be used several times in the same procedure.
The example procedure in Hands-On 7.3 will dynamically load data entered in a worksheet into a one-dimensional array.
|
1. Insert a new module into the Arrays project and rename it DynamicArrays.
2. In the DynamicArrays module, enter the following procedure:
Sub LoadArrayFromWorksheet()
Dim myDataRng As Range
Dim myArray() As Variant
Dim cnt As Integer
Dim i As Integer
Dim cell As Variant
Dim r As Integer
Dim last As Integer
Set myDataRng = ActiveSheet.UsedRange
'get the count of nonempty cells (text and numbers only)
last = myDataRng.SpecialCells(xlCellTypeConstants, 3).Count
If IsEmpty(myDataRng) Then
MsgBox "Sheet is empty."
Exit Sub
End If
ReDim myArray(1 To last)
i = 1
'fill the array from worksheet data
'reformat all numeric values
If cell.Value <> "" Then
If IsNumeric(cell.Value) Then
myArray(i) = Format(cell.Value, "$#,#00.00")
Else
myArray(i) = cell.Value
End If
i = i + 1
End If
Next
'print array values to the Immediate window
For i = 1 To last
Debug.Print myArray(i)
Next
Debug.Print "Items in the array: " & UBound(myArray)
End Sub
3. Switch to the Microsoft Excel application window of the Chap07_ExcelPrimer.xlsm workbook and enter some data in Sheet2. For example, enter your favorite fruits in cells A1:B6 and numbers in cells D1:D9.
4. Choose Developer | Macros. In the Macro dialog box, choose LoadArrayFromWorksheet, and click Run.
When the procedure completes, check the data in the Immediate window. You should see the entries you typed in the worksheet. The numeric data should appear formatted with the currency format.
You can manipulate arrays with five built-in VBA functions: Array, IsArray, Erase, LBound, and UBound. The following sections demonstrate the use of each of these functions in VBA procedures.
The Array function allows you to create an array during code execution without having to dimension it first. This function always returns an array of Variants. Using the Array function, you can quickly place a series of values in a list.
The CarInfo procedure shown here creates a fixed-size, one-dimensional, three-element array called auto.
|
1. Insert a new module into the current project and rename it Array_Function.
2. Enter the following CarInfo procedure:
Option Base 1
Sub CarInfo()
Dim auto As Variant
auto = Array("Ford", "Black", "1999")
MsgBox auto(2) & " " & auto(1) & ", " & auto(3)
auto(2) = "4-door"
MsgBox auto(2) & " " & auto(1) & ", " & auto(3)
End Sub
3. Run the CarInfo procedure.
Using the IsArray function, you can test whether a variable is an array. The IsArray function returns either true, if the variable is an array, or false, if it’s not an array. Here’s an example.
|
1. Insert a new module into the current project and rename it IsArray_Function.
2. Enter the code of the IsThisArray procedure, as shown here:
Sub IsThisArray()
' declare a dynamic array
Dim sheetNames() As String
Dim totalSheets As Integer
Dim counter As Integer
' count the sheets in the current workbook
totalSheets = ActiveWorkbook.Sheets.Count
' specify the size of the array
ReDim sheetNames(1 To totalSheets)
' enter and show the names of sheets
For counter = 1 To totalSheets
sheetNames(counter) = ActiveWorkbook.Sheets(counter).Name
Next counter
' check if this is indeed an array
If IsArray(sheetNames) Then
MsgBox "The sheetNames variable is an array."
End If
End Sub
3. Run the IsThisArray procedure.
When you want to remove the data from an array, you should use the Erase function. This function deletes all the data held by static or dynamic arrays. In addition, the Erase function reallocates all of the memory assigned to a dynamic array. If a procedure has to use the dynamic array again, you must use the ReDim statement to specify the size of the array.
The following example shows how to erase the data from the array cities.
|
1. Insert a new module into the current project and rename it Erase_Function.
2. Enter the code of the FunCities procedure shown here:
' start indexing array elements at 1
Option Base 1
Sub FunCities()
' declare the array
Dim cities(1 To 5) As String
' assign the values to array elements
cities(1) = "Las Vegas"
cities(2) = "Orlando"
cities(3) = "Atlantic City"
cities(4) = "New York"
cities(5) = "San Francisco"
' display the list of cities
MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) & cities(3)
& Chr(13) & cities(4) & Chr(13) & cities (5)
Erase cities
MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) & cities(3) & Chr(13) & cities(4) & Chr(13) & cities (5)
End Sub
After the Erase function deletes the values from the array, the MsgBox function displays an empty message box.
3. Run the FunCities procedure.
The LBound and UBound functions return whole numbers that indicate the lower bound and upper bound of an array.
|
1. Insert a new module into the current project and rename it L_and_UBound_Function.
2. Enter the code of the FunCities2 procedure shown here:
Sub FunCities2()
' declare the array
Dim cities(1 To 5) As String
' assign the values to array elements
cities(1) = "Las Vegas"
cities(2) = "Orlando"
cities(3) = "Atlantic City"
cities(4) = "New York"
cities(5) = "San Francisco"
' display the list of cities
MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) & cities(3) & Chr(13) & cities(4) & Chr(13) & cities (5)
' display the array bounds
MsgBox "The lower bound: " & LBound(cities) & Chr(13) & "The upper bound: " & UBound(cities)
End Sub
3. Run the FunCities2 procedure.
When working with arrays, it’s easy to make a mistake. If you try to assign more values than there are elements in the declared array, VBA will display the error message “Subscript out of range,” as shown in Figure 7.7.
FIGURE 7.3. This error was caused by an attempt to access a nonexistent array element.
Suppose you declare a one-dimensional array that consists of six elements and you are trying to assign a value to the seventh element. When you run the procedure, Visual Basic can’t find the seventh element, so it displays the error message. When you click the Debug button, Visual Basic will highlight the line of code that caused the error.
To fix this type of error, you should begin by looking at the array’s declaration statement. Once you know how many elements the array should hold, it’s easy to figure out that the culprit is the index number that appears in the parentheses in the highlighted line of code. In the example shown in Figure 7.7, once we replace the line of code cities(7) = "Denver" with cities(6) = "Trenton" and press F5 to resume the procedure, the procedure will run as intended.
FIGURE 7.4. When you click the Debug button in the error message, Visual Basic highlights the statement that triggered the error.
Another frequent error you may encounter while working with arrays is Type mismatch. To avoid this error, keep in mind that each element of an array must be of the same data type. If you attempt to assign to an element of an array a value that conflicts with the data type of the array declared in the Dim statement, you’ll obtain the Type mismatch error during code execution. To hold values of different data types in an array, declare the array as Variant.
Values can be passed between subroutines or functions as required or optional arguments. If the passed argument is not absolutely required for the procedure to execute, the argument’s name is preceded by the keyword Optional. Sometimes, however, you don’t know in advance how many arguments you want to pass. A classic example is addition. You may want to add together two numbers. Later, you may use 3, 10, or 15 numbers.
Using the keyword ParamArray, you can pass an array consisting of any number of elements to your subroutines and function procedures.
The following AddMultipleArgs function will add up as many numbers as you require. This function begins with the declaration of an array, myNumbers. Notice the use of the ParamArray keyword. The array must be declared as an array of type Variant, and it must be the last argument in the procedure definition.
|
Hands-On 7.8. Passing an Array to Procedures Using the |
1. Insert a new module into the current project and rename it ParameterArrays.
2. In the ParameterArrays module, enter the following AddMultipleArgs function procedure:
Function AddMultipleArgs(ParamArray myNumbers() As Variant)
Dim mySum As Single
Dim myValue As Variant
For Each myValue in myNumbers
mySum=mySum+myValue
Next
AddMultipleArgs = mySum
End Function
3. To try out the AddMultipleArgs function, activate the Immediate window and type the following instruction:
?AddMultipleArgs(1, 23.24, 3, 24, 8, 34)
When you press Enter, Visual Basic returns the total of all the numbers in the parentheses: 93.24. You can supply an unlimited number of arguments. To add more values, enter additional values inside the parentheses and press Enter. Notice that each function argument must be separated by a comma.
Earlier in this chapter you learned how to use various Array functions. The following procedure demonstrates how the simple Array function can speed up data entry.
|
Hands-On 7.9. Using the Array Function to Enter Headings in a Worksheet |
1. Insert a new module into the current project and rename it DataEntry_withArray.
2. In the EnterData_Array module, enter the following ColumnHeads procedure:
Sub ColumnHeads()
Dim heading As Variant
Dim cell As Range
Dim i As Integer
i = 0
heading = Array("First Name", "Last Name", "Position", "Salary")
Workbooks.Add
For Each cell In Range("A1:D1")
cell.Formula = heading(i)
i = i + 1
Next
Columns("A:D").Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub
3. Switch to Microsoft Excel window and run the ColumnHeads procedure.
We all find it easier to work with sorted data. Some operations on arrays, like finding maximum and minimum values, require that the array is sorted. Once it is sorted, you can find the maximum value by assigning the upper bound index to the sorted array, as in the following:
y = myIntArray(UBound(myIntArray))
The minimum value can be obtained by reading the first value of the sorted array:
x = myIntArray(1)
So, how can you sort an array? This section demonstrates how you can use Excel to get your array data into the sorted order. An easy way to sort an array is copying your array values to a new worksheet, and then using the Excel built-in Sort function. After completing the sort, you can load your sorted values back into a VBA array. This technique is the simplest since you can use a macro recorder to get your sort statement started for you. And, with a large array, it is also faster than the classic bubble sort routine that is commonly used with arrays.
|
1. Insert a new module into the current project and rename it SortArray_with Excel.
2. In the SortArray_withExcel module, enter the following SortArrayWithExcel procedure:
Sub SortArrayWithExcel()
Dim myIntArray() As Integer
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim r As Integer
Dim myDataRng As Range
'initialize random number generator
Randomize
ReDim myIntArray(1 To 10)
' Fill the array with 10 random numbers between 1 and 100
For i = 1 To 10
myIntArray(i) = Int((100 * Rnd) + 1)
Debug.Print "aValue" & i & ":" & vbTab & myIntArray(i)
Next
'write array to a worksheet
Worksheets.Add
r = 1 'row counter
With ActiveSheet
For i = 1 To 10
Cells(r, 1).Value = myIntArray(i)
r = r + 1
Next i
End With
'Use Excel Sort to order values in the worksheet
Set myDataRng = ActiveSheet.UsedRange
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange myDataRng
.Header = xlNo
.MatchCase = False
.Apply
End With
'free the memory used by array by using Erase statement
Erase myIntArray
ReDim myIntArray(1 To 10)
'load sorted values back into an array
For i = 1 To 10
myIntArray(i) = ActiveSheet.Cells(i, 1).Value
Next
'write out sorted array to the Immediate Window
i = 1
For i = 1 To 10
Debug.Print "aValueSorted: " & myIntArray(i)
Next
'find minimum and maximum values stored in the array
x = myIntArray(1)
y = myIntArray(UBound(myIntArray))
Debug.Print "Min value=" & x & vbTab; "Max value=" & y
End Sub
The SortArrayWithExcel procedure populates a dynamic array with 10 random Integer values and prints out this array to an Immediate window and a new worksheet. Next, the values entered in the worksheet are sorted in ascending order using the Excel Sort object. The sort statements have been generated by the macro recorder and then modified for this procedure’s needs. Once sorted, the Erase statement is used to free the memory used by the dynamic array. Before reloading the array with the sorted values, the procedure redeclares the array variable using the ReDim statement. The last statements in the procedure demonstrate how to retrieve the minimum and maximum values from the array variable.
3. Switch to Microsoft Excel window and run the SortArrayWithExcel procedure.
In this chapter, you learned how you can use arrays in complex VBA procedures that require a large number of variables. You worked with examples of procedures that demonstrated how to declare and use a one-dimensional array (list) and a two-dimensional array (table). You saw the difference between static and dynamic arrays, and practiced using five built-in VBA functions that are frequently used with arrays: Array, IsArray, Erase, LBound, and UBound. You also learned how to use a new keyword—ParamArray—and perform sorting of an array with Excel.
In the next chapter you will learn how to use collections instead of arrays to manipulate large amounts of data.
