第1章 基础理论

VBA 主要用于解决工作中遇到的某个实际问题，或者开发插件程序来解决一类问题。在掌握开发技巧前，需要了解一些基本的方法及原则，包括获取帮助的方法、代码优化方法，以及程序防错原则、代码兼容性原则等。

程序的四个支柱是准确性、效率、防错性和兼容性，本章对这四个方面进行一些基本分析，在后面的章节中将通过大量的案例来体现这四大支柱。而本书的所有案例也力求同时兼顾，从而使代码更加完善。

1.1 快速获取帮助

在 VBA编程过程中，通常需要在使用控件时了解所有控件的属性及属性值，输入对象名称后需要及时获取其成员列表信息，在程序出错时获取出错原因等信息。本节将针对如何获取这些帮助信息进行案例演示及原理分析。

疑难1 如何获取所有控件的属性及属性值

VBA编程一定会涉及诸多控件，在初学VBA时有必要了解控件包括哪些属性。那么如何利用VBA代码一次性罗列这些控件或者窗体的属性及属性值呢？

解决方案

ActiveX 控件和 VBE 窗体中的控件可以借助 TypeLib Information 这个对象提供的InterfaceInfoFromObject 方法来获取所有属性。而窗体的属性则通过 VBComponents 集合的Properties属性来获取。

在编写代码时有两种解决方案，包括前期绑定和后期绑定。

操作方法

获取窗体中控件的属性，需要引用TypeLib Information控件，具体步骤如下：

步骤1 按【Alt+F11】组合键打开VBE窗口。

步骤2 选择菜单“工具”→“引用”，打开“引用”对话框，单击“浏览”按钮进入System32目录，从中选择文件“TLBINF32.DLL”并双击，在“引用”对话框中将看到名为“TypeLib Information”的引用，如图1-1所示，将其勾选后单击“确定”按钮，从而添加“TypeLib Information”的引用。

█ 图1-1 添加TypeLib Information引用

步骤3 选择菜单“插入”→“模块”，并在新模块中输入以下代码：

Sub 获取窗体中控件的属性列表()
  On Error Resume Next '遇到错误时继续执行
  Dim 控件 As Object, 数量 As Long, arr() As String
  For Each 控件 In UserForm1.Controls '遍历窗体中所有控件
    Dim 信息 As InterfaceInfo, mem As MemberInfo '声明变量，用于获取控件属性
    Set 信息 = InterfaceInfoFromObject(控件)  '从控件获取属性
    If Not (信息 Is Nothing) Then  '如果有可以提取的信息
      For Each mem In 信息.Members  '遍历信息的子项目
      'InvokeKind表示对象属性，其成员有INVOKE_FUNC、INVOKE_PROPERTYGET、
      'INVOKE_PROPERTYPUT、INVOKE_PROPERTYPUTREF
      If mem.InvokeKind And INVOKE_PROPERTYGET Then
      数量=数量 + 1  '累加变量，它表示找到的信息个数
        '重新声明数组的维数，当找到的信息增加时，数组的维数也相应增加
        ReDim Preserve arr(1 To 3, 1 To 数量)
        arr(1, 数量) = 控件.Name  '对数组的第一行，最后一列赋值为控件名称
        arr(2, 数量) = mem.Name  '对数组的第二行，最后一列赋值为属性名称'对数组的第三行，最后一列赋值为属性值
        arr(3, 数量) = CallByName(控件, mem.Name, VbGet)
      End If
      Next
    End If
  Next
  If 数量> 0 Then  '如果找到有目标值
  [a1:c1] = Array("控件", "属性", "值")    '写入标题
  [a2].Resize(数量, 3) = WorksheetFunction.Transpose(arr)
  '将数组的值导入工作表
  Columns("a:c").AutoFit  'A:C列自动适应列宽
  Columns("a:c").HorizontalAlignment = xlLeft  '左对齐
  End If
End Sub

步骤4 光标定位于代码中任意位置，按【F5】键执行过程“获取窗体中控件的属性列表”，在工作表中将会罗列出名为“UserForm1”的窗体中所有控件的属性，包括名称及其属性值，如图1-2所示。

█ 图1-2 获取窗体中控件的属性

