第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程序窗口。