Excel VBA 学习总结

　　VBA的对象是有很多共性的，抛开它们所在层次的不同，它们的很多操作特征是一样的，比如它们都包括子对象集合，都包括某些激活操作，删除操作等等。像简单的Activate，Delete，Cut类似的操作我就不详细说了。下面我先总结一下这些共性中出镜率最多的类型：Collection，然后分析一下与之类似的两种常用类型，总结它们的不同。选择集合的时候，要充分考虑它们的特性和实际的问题，采用合适的集合解决问题。

1. 集合

定义集合的方法：Dim col as New Collection

• Add：往集合中添加一个元素，需要提供添加到集合中的元素，也可以提供一些其它可选的参数，比如键值、位置等。
• Remove：移除集合中的一个元素，需要提供该元素的Index值。
• Count功能：返回集合中元素的数目。
• Item功能：获取集合中的一个元素，由于Item是集合的默认属性，所以可以直接拿集合对象获取元素。参数：提供Index值(基本都是从1开始)，Name都是可以的(使用Name时要注意，必须给元素的Name赋过值)。例如：Workbooks(1)，Worksheets(“Name”)，Workbooks.Item(1)是等价的。

小例子如下： 

Sub TestCollection()
   Dim col As New Collection
   With col
     .Add (0)
     .Add (1)
     .Add (2)
   End With
   
   col.Remove (1)
   MsgBox col.Count
   
   Dim i As Variant
   For Each i In col
     MsgBox i
   Next
   
   Dim j As Integer
   For j = 1 To col.Count
    MsgBox col.Item(j)
   Next
End Sub

VBA的很多对象中都包括集合，但是这些集合中的对应方法已经做出了修改，使用时要注意参数和返回值。例如Workbooks集合Add方法就返回新添加的Workbook；Worksheets集合Add方法返回新添加的Worksheet。基本上内置对象的集合属性(Application.Workbooks, Workbook.Worksheets等等)，Add方法都是用于添加一个新的成员并返回这个成员，你可以使用这个引用继续完成相关的操作。

集合的索引值都是从1开始的。

集合中的元素并不要求类型一样，但是为了方便处理，通常要求是一样的。

可以用With减少代码输入。

可以用For Each遍历集合，但是VBA要求元素必须定义成Variant、Object或者自定义的Class类型；而使用For和Item枚举也很方便。

2. 数组

看完了集合，我们再看看数组与集合的对比。数组其实也应该算是一个'集合'，那么它与集合有什么不同呢？

先来看看数组的特性：

• 数组是一个数据表，数组的元素类型通常都是一致的(别忘了Variant，Object)，在定义时声明。
• 数组的下界默认为0，可以通过在模块开头加上“Option Base 1”强行改为1；当然也可以在定义时指定。
• 数组可以通过ReDim和Preserve改变大小。
• 数组可以使用Erase重新初始化固定大小数组中的元素(如清0，全部设为空串''，Variant类型全部设为Empty)，或者释放动态数组的空间。
• 数组支持多维的。
• 数组是快速，高效的数据'集合'(内存中顺序存贮的，所以每个元素访问都很快)。
• 数组通常用于存储单元格的值，处理完毕后再返回给单元格。

从上面其实已经看到了它与集合最主要的不同了：元素类型，长度，下界，维度，效率。 

既然说到数组，那我也总结一下数组常用的方式和函数：

赋值的方式

对于直接定义成数组的变量，循环赋值就可以了，但是除了这种方式，还有其它的方式动态生成数组，那就是可以把变量定义成Variant类型的，然后用这几种方式动态生成数组:arr = Array(1,2,3)，arr = [A1:A10]，或者arr = Split('a,b,c',',')。

Split函数生成的数组下标始终从0开始，即使是使用了Option Base强制从1开始了也如此。

Array函数生成的数组的下标是从Option Base定义的下标开始。

直接使用单元格赋值这种方式生成的数组的下标是从1开始的，即使是使用了Option Base强制从0开始了也如此。

数组生成字符串

使用Join函数就可以了:s = Join(Array('a','b','c'),',')；如果数组类型不是String，则需要先转换一下。

获取上下界

使用UBound(arr), LBound(arr)获得上下界。

筛选数据

使用Filter(arr,match,include,compare)方法获得符合条件的数据，后面两个参数并不是必须的。下面的小例子返回“不”包含“A“字符串的结果“D E”：