如果在工作表中插入 OLE 控件——图像控件和选项按钮，获取它们的属性及属性值可以采用与上面相同的思路，仅仅对其中一句代码稍加修改即可，即第四句修改为：

For Each 控件 In Sheets(1).OLEObjects '遍历工作表中所有OLE对象

而对于窗体的属性及属性值，可以使用以下代码实现：

'2007和2010用户需要进入“信任中心”→“宏设置”，勾选“信任对VBA工程对象模型的访问”'2003用户进入“工具”→“宏”→“安全性”→“可靠发行商”，勾选“信任对“Visual Basic'项目”的访问”
Sub 获取窗体的属性列表()  '代码通用于Excel 2003、2007和2010
  On Error Resume Next
  Dim i As Long, j As Long, 数量 As Long, arr() As String
  With ThisWorkbook.VBProject.VBComponents
    For i = 1 To .Count     '遍历工程中所有部件
      If .Item(i).Type = 3 Then  '如果其类型是窗体（值为1表示模块，2表示类模块）
      '遍历窗体的所有属性
      For j = 1 To ThisWorkbook.VBProject.VBComponents(i).Properties.
      Count
      数量=数量 + 1  '累加计数器，该变量代表窗体属性的个数
        ReDim Preserve arr(1 To 3, 1 To 数量)  '重新声明数组的维数
        With ThisWorkbook.VBProject.VBComponents(i)
          arr(1, 数量) = .Name  '对数组第一行赋值为窗体名
          arr(2, 数量) = .Properties(j).Name  '对数组第一行赋值为属性名
          arr(3, 数量) = .Properties(j)    '对数组第一行赋值为属性值
          End With
      Next j
      End If
    Next
  End With
  If 数量> 0 Then  '如果找到目标值
    [a1:c1] = Array("控件", "属性", "值")    '写入标题
    [a2].Resize(数量, 3) = WorksheetFunction.Transpose(arr)
    '将数组的值导入工作表
    Columns("a:c").AutoFit  'AC列自动适应列宽
    Columns("a:c").HorizontalAlignment = xlLeft  '左对齐
  End If
End Sub

原理分析

TLBINF32.DLL 控件中的 InterfaceInfoFromObject 方法可以从任意控件中获取属性信息，利用对象变量配合For...Next循环可以提取每个控件的所有属性值。但是TLBINF32.DLL控件默认未引用，不能直接使用其提供的对象与方法，在使用前必须手工添加引用。

数组变量在多次重新分配存储空间时，只能重新分配最后一维的空间。基于此特性，本例声明变量Arr时采用arr(1 To 3, 1 To 变量)方式重置数组变量的存储空间，本例中表示3行多列。而最后赋值到单元格时需要3列多行，所以必须利用Transpose函数将其旋转90度后再赋值给单元格。

知识扩展

本例程序较好地展现了程序准确性、效率、防错性和兼容性四大支柱，在准确获取指定信息基础上，使用了“On Error Resume Next”防错技术及“If 数量> 0 Then”语句解决未找到控件时对单元格写入而产生的错误；使用了数组来提升执行效率，仅执行一次单元格写入操作即可将所有属性值导入到工作表中；代码在Excel 2003、2007和2010中都能正常运行。

VBProject代表工作簿中的Visual Basic项目，为了提升安全性，默认状态下Excel禁止读取和修改 VBProject 中的任何对象属性。在必要时，只能勾选“信任对 VBA 工程对象模型的访问”才执行程序。Excel 2007和2010用户需要进入“信任中心”→“宏设置”勾选“信任对VBA工程对象模型的访问”，而Excel 2003用户需要进入“工具”→“宏”→“安全性”→“可靠发行商”，勾选“信任对“Visual Basic项目”的访问”。

由于对单元格写入数据需要时间，为了提升程序的执行效率，应尽量减少单元格写入次数。而写入数组的速度大大高于写入单元格，所以本例中将每个找到的属性分别写入数组，最后一次性将数组的值写入单元格。

※ 前期绑定与后期绑定 ※

对于外部控件引用，存在前期绑定技术和后期绑定技术之分。前期绑定是指手工添加文件引用，其优势在于编写代码时自动列出成员信息，包括属性、方法及参数列表；而后期绑定是指不引用文件，而通过代码CreateObject来创建对象实例。对于获取窗体中控件所有属性的需求，如果改用后期绑定，那么代码如下：

