VBA Brush Up 07：Working with Arrays

Technorati 標籤: 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 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 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）可以用for each語句訪問數組

 

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() calls 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 use the ReDim statement to dynamically set the lower and upper bounds of 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) ' 注意是規定上界，而不是元素個數 
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 declare a parameter 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 use the ParamArray keyword 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 include the ByVal keyword in the procedure definition.
• The code within the procedure must treat the parameter array as a one-dimensional array, each element of which is the same data type as the ParamArray data type.
• The parameter array is automatically optional. Its default value is an empty one-dimensional 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 omit the ParamArray argument. In this case, an empty array is passed to the procedure. You can also pass the Nothing keyword, 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 to the ParamArray element 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 calls to StudentScores:

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）我寫的用自訂類型動態數組做傳值參數的函數和函數調用的例子：

a)自訂型別宣告：

1. Private Type TREENODE_TYPE 
2.     treX As MSComctlLib.TreeView 
3.     objNode As MSComctlLib.Node 
4. End Type 

b)函數：供主程式調用的函數

1. Private Function GetRelatedTreeNode(ByVal blnIncludeMe As Boolean, udtTreeNode() As TREENODE_TYPE) As Boolean 
2.     If IsFdNode(m_tree.SelectedItem) Then '如果是多掛節點，則找聯動樹 
3.         GetRelatedTreeNode = GetBossTreeNode(blnIncludeMe, udtTreeNode()) 
4.     Else 
5.         GetRelatedTreeNode = GetSameTreeNode(blnIncludeMe, udtTreeNode()) 
6.     End If 
7. End Function 

c)被上述函數調用的函數：具體示範了如何對傳來的動態數組參數redim和賦值

1. Private Function GetSameTreeNode(ByVal blnIncludeMe As Boolean, udtTreeNode() As TREENODE_TYPE) As Boolean 
2.     Dim i As Integer 
3.     Dim intNum As Integer 
4.     
5.     intNum = 0 '表示當前找到的同樹的數目，同時也是數組下一輪迴圈的最大index，因為數組是0-based)。 
6.     ReDim udtTreeNode(intNum) 
7.     
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) '在for迴圈之中，數組的大小比樹的實際數目多一 
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 '樹表名相同 
24.     Next i 
26.     If intNum = 0 Then 
27.         GetSameTreeNode = False 
28.     Else 
29.         ReDim Preserve udtTreeNode(intNum - 1) '使數組的size變準確 
30.          GetSameTreeNode = True 
31.     End If 
33. End Function 

d)主程式中對上述函數的調用：具體示範了如何主動釋放記憶體（也許太過小心了:P）

1. Private Sub changeNodeDetailFromTree(strDetailTable As String, lngDetailId As Long) 
2.     Dim udtRelatedTreeNode() As TREENODE_TYPE 
3.     Dim i As Integer  
5.     '聯動'樹上更改相應節點的tag，包括自己 
6.     If GetRelatedTreeNode(True, udtRelatedTreeNode()) Then 
7.         '在聯動樹下改相應節點的tag 
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

參考文獻

 

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