Sub testArr()
Dim a1 As Variant
Dim i As Integer

a1 = Array('A', 'BA', 'CA', 'D', 'E')
a1 = Filter(a1, 'A', False)
MsgBox Join(a1)
End Sub

Filter过滤是模糊过滤，注意这个特点。如果需要精确过滤，需要自己写方法解决。

排序

可以利用内置的Large，Small方法实现数值类型的排序。非数值类型，可以使用Worksheet中的Rank辅助排序，也可以自己写冒泡，快速，选择，希尔，桶排序等算法。 

Sub testArr()
    Dim a(1 To 5) As Integer
    Dim b(1 To 5) As String
    Dim i As Integer
    a(1) = 111
    a(2) = 121
    a(3) = 234
    a(4) = 11
    a(5) = 9

    For i = 1 To 5
     b(i) = Application.WorksheetFunction.Large(a, i)
    Next

    MsgBox Join(b, ',')
End Sub

经典排序的实现可参看：http://www./cdb/viewthread.php?tid=13706&fpage=1

取得最大值最小值

 使用WorksheetFunction内置的最大，最小值函数即可。

MsgBox Application.WorksheetFunction.Max(arr)

数组置换

使用内置的WorksheetFunction.Transpose函数。

Public Sub TestTranspose()
Dim d(1 To 3) As String
d(1) = 'a'
d(2) = 'b'
d(3) = 'c'

[a1].Resize(1, UBound(d)) = d
[d1].Resize(UBound(d), 1) = WorksheetFunction.Transpose(d)

End Sub

Transpose方法用于将一组单元格的行列置换，输入的参数要求是数组格式的。

Transpose方法可以用于将数组的数组(数组的元素是数组，要求元素数组长度一致)转换成二维数组。

Transpose方法返回的类型为Variant，返回的数组的下界始终为1，即使是使用了Option Base强制定义为其它值也如此。

3. 字典

在很多Collection的不方便处理的情况下，字典常常作为扩展手段来使用。字典的含义就不啰嗦了，下面总结一下字典。

字典的创建方式：

方式一：“后期绑定”，定义一个Object变量，直接赋值 - Set d = CreateObject('Scripting.Dictionary')。

方式二：“前期绑定”，加入c:\windows\system32\scrrun.dll 引用，然后明确创建类型 - Dim d as New Dictionary

字典的四个属性:

• CompareMode - 决定key的比较方法，BinaryCompare(默认)是采用二进制方式比较：即a,A是不同字符；TextCompare采用文本方式比较：即a,A是相同字符。其它的比较方式可以参考相关的文档。
• Count - 计算字典中的条目数量，返回Long值。
• Key - 键值，通常是整数或字符串；可以是除数组外的任何类型；Key具有唯一性。
• Item - 可以是任何对象(不含自定义数据)。

字典的六个方法:

•  Add - 向字典添加内容(添加的内容是对象时注意不要忘记使用Set)。
•  Exists - 判断keys中有没有要找的key，返回 true 或 false。
•  Keys - 返回字典的Key数组，数组下标始终从0开始，无视Option Base强制定义的值。
•  Items - 返回字典的Item数组，数组下标始终从0开始，无视Option Base强制定义的值。
•  Remove - 按照key从字典中删除一个项目。
•  RemoveAll - 清空字典。

简单的看一个小例子：

Public Sub TestDic()
    Dim d As Object
    Set d = CreateObject('Scripting.Dictionary')
    d('A') = 1
    d('B') = 2
    d('C') = 3
    'Change Key
    d.Key('A') = 'D'
    'Change Value
    d('D') = 5
    MsgBox d('D')
    MsgBox d.Count
End Sub

VBA中字典具有以下的优点：添加、删除元素的速度比Collection要快，避免了大量的循环与逻辑判断，可使用的方法也比较多、比较方便。字典的使用也基本上是围绕这些优点展开的，比如：利用Key的唯一性，快速去除重复的数据，通常配合Transpose方法可以完成许多这种任务。

关于字典的知识，可以参看牛人的详细解释和常用示例：http://www./forum.php?mod=viewthread&tid=145901&highlight=%D7%D6%B5%E4。

快速引用单元格的值使用'[]'，例如：arr = [A1]。