Sub 获取窗体中控件的属性列表2()  '后期绑定技术的应用
  On Error Resume Next    '遇到错误时继续执行
  Dim CreateTLIobject As Object, 控件 As Object, 数量 As Long,arr()As String
  '创建对象引用，这属于后期绑定技术，优点是不需要手工添加控件引用
  Set CreateTLIobject = CreateObject("TLI.TLIapplication")
  For Each 控件 In UserForm1.Controls    '遍历窗体中所有控件
    Set 信息= CreateTLIobject.InterfaceInfoFromObject(控件)
    '从控件中获取属性
    If Not (信息 Is Nothing) Then  '如果有可以提取的属性
      For Each mem In 信息.Members  '遍历信息的子项目
      If mem.InvokeKind And VbGet Then  '使用IF判断，避免提取不必要的信息
      数量=数量+ 1  '累加变量，它表示找到的信息个数
        '重新声明数组的维数，当找到的信息增加时，数组的维数也相应增加
        ReDim Preserve arr(1 To 3, 1 To 数量)
        arr(1, 数量) = 控件.Name  '对数组的第一行，最后一列赋值为控件名称
        arr(2, 数量) = mem.Name   '对数组的第二行，最后一列赋值为属性名称
        '对数组的第三行，最后一列赋值为属性值
       arr(3, 数量) = CallByName(控件, mem.Name, VbGet)
      End If
    Next
  End If
  Next
  If 数量> 0 Then  '如果找到有目标值
  [a1:c1] = Array("控件", "属性", "值")    '写入标题
  [a2].Resize(数量, 3) = WorksheetFunction.Transpose(arr)
  '将数组的值导入工作表
  Columns("a:c").AutoFit  'AC列自动适应列宽
  Columns("a:c").HorizontalAlignment = xlLeft  '左对齐
  End If
End Sub

CallByName用于设置或者获取控件的属性，例如获取窗体中指定控件指定属性的值：

MsgBox "按钮的左边距是" & CallByName(UserForm1.CommandButton1, "left", VbGet)

注意

本例案例文件与代码参见本书光盘：..\第1章\疑难1.xlsm

疑难2 部分对象在输入时没有成员列表提示，如何解决

Range或者WorksheetFunction等大部分对象在输入代码时会自动列出成员列表，如图1-3所示，方便程序员快速而准确地完成代码。但是有部分对象如cells、[a1]、Worksheet等却没有提示，有没有变通的方法可以实现呢？

█ 图1-3 Range对象的属性与方法列表

解决方案

Cells对象与Range对象所有属性、方法都一致，所以借用Range来实现Cells的成员提示；而Worksheet则可以通过声明变量的方案来解决。

操作方法

让Cells(a,b)对象产生成员列表，可用以下方式进行：

步骤1 在“cells(1,2)”后面输入一个空格。

步骤2 继续输入“range.”，后面将自动列出成员列表。而再输入字母i，那么用于设置单元格内部的颜色、字体等相关的属性值“Interior”就出现在列表中，如图1-4所示。此时单击属性“Interior”即可完成输入。此方式可以确保单词的正确性，防止手工输入“Interor”或者“Intarior”等错误。

█ 图1-4 借用Range实现cells和[a1]的成员列表

步骤3 当输入“Interior”后，删除“range.”和空格，使“Interior”属性应用于Cells(a,b)对象。

对于输入“Sheets(2)”后不能自动列出成员列表，可以按以下方式进行：

步骤1 声明一个Worksheet对象变量：

Dim sht As Worksheet

步骤2 将Sheets(2)赋值给变量sht：

Set sht = Sheets(2)

步骤3 输入“sht.”，其后面立刻罗列出成员列表，如图1-5所示。

█ 图1-5 通过声明对象变量实现成员列表

原理分析

单元格有多种表达方式，range(“A1”)、cells(1,1)、[a1]都表示同一个对象，其属性是相通的。所以可以借用Range的列表来输入cells(1,1)、[a1]的属性与方法。

VBA所有对象变量都一定有成员列表，基于此特性，Sheets(2)后面不会产生成员列表的问题利用对象变量即可解决。

知识扩展

※ 没有成员列表的对象 ※

