VBA数组进阶--典型编译/运行时错误之成因及解决办法

liu-aguang

----------VBA数组典型编译/运行时错误之成因及解决办法----------
  
      如果错误理解声明、创建和使用数组的规则,将产生编译或运行时错误。本文探讨常见错误的产生原因及解决
办法。数组的各种限制或规则在不同Excel（VBA）版本中有一定差异，文中所及主要以2003(VBA 6.5)和2010(VBA
7.0)为测试对象.有些错误在2003版本以后得以修正，则不再列举了。
.
...........要顺利阅读本文，请先了解以下概念.............
  
数组数据类型与数组元素数据类型
    这两个概念是有差别的，一般来说数组代表一群具有相同数据类型的元素值，这种情况下两者数据类型是一致
的；但对数据类型为Variant的数组却并不一定如此.数据类型可以用TypeName或VarType函数返回。

数组的类型
    以维度来分: 有一维、二维、三维...数组.
    以数组数据类型分: 有字符型数组/固定字符长度型数组/Variant数组等等（不是指元素的数据类型）.
    以创建途径不同分: 标量数组（本文探讨的类型）/控件数组/参数数组/内存数组等.
    以数组大小是否固定分: 动态数组/静态数组
    以数组有效范围分：全局（公用）数组、模块级数组、局部数组

.....................本文讨论的主题..........................

※声明全局数组错误         ※For Each语句循环读取数组元素值错误
※参数数组声明错误         ※数组数据传入工作表错误
※改变数组的大小错误      ※转置数组错误
※改变数组数据类型错误   ※工作表函数应用于日期类型数组错误
※数组赋值错误                ※过程中传递整个数组错误

liu-aguang

