VBA 一些概念与细节

#1 Set赋值

对象变量的赋值须使用Set，例如：Set newbook = Workbooks.Add

 

#2 工作簿(Workbook)的引用方法

Workbooks("A.xls")表示名称为A的excel工作簿，引用前须先打开A工作簿。

ActiveWorkbook 表示打开多个工作簿时的活跃工作簿。

ThisWorkbook表示这段代码所在的工作簿，不会随着打开新工作簿而改变。

 

#3 工作表(Worksheets)集合的遍历

可以通过workbook1.WorkSheets方法获取workbook1所有工作表的集合

当对上述集合进行遍历时，遍历顺序与实际使用顺序相同；但存在隐藏工作表时，会先正序遍历显示工作表，再倒序遍历隐藏工作表，例如：

　　使用看到的顺序：sh1, sh2（隐藏）, sh3（隐藏）, sh4, sh5（隐藏）, sh6

　　程序遍历顺序：sh1, sh4, sh6, sh5, sh3, sh2

 

#4 打开新工作簿事件：auto_open与Workbook_Open

Sub auto_open() 函数布置在任意一个Module中即可；Private Sub Workbook_Open须布置在ThisWorkbook。

Workbook_Open优先于auto_open运行。

Workbook_Open总会运行。auto_open在代码打开的新文档中不会运行。

 

#5 可多选的下拉单元格

有时我们需要某个单元格所填写内容为指定内容集合的子集，这是单纯数据验证无法做到的。可以通过工作表的事件配合数据验证来实现这个功能。

下面这段代码给所属工作表中所有第五列的单元格添加了多选功能，每次选择将会把新内容添加在原始内容之后，并以逗号分隔。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldVal As String
    Dim newVal As String
    
    If Target.Count > 1 Then GoTo exitHandler
    
    If Target.Column = 5 Then
        Application.EnableEvents = False
        newVal = Target.value

　　　　 On Error Goto exitHandler
        Application.Undo
        oldVal = Target.value
        If newVal = "清除" Then
            Target.value = ""
        ElseIf oldVal = "" Then
            Target.value = newVal
        ElseIf newVal = "" Then
            Target.value = ""
        Else
            Target.value = oldVal & ", " & newVal
        End If
    End If
    
exitHandler:
        Application.EnableEvents = True
End Sub

　　

#6 按钮指定宏的刷新

按钮是界面交互功能最常见的途径，而所有的OLEObject都可以成为一个按钮，因为其本质是设定对象OnAction属性所指向的过程。

在Excel文件绝对路径的变化中（包括移动位置、改名），按钮的指定宏是会随之更新的。但奇怪的是，在绝对路径变化之后，复制文件中原有表，得到的新表中的按钮的宏仍然指向变化之前宏的路径，并将导致错误。

因此，对于有复制表需求的Excel文件，需要对于其按钮进行宏更新，如：

ActiveSheet.Shapes(i).OnAction = "ModuleX.SomeSub"