与sheets(2)相同，Shapes、OLEObjects、ChartObjects、Scripts、Selection等都无法产生成员列表，即输入对象后没有方法与属性提示，只能手动输入完整的代码，这不利于提升代码的输入速度和准确性，可采用与本例相同方式处理，使其自动弹出成员列表供选择。

疑难3 如何找出错误语句及获取错误原因并发送到开发者邮箱

如果程序中有语法错误，如何找到错误语句？如果自己开发的程序在客户使用时产生错误，如何让程序获取错误原因并将其发送到程序开发者邮箱？

解决方案

对于出错语句，VBA有内置调试工具，可以自行定位出错语句；也可以通过设置VBE选项让VBE自动识别错误函数或者方法；对于语法错误造成的错误信息则可以利用error函数来捕捉。最后使用API函数ShellExecute调用邮件程序将错误信息反馈到开发者邮箱。

操作方法

对于有语法错误的语句，例如函数或者方法的单词拼写错误，可以通过选项设置让 VBA自动查找错误。步骤如下：

步骤1 选择菜单“工具”→“选项”，打开“设置”对话框。

步骤2 在“编辑器”选项卡中选择“自动语法检测”，表示运行时让 VBA 检查代码是否存在语法错误，及时将错误反馈给用户。

步骤3 进入“通用”选项卡，将“错误捕获”设置为第三项，表示忽略代码中处理过的、允许出错的语句。在特殊情况下，会故意让代码产生错误。

步骤4 按【F5】键运行有语法错误的代码，如图1-6所示，其中“msgbox”函数拼写错误，VBA 会自动报告函数未定义，同时选定该函数；而运行如图1-7所示的代码同样因“Activate”拼写错误而产生提示。

█ 图1-6 函数拼写错误

█ 图1-7 方法拼写错误

步骤5 对于以下过程，如果工作簿中不存在“Sheet4”工作表，那么代码正确的前提下运行代码后仍然会产生错误：

Sub 提取工作表名()
    MsgBox Sheet4.Name
End Sub

将以上运行时错误发送到开发者邮箱，可以通过以下方式改造代码：

'声明API，用于调用邮件程序
Private Declare Function ShellExecute Lib "shell32.dll" Alias "Shell
ExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile
As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal
nShowCmd As Long) As Long
Sub 提取工作表名()
    On Error Resume Next '错误时继续执行
    MsgBox sheet4.Name
    If Err <> 0 Then '如果有错误则向作者发送信息，包括日期、用户名、程序名和错误信息
      Dim MyMail As String
      MyMail = "mailto:excelbbx@163.com?subject=反馈&body=程序“提取工作
      表名”出错，类型为“" & Error(Err.Number) & "”%0A" + Space(20) + "
      用户：" + Application.UserName & "%0A" + Space(20) & Date
      ShellExecute 0&, vbNullString, MyMail, vbNullString, vbNullString, 1
  End If
End Sub

步骤6 当执行以上过程后，如果发生错误则会自动打开如图1-8所示的邮件窗口。单击“发送”按钮后即可将信息反馈给程序开发者。

█ 图1-8 出错时自动启动邮件反馈错误信息

原理分析

VBA 内置的错误捕获设置可以检查到所有语法错误，例如函数名、过程名、属性名、方法名等，称为“编译错误”，代码执行之前就可以捕捉到；对于读取不存在的对象这类错误称为“运行时错误”，只能在运行代码时才会捕捉到，并在出错语句处停止，用黄色背景标识该语句。为了提取错误信息，必须使用“On Error Resume Next”让程序出错时继续执行，但将错误代码Err.Number通过Error函数转换成字符串，最后向作者反馈。

知识扩展

本例中错误信息“要求对象”表示未找到代码中指定的对象，即工作簿中不存在“Sheet4”这个工作表。如果使用“Sheets(4)”，那么会产生“下标越界”的错误信息，表示下标4超过了Sheets集合的最大上限，程序找不到指定的对象。

※ 利用ShellExecute函数调用程序的方式 ※

ShellExecute函数用于调用Windows中文件的默认关联程序。例如本例中需要调用发送邮件的软件，不管系统中安装有多少个邮件软件，例如Outlook、Foxmail或者Outlook Express，程序总是指向默认的程序。笔者将“Foxmail”设置为默认邮件程序，所以执行代码会打开Foxmail程序窗口。