一、试图声明全局(公用）数组出错
症状：
    在一个工作表对象模块中声明全局数组。

1. Public arr()   '在一个工作表对象模块中声明全局数组出错．
   
2. Sub test()
   
3.    ReDim arr(9)
   
4.    For i = 0 To 9
   
5.       arr(i) = i
   
6.    Next
   
7. End Sub

复制代码

原因：
    VBA可以通过Public或Private(Dim)在项目模块中声明全局数组或模块级数组,通过Private(Dim)在过程中声明
局部数组.但VBA不允许在工作表模块内创建全局数组．

办法1：
　  在项目标准模块的通用声明部分声明

1. Public MyArray(1) As String
   
2. '.............
   
3. Sub FiArray()
   
4.     MyArray(0) = "Hi": MyArray(1) = "Bye"
   
5.     DisplayArray
   
6. End Sub
   
7. Sub DisplayArray()
   
8.     For i = 0 To UBound(MyArray)
   
9.         MsgBox MyArray(i)
   
10.     Next
    
11. End Sub

复制代码

方法 2：
    使用参数数组功能
    可以使用参数数组来传递给过程的参数的数组。您不必定义过程时知道数组中的元素的数目。您可以使用参数
组关键字来表示一个参数数组。您必须将数组声明为 variant 类型的数组，它必须是过程定义中的最后一个参数。
Sub AnyNumberArgs(strName As String, ParamArray intScores() As Variant)
      Dim intI As Integer
      For intI = 0 To UBound(intScores())
         MsgBox strName & "'s Scores: " & intScores(intI)
      Next intI
   End Sub
Sub CallParamArrayRoutine
      AnyNumberArgs "Jamie", 10, 26, 32, 15, 22, 24, 16
      AnyNumberArgs "Kelly", "High", "Low", "Average", "High"
End Sub

liu-aguang

二、参数数组声明错误
症状1:
    将参数数组声明为一个非Variant数组出错:
   
    Sub Mysub(paramarray VarArg() as Integer) '声明为Integer类型出错
原因：   
    ParamArray数组的每个元素可以是不同的数据类型,然而数组本身必须声明为Variant的数组.
   
    Sub Mysub(ParamArray VarArg() As Variant)
症状2:
    在模块声明部分使用了Deftype关键字,而函数过程参数数组没有指定为任何数据类型而出错。

1. DefInt A-K
   
2. Function bTest(ParamArray aVarArg())  '这时数组aVarArg不再是默认的Variant类型
   
3.     bTest = aVarArg(0)
   
4. End Function
   
5. Sub Test()
   
6.     bTest 1, 2, 3
   
7. End Sub

复制代码

办法：
   将参数数组aVararg显示设置为Variant.
   
Function bTest(ParamArray aVarArg() As Variant)

liu-aguang

复制代码

三、改变数组的大小出错
症状：
    试图用Redim重新声明静态数组的大小。
错例1:

1. Sub test()
   
2.     Dim a(1) As Integer
   
3.     ReDim a(2) '错误发生在这里.
   
4. End Sub
   

复制代码

错例2：

1. Sub FirstOne()
   
2.     Dim FixedArr(25) As Integer
   
3.     NextOne FixedArr() '将之传给过程NextOne
   
4. End Sub
   
5. Sub NextOne(someArr() As Integer)
   
6.     ReDim someArr(35)    '发生错误10
   
7.     '....
   
8. End Sub

复制代码

原因：
    Redim可反复改变动态数组大小（元素和维数的数目），但是不能改变静态数组的大小。
  
办法1:
    将数组用Redim(如果是在过程内声明数组)声明为动态而非固定大小;或在声明时不指定元素数(如果是模块级
别中声明数组)

1. Sub test()
   
2.     Dim a() As Integer   '声明一个动态数组
   
3.     ReDim a(1 To 10, 1 To 2)  '初始化为一个二维数组
   
4.     ReDim a(9)                '修改为一维数组
   
5. End Sub

复制代码

办法2：
    Redim将原来数组中的数据全部清除，如果要保留原来数据，可用Redim Preserve语句.

1. Sub test()
   
2.     Dim a() As String
   
3.     ReDim a(1)
   
4.     a(0) = "A": a(1) = "B"
   
5.     ReDim Preserve a(2)
   
6.     a(2) = "C"
   
7.     MsgBox a(0) & a(1) & a(2)
   
8. End Sub

复制代码

讨论:
     Redim Preserve只能重新定义最末维的大小,并且不能改变维数数目.
     Redim Preserve可以保留调整数组大小时的数组内容，但其操作不快。为了调整数组大小，VBA要取一块内存用
于新数组，并在指定Perserve的情况下将原数组中的所有项目复制过去，最后还要释放原数组的内存。最好不用
Redim Preserve。替代办法有：1.使用集合。2.链接清单之类的动态数据结构。3.可以一次加进一块项目来调整数
组大小。即不是每次需要更多项目时都调整数组大小，而是一次加进一个较大项目数，只在超出时才调整数组大小
。项目加入之后，可以最后再调用Redim Preserve正确地调整数组长度。

错例3：
    试图改变动态数组大小出错
    对模块层次的动态数组,要重新确定大小,而某一个元素已经作为参数传给了过程.
Dim ModArray() As Integer '创建一个模块层次动态数组
Sub AError()
    ReDim ModArray(1 To 3) As Integer
    Test ModArray(1) '传递动态数组的元素给Test过程
End Sub
Sub Test(someint As Integer)
    ReDim ModArray(1 To 10) As Integer '错误发生
End Sub

liu-aguang

四、改变数组数据类型出错

症状：
    试图使用redim重新声明数组的数据类型
错例1：

1. Sub test()
   
2.     Dim abc()
   
3.     ReDim abc(2) As String
   
4. End Sub
   

复制代码

错例2：

1. Sub test1()
   
2.    Dim ar() As String
   
3.    ReDim ar(2) As Integer
   
4. End Sub
   

复制代码

原因:
    Redim不能用来改变数组数据类型。与标量变量不同，VBA没有提供直接改变数组数据类型的函数.
办法1:
    请声明一个所要数据类型的新数组,然后使用转换函数将原来数组中的各元素转换为新数组的数据类型,最后赋与新数组中对应的元素.
办法2:
    也可将此数组放在Variant变量中。这可以用一个简单的赋值式来完成：
    Dim Myvar As Variant
    Myvar = oldArray()
    这将创建一个包含与原来数组类型相同之数组的 Variant。然后可以将任何有效 VarType 的变量赋予 variant 中的数组元素。

liu-aguang

八、转置数组出错

症状：
    数组元素字长超过一定值导致转置出错

1. Sub test()
   
2.     Dim x, y
   
3.     ReDim x(1 To 2, 1 To 2)
   
4.     x(1, 1) = String(1000, "a")
   
5.     x(1, 2) = String(100, "b")
   
6.     x(2, 1) = String(50, "c")
   
7.     x(2, 2) = String(10, "d")
   
8.     y = Application.Transpose(x) '错误发生在这里
   
9. End Sub
   

复制代码

原因：
转置，数组元素字符串长度不能超过255个。

办法1：
    构建一个自定义的转置函数。

1. Sub Transpose1_Solve()
   
2.     Dim arr() As Variant
   
3.     Range("A1:B1") = Array("A", String(256, "B"))
   
4.     arr = Range("A1:B1")
   
5.     Range("A3:A4") = TransposeDim(arr)
   
6. End Sub
   
7. 
   
8. Function TransposeDim(vArrTrans As Variant) As Variant
   
9.     Dim X As Long, Y As Long
   
10.     Dim XUpper As Long, YUpper As Long
    
11.     Dim XLower As Long, YLower As Long
    
12.     Dim tempArray As Variant
    
13.     XUpper = UBound(vArrTrans, 2)
    
14.     XLower = LBound(vArrTrans, 2)
    
15.     YUpper = UBound(vArrTrans, 1)
    
16.     YLower = LBound(vArrTrans, 1)
    
17.     ReDim tempArray(XLower To XUpper, YLower To YUpper)
    
18.     For X = XLower To XUpper
    
19.         For Y = YLower To YUpper
    
20.             tempArray(X, Y) = vArrTrans(Y, X)
    
21.         Next Y
    
22.     Next X
    
23.     TransposeDim = tempArray
    
24. End Function

复制代码

办法2：
    在2010以上版本中，如果数组类型是字符串类型数组，则不受限制。

1. Sub test()
   
2.     Dim x, y
   
3.     ReDim x(1 To 2, 1 To 2) As String
   
4.     x(1, 1) = String(1000, "a")
   
5.     x(1, 2) = String(100, "b")
   
6.     x(2, 1) = String(50, "c")
   
7.     x(2, 2) = String(10, "d")
   
8.     y = Application.Transpose(x)
   
9. End Sub

复制代码

liu-aguang

五、数组赋值错误

症状1：
    试图一次性赋与所有元素相同值

1. Sub test()
   
2.     Dim VarDate() As Integer
   
3.     VarDate = 5
   
4. End Sub
   

复制代码

原因：
    数组中的各元素必须个别指定其值。
症状2：
    试图将整个数组指定给另一个数组

1. Sub test()
   
2.    Dim Arr1()
   
3.    Dim Arr2(9) As String
   
4.    Arr1 = Arr2   '错误发生在这里
   
5. End Sub

复制代码

原因：
    想要将一个数组赋值给另一个数组，必须确定接收数组是动态数组，并且类型匹配。
办法1:
   将整个数组传递给一个类型相同的动态数组

1. Sub test1()
   
2.   Dim Astr1() As String
   
3.   Dim Astr2(9) As String
   
4.   Astr1 = Astr2
   
5. End Sub
   
6. 
   
7. Sub test2()
   
8.   Dim arr()
   
9.   'arr = Range("a1:d3") '或
   
10.   arr = ActiveSheet.Range("a1:d3").Value
    
11. End Sub

复制代码

讨论：
    工作表的区域(Range)的Value(包括Formula/FormulaR1C1等)是以二维数组形式组织的。它是一个静态Variant数组，所以在Test2过程中可以直接传递给一个动态的Variant数组(arr())。
    注意的是在【ActiveSheet.Range("a1:d3").Value】中，Value属性是不能省略的。Range("a1:d3")与ActiveSheet.Range("a1:d3")表达方式内部组织不同，前者是一个Range对象,默认属性是Value，可以省略;而后者被组织为一个Variant变量包含Range对象，而Variant变量没有默认属性，必须显示表达Value。
办法2（推荐）：
    可以将整个数组放在一个Variant变量中，使一个Variant变量包含整个数组：

1. Sub test()
   
2.   Dim Astr1    '声明一个Variant变量
   
3.   Dim Astr2(9) As String
   
4.   Astr1 = Astr2
   
5. End Sub
   
6. 
   
7. Sub test()
   
8.   Dim arr      '声明一个Variant变量
   
9.   'arr = Range("a1:d3") '或
   
10.   arr = ActiveSheet.Range("a1:d3")
    
11. End Sub
    
12. 
    
13. Sub test()
    
14.   Dim arr     '声明一个Variant变量
    
15.   arr = Array("A", String(25, "B"))
    
16. End Sub

复制代码

liu-aguang

六、For Each语句循环读取数组元素值出错

错例1：

1. Sub test()
   
2.   Dim ar(1) As String * 10
   
3.   ar(0) = String(3, "a")
   
4.   ar(1) = String(10, "b")
   
5.   For Each k In ar '错误发生在这里
   
6.     Debug.Print k
   
7.   Next
   
8. End Sub
   

复制代码

1. Type Carr
   
2.     FirstName As String
   
3. End Type
   
4. 
   
5. Sub test()
   
6.   Dim ar(1) As Carr
   
7.   ar(0).FirstName = String(3, "a")
   
8.   ar(1).FirstName = String(10, "b")
   
9.   For Each k In ar '错误发生在这里
   
10.     Debug.Print k
    
11.   Next
    
12. End Sub

复制代码

原因：
自定义类型数组和固定长度字符串的数组不能用For Each。这两类数组的元素控制变量不是Variant。

办法：
用for i语句循环。

liu-aguang

七、数组数据传入工作表出错

症状：
    向2003工作表传入数组数据出错

1. Sub test()
   
2.     Dim x
   
3.     ReDim x(1 To 2, 1 To 2)
   
4.     x(1, 1) = String(2000, "a")
   
5.     x(1, 2) = String(100, "b")
   
6.     x(2, 1) = String(50, "c")
   
7.     x(2, 2) = String(10, "d")
   
8.     Range("a1:b2") = x  '错误在这里
   
9. End Sub
   

复制代码

原因:
    当满足以下条件之一，则可能发生此问题：
    在2003中，数组长度超过1823个字符长度（VBA6.5超过911）；
    在2007中，数组长度超过8203个字符长度；     
    在2010以上，无此限制。
办法：
    循环数组，逐个单元格填充。

liu-aguang

九、工作表函数应用于日期类型数组出错

症状：
    日期类型的一元数组求最值时，结果始终为0.

1. Sub Date_Limit()
   
2.     Dim arrDate(1 To 2) As Date
   
3.     arrDate(1) = "2008-08-08"
   
4.     arrDate(2) = Date
   
5.     Debug.Print Application.Max(arrDate)
   
6. End Sub
   

复制代码

原因及解决办法：
    日期类型数组应用于工作表函数Max/Min、Large、Small、Match等，必须首先把日期类型转换为Double。

1. Sub Date_Solve()
   
2.     Dim arrDate(1 To 2) As Date
   
3.     arrDate(1) = "2008-08-08"
   
4.     arrDate(2) = Date
   
5.     Debug.Print Format(Application.Max(arrDate(1), arrDate(2)), "yyyy-mm-dd")
   
6. End Sub
   
7. 
   
8. Sub Date_Solve2()
   
9.     Dim arrDate(1 To 2) As Date
   
10.     Dim arr(1 To 2) As Double
    
11.     arrDate(1) = "2008-08-08"
    
12.     arrDate(2) = Date
    
13.     arr(1) = arrDate(1)
    
14.     arr(2) = arrDate(2)
    
15.     Debug.Print Format(Application.Max(arr), "yyyy-mm-dd")
    
16. End Sub

复制代码