VBA brush up 07: Working with Arrays

Technorati label: VBA, array

(1) If you want to store values of different data types in the same array, you must declare the array as variant.

 

1. Dim Exchange (5, 3) as Variant

(2) If you 'd rather start counting your array's elements at 1, you can explain icitly specify a lower bound of the array by usingOption base 1Statement. This instruction must be placed in the Declaration section at the top of the VBA module, before any sub statements.

(3) The spread of The subscripts specified by the dim statement is called the range of the array. For example:

1. Dim mktgcodes (5 to 15)

(4) AvailableFor eachStatement access Array

 

1. Dim cities (6) as string
2. Dim City as Variant
4. For each city in cities
5. Msgbox City
6. Next

(5) When an array is declared in a procedure, it is local to this procedure and unknown to other procedures. however, you can pass the local array to another procedure by using the array's name followed by an empty set of parentheses as an argument in the calling statement. for example, the statement Hello cities () CILS the procedure named hello and passes to it the array cities ().

 

1. Sub Hello (cities () as string)
2. Dim counter as integer
3. For counter = 1 to 6
4. Msgbox "hello," & Cities (Counter )&"! "
5. Next
6. End sub
8. Hello cities ()

(7) The statement dim myarray () as integer declares a dynamic array called myarray. although this statement declares the array, it does not allocate any memory to the array. before you use a dynamic array in your procedure, you must useRedimStatementDynamically set the lower and upper boundsOf the array. The first redim statement specifies the initial size of myarray and reserves for it 10 bytes of memory to hold its five elements.
Normally, when you change the size of the array, you lose all the values that were in that array. the redim statement alone reinitializes the array. however, you can append new elements to an existing array by following the redim statement with the preserve keyword. in other words, the preserve keyword guarantees that the redimensioned array will not lose its existing data.

1. Dim myarray () as integer 'declare a dynamic array
2. Redim myarray (5) 'note that it specifies the upper bound, not the number of elements
3. 'Change the size of myarray to hold 10 elements
4. Redim preserve myarray (10)

(8) You can manipulate arrays with five built-in VBA functions: array, isarray, erase, lbound, and ubound.

1. Dim auto as Variant
   Auto = array ("Ford", "black", "1999 ")
2. Using the isarray function you can test whether a variable is an array. The isarray function returns true if the variable is an array or false if it's not an array.
3. 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.
   Erase cities
4. Msgbox "the upper bound (first dimension) is" & ubound (ex, 1 )&"."
   Msgbox "the lower bound (second dimension) is" & lbound (ex, 2 )&"."

(9) usually, you cannot call a procedure with more arguments than the procedure declaration specifies. When you need an indefinite number of arguments, you can declareParameter Array, Which allows a procedure to accept an array of values for an argument. you do not have to know the number of elements in the parameter array when you define the procedure. the array size is determined individually by each call to the procedure.
You useParamarrayKeyword to denote a parameter array. The following rules apply:

• A procedure can have only one parameter array, and it must be the last argument in the procedure definition.
• The parameter array must be passed by value. It is good programming practice to explicitly includeByvalKeyword in the procedure definition.
• The code within the procedure must treat the parameter array as a one-dimen1_array, each element of which is the same data type asParamarrayData type.
• The parameter array is automatically optional. Its default value is an empty one-dimen1_array of the parameter array's element type.
• All arguments preceding the parameter array must be required. The parameter array must be the only optional argument.

When you call a procedure with a parameter array argument, you can pass any of the following for the parameter array:

• Nothing-that is, you can omitParamarrayArgument. In this case, an empty array is passed to the procedure. You can also passNothingKeyword, with the same effect.
• A list of an indefinite number of arguments, separated by commas. The data type of each argument must be implicitly convertible toParamarrayElement type.
• An array with the same element type as the parameter array.

The following example shows how you can define a procedure with a parameter array:

1. Sub studentscores (byval name as string, byval paramarray scores () as string)
2. Dim I as integer
3. Debug. writeline ("scores for" & name &":")
4. 'Use ubound function to determine largest subscript of array.
5. For I = 0 to ubound (scores)
6. Debug. writeline ("score" & I & ":" & scores (I ))
7. Next I
8. End sub

The following examples show typical calltoStudentScores:

1. Studentscores ("Anne", "10", "26", "32", "15", "22", "24", "16 ")
2. Studentscores ("Mary", "high", "low", "average", "high ")
3. Dim johnscores () as string = {"35", "absent", "21", "30 "}
4. Studentscores ("John", johnscores)

(10) I wrote an example of using a custom-type dynamic array to call functions and functions for passing value parameters:

A) custom type declaration:

1. Private type treenode_type
2. Trex as mscomctllib. Treeview
3. Objnode as mscomctllib. Node
4. End type

B) function: the function called by the main program.

1. Private function getrelatedtreenode (byval blnincludeme as Boolean, udttreenode () as treenode_type) as Boolean
2. If isfdnode (m_tree.selecteditem) then' if it is a multi-hanging node, find the linkage tree
3. Getrelatedtreenode = getbosstreenode (blnincludeme, udttreenode ())
4. Else
5. Getrelatedtreenode = getsametreenode (blnincludeme, udttreenode ())
6. End if
7. End Function

C) functions called by the above functions: demonstrate how to redim and assign values to dynamic array parameters.

1. Private function getsametreenode (byval blnincludeme as Boolean, udttreenode () as treenode_type) as Boolean
2. Dim I as integer
3. Dim intnum as integer
5. Intnum = 0' indicates the number of the same tree currently found, and it is also the maximum index of the next loop of the array, because the array is 0-based ).
6. Redim udttreenode (intnum)
8. For I = 1 to m_trees.count
9. If m_strtreetable = m_trees.item (I). treetablename then
10. If blnincludeme then
11. Set udttreenode (intnum). trex = m_trees.item (I). Tree
12. Set udttreenode (intnum). objnode = m_trees.item (I). Tree. nodes. Item (m_tree.selecteditem.key)
13. Intnum = intnum + 1
14. Redim preserve udttreenode (intnum) 'in the for loop, the array size is more than the actual number of trees.
15. Else
16. If not (m_tree is m_trees.item (I). Tree) then
17. Set udttreenode (intnum). trex = m_trees.item (I). Tree
18. Set udttreenode (intnum). objnode = m_trees.item (I). Tree. nodes. Item (m_tree.selecteditem.key)
19. Intnum = intnum + 1
20. Redim preserve udttreenode (intnum)
21. End if
22. End if 'blnincludeme
23. End if 'tree table name is the same
24. Next I
26. If intnum = 0 then
27. Getsametreenode = false
28. Else
29. Redim preserve udttreenode (intnum-1) 'to make the array size more accurate
30. Getsametreenode = true
31. End if
33. End Function

D) calls to the above functions in the main program: demonstrate how to actively release the memory (maybe too careful: P)

1. Private sub changenodedetailfromtree (strdetailtable as string, lngdetailid as long)
2. Dim udtrelatedtreenode () as treenode_type
3. Dim I as integer
5. Change the tag of the corresponding node on the "linkage" tree, including
6. If getrelatedtreenode (true, udtrelatedtreenode () then
7. 'Change the tag of the corresponding node under the linkage tree
8. For I = 0 to ubound (udtrelatedtreenode)
9. Udtrelatedtreenode (I). objnode. Tag = makenodetag (strdetailtable, lngdetailid)
10. Set udtrelatedtreenode (I). objnode = nothing
11. Set udtrelatedtreenode (I). trex = nothing
12. Next I
13. Erase udtrelatedtreenode
14. End if
16. End sub

References

 

1. Julitta korol, "access.2003.programming. By. example. With. VBA. xml. And. asp", by wordware Publishing, Inc. 2005, p102-p118
2. MS-help: // Ms. msdnqtr.2006jan. 1033/vbcn7/html/vaconunderstandingparamarrays.htm