VBA的一些使用心得

VBA的知识比较零散，因此开一贴记录一下使用VBA时的一些方法和心得。主要针对Excel，参考在这里

 

1. Collection Class

大部分情况下，Collection Class是比数组(Array)更好的一个选择，使用也十分简单：

Sub test()
    'Create
    Dim names As New Collection
    names.Add "Jack"
    names.Add "Rose"
    
    'Or
    Dim phones As Collection
    Set phones = New Collection
    
    phones.Add "123"
    phones.Add "345"
    
    'Access an Item
    MsgBox names.Item(1)
    MsgBox phones.Item(2)
    
    'Count
    MsgBox names.Count
    
    'Remove
    phones.Remove (2)
    
    'Loop collection
    For Each n In names
        MsgBox n
    Next
    For Each n In phones
        MsgBox n
    Next
    
End Sub

参考：http://msdn.microsoft.com/zh-cn/library/yb7y698k(v=vs.90).aspx

 

2. For Each 遍历数组时，For Each后跟的必须是Variant：

Sub test()

Dim names(3) As String
Dim n As Variant 'This can't be String! Must be Vaiant

names(0) = "Pre"
names(1) = "Jack"
names(2) = "Haha"
names(3) = "Rose"

For Each n In names
    MsgBox n
Next

End Sub

参考：http://stackoverflow.com/questions/4228137/vba-for-each-on-an-array

 

3. Function与Sub的区别：

1) Sub 过程定义时无需定义返回值类型，而Function 过程一般需要用“As 数据类型” 定义函数返回值类型。  
2) Sub 过程中没有对过程名赋值的语句，而Function 过程中一定有对函数名赋值的语句，一般在函数最后返回值，格式如下：

Set functionName = xxxxxx

3) 调用过程：调用 Sub 过程与 Function 过程不同。调用 Sub 过程的是一个独立的语句，而调用函数过程只是表达式的一部分。另外，自定义函数并不允许修改工作表和单元格格式 (A UDF will only return a value it won't allow you to change the properties of a cell/sheet/workbook. )。但是，与 Function过程一样，Sub 过程也可以修改传递给它们的任何变量的值。  
4) 调用 Sub 过程有两种方法：  
以下两个语句都调用了名为 MyProc 的 Sub 过程。  
Call  MyProc (FirstArgument, SecondArgument)  
MyProc  FirstArgument, SecondArgument  
注意当使用 Call 语法时，参数必须在括号内。若省略 Call 关键字，则也必须省略参数两边的括号。

 

4. 遍历Dictionary

For Each strKey In oDic.Keys()
    do_something
Next

 

5. Application.WorksheetFunction

调用Application.WorksheetFunction，可以在VBA中使用Excel已有的函数，例如Max,Min,Average等。常用的还有COUNTA这个函数：

Sub test()

    MsgBox Range("A1:A10").Count 'Output 10

    MsgBox Application.WorksheetFunction.CountA(Range("A1:A10")) 'Output 4 because 4 of 10 cells are not empty

End Sub

 

6.Standard Modules vs. Class Modules

面试曾被问到这题，之前的VBA全都是操作单元格为主，没想到还能面向对象。根据这里的定义，Standard Modules就像静态域，全局只有一个数据备份；而Class Modules就如普通的类，可以有多个对象，多个不同的数据备份。

 

7. Range.Offset和Range.Resize的返回值必须用set来引用。

如：

Set priceRange = priceRange.Resize(rowNum - 1, priceRange.Columns.Count) 'Set!!! 
Set actRange = actRange.Offset(1, -1)