VBA Brush Up 07：Working with Arrays-CSDN博客

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.

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:

Dim mktgCodes(5 To 15)

（4）可以用for each语句访问数组

 
 Dim cities(6) As String 
Dim city As Variant 
For Each city In cities 
    MsgBox city 
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().

 
 Sub Hello(cities() As String) 
    Dim counter As Integer 
    For counter = 1 To 6 
        MsgBox "Hello, " & cities(counter) & "!" 
    Next 
End Sub 
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.

 
 Dim myArray() As Integer ' declare a dynamic array  
ReDim myArray(5) ' 注意是规定上界，而不是元素个数  
' change the size of myArray to hold 10 elements  
ReDim Preserve myArray(10)
 

（8）You can manipulate arrays with five built-in VBA functions: Array, IsArray, Erase, LBound, and UBound.

Dim auto As Variant
auto = Array("Ford", "Black", "1999")
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.
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
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:

 
 Sub StudentScores(ByVal Name As String, ByVal ParamArray Scores() As String) 
    Dim I As Integer 
    Debug.WriteLine("Scores for " & Name & ":") 
    ' Use UBound function to determine largest subscript of array.  
    For I = 0 To UBound(Scores) 
        Debug.WriteLine("Score " & I & ": " & Scores(I)) 
    Next I 
End Sub 
 

The following examples show typical calls to StudentScores:

 
 StudentScores("Anne", "10", "26", "32", "15", "22", "24", "16") 
StudentScores("Mary", "High", "Low", "Average", "High") 
Dim JohnScores() As String = {"35", "Absent", "21", "30"} 
StudentScores("John", JohnScores) 
 

（10）我写的用自定义类型动态数组做传值参数的函数和函数调用的例子：

a)自定义类型声明：

 
 Private Type TREENODE_TYPE 
    treX As MSComctlLib.TreeView 
    objNode As MSComctlLib.Node 
End Type 
 

b)函数：供主程序调用的函数

 
 Private Function GetRelatedTreeNode(ByVal blnIncludeMe As Boolean, udtTreeNode() As TREENODE_TYPE) As Boolean 
    If IsFdNode(m_tree.SelectedItem) Then '如果是多挂节点，则找联动树  
        GetRelatedTreeNode = GetBossTreeNode(blnIncludeMe, udtTreeNode()) 
    Else 
        GetRelatedTreeNode = GetSameTreeNode(blnIncludeMe, udtTreeNode()) 
    End If 
End Function 
 

c)被上述函数调用的函数：具体演示了如何对传来的动态数组参数redim和赋值

 
 Private Function GetSameTreeNode(ByVal blnIncludeMe As Boolean, udtTreeNode() As TREENODE_TYPE) As Boolean 
    Dim i As Integer 
    Dim intNum As Integer 
    
    intNum = 0 '表示当前找到的同树的数目，同时也是数组下一轮循环的最大index，因为数组是0-based)。  
    ReDim udtTreeNode(intNum) 
    
    For i = 1 To m_trees.Count 
        If m_strTreeTable = m_trees.Item(i).TreeTableName Then 
            If blnIncludeMe Then 
                Set udtTreeNode(intNum).treX = m_trees.Item(i).tree 
                Set udtTreeNode(intNum).objNode = m_trees.Item(i).tree.Nodes.Item(m_tree.SelectedItem.Key) 
                intNum = intNum + 1 
                ReDim Preserve udtTreeNode(intNum) '在for循环之中，数组的大小比树的实际数目多一  
              Else 
                If Not (m_tree Is m_trees.Item(i).tree) Then 
                    Set udtTreeNode(intNum).treX = m_trees.Item(i).tree 
                    Set udtTreeNode(intNum).objNode = m_trees.Item(i).tree.Nodes.Item(m_tree.SelectedItem.Key) 
                    intNum = intNum + 1 
                    ReDim Preserve udtTreeNode(intNum) 
                End If 
            End If 'blnIncludeMe  
        End If '树表名相同  
    Next i 
    If intNum = 0 Then 
        GetSameTreeNode = False 
    Else 
        ReDim Preserve udtTreeNode(intNum - 1) '使数组的size变准确  
         GetSameTreeNode = True 
    End If 
End Function 
 

d)主程序中对上述函数的调用：具体演示了如何主动释放内存（也许太过小心了:P）

 
  Private Sub changeNodeDetailFromTree(strDetailTable As String, lngDetailId As Long) 
    Dim udtRelatedTreeNode() As TREENODE_TYPE 
    Dim i As Integer  
    '联动'树上更改相应节点的tag，包括自己  
    If GetRelatedTreeNode(True, udtRelatedTreeNode()) Then 
        '在联动树下改相应节点的tag  
        For i = 0 To UBound(udtRelatedTreeNode) 
            udtRelatedTreeNode(i).objNode.Tag = MakeNodeTag(strDetailTable, lngDetailId) 
            Set udtRelatedTreeNode(i).objNode = Nothing 
            Set udtRelatedTreeNode(i).treX = Nothing 
        Next i 
        Erase udtRelatedTreeNode 
    End If 
End Sub
 
 
 

参考文献

Julitta Korol，“Access.2003.Programming.by.Example.with.VBA.XML.and.ASP”，by Wordware Publishing, Inc. 2005, p102-p118
ms-help://MS.MSDNQTR.2006JAN.1033/vbcn7/html/vaconUnderstandingParamArrays.htm