1.2 程序防錯(cuò)要點(diǎn)

在編寫VBA代碼前，需要了解程序通常會(huì)產(chǎn)生哪些錯(cuò)誤；在編寫程序時(shí)，需要對(duì)程序可能出現(xiàn)的錯(cuò)誤進(jìn)行防范，以及指定出錯(cuò)時(shí)需要采取的措施。本節(jié)對(duì) VBA 程序出錯(cuò)進(jìn)行常見的原因分析，以及羅列所有錯(cuò)誤的解釋，并通過實(shí)例演示防錯(cuò)手法及“錯(cuò)誤”的利用方案。

疑難4 程序出錯(cuò)有哪些原因

程序在運(yùn)行前或者運(yùn)行時(shí)都可能會(huì)出錯(cuò)，那么程序出錯(cuò)有哪些常見的原因呢？

解決方案

本例專注于程序出錯(cuò)的理論分析，輔助簡單案例進(jìn)行講解。讀者在編寫代碼時(shí)可以從文中提到的幾個(gè)方面檢查自己的代碼是否存在疏漏。

操作方法

※ 常見錯(cuò)誤分析 ※

程序出錯(cuò)包括編譯錯(cuò)誤和運(yùn)行時(shí)錯(cuò)誤。常見的編譯錯(cuò)誤包括命名錯(cuò)誤、拼寫錯(cuò)誤、語法錯(cuò)誤（不完整）、參數(shù)不完整。圖1-9所示屬于過程名稱錯(cuò)誤，Sub過程名、Function名及Names的名稱不能以數(shù)字開始；圖1-10所示則是方法“Shell”的拼寫有誤，多了一個(gè)“l(fā)”；圖1-11所示屬于IF…End IF語句缺少結(jié)束語句所致；圖1-12所示屬于參數(shù)不完整，DateSerial函數(shù)有三個(gè)必需參數(shù)，缺一不可。另一個(gè)比較常見的編譯錯(cuò)誤是“二義性的名稱”，它表示模塊中存在多個(gè)同名的過程，手工重命名Sub過程名或者Function過程名即可。

█ 圖1-9 命名錯(cuò)誤

█ 圖1-10 拼寫錯(cuò)誤

█ 圖1-11 語法不完整

█ 圖1-12 參數(shù)不完整

運(yùn)行時(shí)錯(cuò)誤種類相當(dāng)多，其中最常見的是代碼中的值超過了允許的范圍。例如工作簿中僅僅有3個(gè)工作表時(shí)調(diào)用“Sheets(4)”，則產(chǎn)生“下標(biāo)越界錯(cuò)誤”；而圖1-13所示對(duì)行高的賦值超過了允許的范圍0～409；圖1-14所示的單元格列坐標(biāo)105超過了允許的最大值16384；圖1-15所示是因?yàn)樽兞抠x值時(shí)超過了Byte型的有效范圍0～255；而圖1-16所示則因?yàn)槊Q的引用超過了1～255范圍而出錯(cuò)，同類的還有數(shù)據(jù)有效性的公式引用表達(dá)式不能超過255個(gè)字符，一次創(chuàng)建工作表不能超過255個(gè)，字體大小只能在1～409，字體顏色ColorIndex只能在0～56等。對(duì)于Open方法打開不存在的文件名也屬于超出范圍，保存文件到不存在的磁盤等都可以算超過有效范圍，在代碼中需要加入判斷才能防錯(cuò)。

█ 圖1-13 行高超過有效范圍0～409

█ 圖1-14 單元格列坐標(biāo)超過有效范圍16384

█ 圖1-15 對(duì)變量賦值時(shí)超過了變量的范圍

█ 圖1-16 名稱的引用超過最大值255

另外比較常見的還有“被零除”錯(cuò)誤。例如圖1-17所示為C2為0時(shí)就會(huì)出錯(cuò)，而圖1-18所示為a2:a9沒有數(shù)值時(shí)仍然是因零除數(shù)而出錯(cuò)。

█ 圖1-17 C2為0時(shí)產(chǎn)生被零除錯(cuò)誤

█ 圖1-18 當(dāng)a2:a9沒有數(shù)值時(shí)出錯(cuò)

原理分析

VBA中程序出錯(cuò)的可能性極多，但通常都有規(guī)律可循。其中編譯錯(cuò)誤最好處理，VBA會(huì)自動(dòng)找出出錯(cuò)的語句并告知出錯(cuò)原因；運(yùn)行時(shí)錯(cuò)誤最常見的是讀出和寫入時(shí)超過有效范圍。而所有有效范圍皆有據(jù)可查，可以從幫助中獲取所有信息。只要掌握其規(guī)律，編寫程序時(shí)出錯(cuò)的幾率就會(huì)大大減少。

知識(shí)擴(kuò)展

本文羅列的是代碼本身的錯(cuò)誤，而實(shí)際工作中還會(huì)有思路錯(cuò)誤，即代碼完全正確，但無法獲取需要的結(jié)果，這不在本書討論的范疇。

程序的錯(cuò)誤也有可以利用的時(shí)候，即故意通過程序代碼來獲取某些錯(cuò)誤信息，在后面的很多案例中都將涉及到。

疑難5 如何獲取VBA所有運(yùn)行時(shí)錯(cuò)誤的詳細(xì)說明

將VBA中的運(yùn)行時(shí)錯(cuò)誤羅列到工作表中，方便學(xué)習(xí)，而且可以查看詳細(xì)說明。

解決方案

要獲取所有錯(cuò)誤的編號(hào)及信息，可以利用Err對(duì)象的Raise方法人為制造錯(cuò)誤，然后利用Err.Number取出錯(cuò)誤編號(hào)，而用Err.Description屬性返回錯(cuò)誤信息。如果需要返回更詳細(xì)的信息，則借用 Err.HelpFile 得到錯(cuò)誤對(duì)應(yīng)的幫助文件，以及 Err.HelpContext 得到錯(cuò)誤主題的 ID號(hào)，最后用Application.Help方法可以根據(jù)錯(cuò)誤ID號(hào)打開對(duì)應(yīng)的幫助文件。

操作方法

步驟1 按【Alt+F11】組合鍵打開VBE窗口。選擇菜單“插入”→“模塊”，并輸入以下代碼：

Sub 獲取所有錯(cuò)誤類型編碼及含義()  '人工造成錯(cuò)誤并獲取錯(cuò)誤信息
  On Error Resume Next  '錯(cuò)誤時(shí)繼續(xù)執(zhí)行
  Dim arr(), i As Integer
  ReDim arr(1 To 1000, 1 To 2)  '聲明一個(gè)二維數(shù)組，1000行2列
  Range("A1:B1") = Array("錯(cuò)誤ID", "錯(cuò)誤描述(單擊查看詳細(xì)描述)")  '標(biāo)題賦值
  Range("A1:B1").Interior.ColorIndex = 3  '添加背景色
  VBA.Err.Raise 1   ' 故意產(chǎn)生運(yùn)行時(shí)錯(cuò)誤，方便后面的操作
  Cells(2, 1) = 1        '錯(cuò)誤編號(hào)
  Cells(2, 2) = Err.Description  '錯(cuò)誤類型
  For i = 2 To 1000      '提取2到1000之間的錯(cuò)誤類型
    VBA.Err.Raise i    '產(chǎn)生一個(gè)編號(hào)為i的錯(cuò)誤（其中有重復(fù)）
    '如果其錯(cuò)誤類型不和編號(hào)1相同則取其編號(hào)與錯(cuò)誤描述
    If Error(i) <> "應(yīng)用程序定義或?qū)ο蠖x錯(cuò)誤" Then
      arr(i + 1, 1) = Err.Number  '將錯(cuò)誤編號(hào)存入數(shù)組
      arr(i + 1, 2) = Err.Description  '將錯(cuò)誤描述存入數(shù)組
    End If
    Err.Clear  '清除錯(cuò)誤，方便取下一個(gè)錯(cuò)誤的相關(guān)信息
  Next i
  Range("A3:B1001") = arr  '將數(shù)組寫入單元格
  Columns("A:B").AutoFit  '自動(dòng)調(diào)整列寬
  '將A1:A1001中空單元格整行刪除
  Range("A1:A1001").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

步驟2 如果沒有顯示工程資源管理器則按【Ctrl+R】組合鍵打開工程資源管理器，雙擊當(dāng)前工程中的“ThisWorkbook”，并在右邊的代碼窗口中輸入以下代碼：

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
  If Target.Column = 2 Then '僅在選擇第二列時(shí)執(zhí)行
    On Error Resume Next '防錯(cuò)
    Err.Raise Target(1).Offset(0, -1) '產(chǎn)生一個(gè)錯(cuò)誤，錯(cuò)誤號(hào)為A列的值
    Application.Help Err.HelpFile, Err.HelpContext '打開對(duì)應(yīng)的幫助文件
  End If
End Sub

步驟3 雙擊工程資源管理器中的“模塊1”，光標(biāo)定位于過程“獲取所有錯(cuò)誤類型編碼及含義”中任意位置，按【F5】鍵執(zhí)行程序。

步驟4 關(guān)閉VBE窗口返回工作表界面，在A:B區(qū)域中已羅列出1到1000之間的運(yùn)行時(shí)錯(cuò)誤編號(hào)及錯(cuò)誤描述，如圖1-19所示。單擊B4單元格，可以打開編號(hào)為A4單元格的錯(cuò)誤信息的詳細(xì)描述，如圖1-20所示：

█ 圖1-19 羅列所有運(yùn)行時(shí)錯(cuò)誤

█ 圖1-20 編號(hào)為5的錯(cuò)誤信息描述

原理分析

VBA中的ERR對(duì)象提供了一整套關(guān)于錯(cuò)誤信息管理的完整方案，包括Err.Raise方法人工產(chǎn)生錯(cuò)誤、Err.Number屬性獲取錯(cuò)誤編號(hào)、Err.Description屬性獲取錯(cuò)誤信息、Err.HelpFile屬性獲取幫助文件名稱、Err.HelpContext 屬性獲取在幫助文件中的主題的上下文 ID，以及Err.Clear方法清除錯(cuò)誤。將以上各屬性、方法組合并循環(huán)即可獲取所有運(yùn)行時(shí)錯(cuò)誤的幫助信息。

除了表中羅列的錯(cuò)誤以外還有很多，不過它們的錯(cuò)誤信息都和第一條一致，所以代碼中利用“If Error(i) <> "應(yīng)用程序定義或?qū)ο蠖x錯(cuò)誤"”將它們排除了。

知識(shí)擴(kuò)展

※ 通過Error函數(shù)獲取錯(cuò)誤信息 ※

Error函數(shù)也可以取得錯(cuò)誤信息，和Err.Description屬性功能相近。不過Error函數(shù)可以在任何時(shí)候獲取指定編號(hào)的錯(cuò)誤信息，而 Description 屬性只有在程序出錯(cuò)時(shí)才能獲取當(dāng)前錯(cuò)誤的信息描述。

利用Error函數(shù)取代Description屬性也可以完成，且代碼更簡單，完整代碼如下：

Sub 獲取所有錯(cuò)誤類型編碼及含義2()  '不產(chǎn)生錯(cuò)誤，而利用Error函數(shù)來獲取錯(cuò)誤信息
  Dim arr(), i As Integer
  ReDim arr(1 To 1000, 1 To 2)  '聲明一個(gè)二維數(shù)組，1000行5列
  Range("A1:B1") = Array("錯(cuò)誤ID", "錯(cuò)誤描述(單擊查看詳細(xì)描述)")  '標(biāo)題賦值
  Range("A1:B1").Interior.ColorIndex = 3  '添加背景色
  Cells(2, 1) = 1           '錯(cuò)誤編號(hào)
  Cells(2, 2) = Error(1)   '錯(cuò)誤類型
  For i = 2 To 1000      '提取2到1000之間的錯(cuò)誤類型
    '如果其錯(cuò)誤類型不和編號(hào)1相同則取其編號(hào)與錯(cuò)誤描述
    If Error(i) <> "應(yīng)用程序定義或?qū)ο蠖x錯(cuò)誤" Then
      arr(i + 1, 1) = i  '將錯(cuò)誤編號(hào)存入數(shù)組
      arr(i + 1, 2) = Error(i)  '將錯(cuò)誤描述存入數(shù)組
    End If
  Next i
  Range("A3:B1001") = arr  '將數(shù)組寫入單元格
  Columns("A:B").AutoFit  '自動(dòng)調(diào)整列寬
  '將A1:A1001中空單元格整行刪除
  Range("A1:A1001").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

疑難6 如何在執(zhí)行完畢后報(bào)告出錯(cuò)次數(shù)及原因

VBA 代碼出錯(cuò)時(shí)，如果未通過代碼控制程序，通常在第一次出錯(cuò)時(shí)中斷程序；如果利用“On Error Resume Next”語句忽略錯(cuò)誤又會(huì)讓用戶不知道是否產(chǎn)生過錯(cuò)誤。那么如何實(shí)現(xiàn)當(dāng)程序中有錯(cuò)誤時(shí)就報(bào)告錯(cuò)誤的次數(shù)及其原因呢？以圖1-21所示的多工作表求平均成績且報(bào)告最小值為例講述設(shè)計(jì)思路。

█ 圖1-21 成績表

解決方案

利用On Error Goto Line讓程序出錯(cuò)時(shí)執(zhí)行指定標(biāo)簽處的語句，然后通過err. Description提取錯(cuò)誤信息，再配合“Resume Next”語句執(zhí)行下一句，直到記錄所有錯(cuò)誤。

操作方法

步驟1 按【Alt+F11】組合鍵打開VBE窗口。選擇菜單“插入”→“模塊”，并輸入以下代碼：

Sub 每個(gè)班求平均()  '對(duì)每個(gè)班級(jí)每個(gè)人員計(jì)算平均成績，且報(bào)告每個(gè)最差成績（羅列每一次
錯(cuò)誤信息的技術(shù)應(yīng)用）
  Dim ShtCount As Byte, RowCount As Byte, total, MinValue As Byte, ErrStr As String
  On Error GoTo err  '出現(xiàn)錯(cuò)誤時(shí)執(zhí)行Err標(biāo)簽后的語句
  For ShtCount = 1 To 5  '循環(huán)五次'如果A列空白則中轉(zhuǎn)到Line標(biāo)簽
    If WorksheetFunction.CountA(Sheets(ShtCount).[a:a]) = 0 Then GoTo Line
    For RowCount = 2 To Sheets(ShtCount).Cells(Rows.Count, 1).End(xlUp).
    Row  '循環(huán)每一行
      '在第五列返回平均值，即每個(gè)學(xué)生的平均成績
      Sheets(ShtCount).Cells(RowCount, 5) = WorksheetFunction.Average
      (Sheets(ShtCount).Cells(RowCount, 4).Offset(0, -2).Resize(1, 3))
      '記錄每個(gè)學(xué)生的總成績
      total = Application.Sum(Sheets(ShtCount).Cells(RowCount, 4).Offset
      (0, -2).Resize(1, 3))
      If MinValue = 0 Then MinValue = total
      '如果MinValue變量為0則賦值第一個(gè)總成績
      If MinValue > total Then MinValue = total
      '如果變量大于總成績則對(duì)變量賦值為總成績
    Next RowCount
    '記錄每個(gè)班級(jí)（工作表名）的最小總成績
    TempStr = TempStr & Chr(10) & Sheets(ShtCount).Name & MinValue
    MinValue = 0  '循環(huán)完一個(gè)工作表后將變量重置為0
Line:
  Next
  MsgBox TempStr  '程序結(jié)束前報(bào)告每個(gè)班最小總成績
  If i > 0 Then MsgBox "出錯(cuò)" & i & "次，原因分別為：" & ErrStr
  '如果有錯(cuò)誤則報(bào)告次數(shù)和原因
  Exit Sub  '退出程序
err:
  Application.StatusBar = err.Description  '在狀態(tài)欄顯示錯(cuò)誤原因
  i = i + 1  '累加變量，該變量表示出錯(cuò)次數(shù)
  ErrStr = ErrStr & Chr(10) & err.Description  '將所有錯(cuò)誤串連成一個(gè)字符串
  err.Clear  '清除當(dāng)前錯(cuò)誤
  Resume Next    '執(zhí)行下一句代碼
End Sub

步驟2 光標(biāo)定位于代碼中任意位置并按【F5】鍵執(zhí)行過程，程序?qū)?duì)每個(gè)工作表中所有學(xué)生成績計(jì)算平均值，并彈出圖1-22所示的最差成績列表和圖1-23所示的錯(cuò)誤次數(shù)及原因。

█ 圖1-22 每個(gè)班級(jí)最差成績列表

█ 圖1-23 報(bào)告出錯(cuò)次數(shù)及原因

原理分析

※ 捕捉所有錯(cuò)誤信息 ※

記錄錯(cuò)誤次數(shù)和原因主要基于“On Error GoTo err”、“err.Clear”和“Resume Next”三者的配合，缺一不可。“On Error GoTo err”表示遇到錯(cuò)誤就執(zhí)行“err”標(biāo)簽后的語句，而在該標(biāo)簽處提取錯(cuò)誤信息后，通過“err.Clear”立即清除當(dāng)前錯(cuò)誤，然后利用“Resume Next”語句繼續(xù)執(zhí)行錯(cuò)誤語句后面的代碼。本例中“不能取得類 WorksheetFunction 的 Average 屬性”是因?yàn)橛幸粚W(xué)生成績?yōu)榭瞻姿拢薷姆椒ㄊ抢?Count 函數(shù)和 IF 判斷是否存在數(shù)值，如果沒有則略過；而兩次“溢出”則由“MinValue As Byte”這個(gè)錯(cuò)誤的變量聲明造成，因?yàn)槌煽兇笥?55，可以將“Byte”修改成“Integer ”來解決；而“下標(biāo)越界”則因?yàn)楣ぷ鞅砜倲?shù)為4，F(xiàn)or...Next循環(huán)的上限為5。可以將5修改為“Sheets.count”，即自動(dòng)計(jì)算工作表數(shù)。

知識(shí)擴(kuò)展

Resume用于在錯(cuò)誤處理程序結(jié)束后恢復(fù)原有的運(yùn)行，通常搭配Next使用。

Application.StatusBar表示狀態(tài)欄顯示的文字，可讀寫，通常用它展示程序進(jìn)度。

疑難7 如何開發(fā)完善的程序

編寫代碼解決一個(gè)工作問題是很簡單的，但是如何讓程序完善，可以適應(yīng)所有環(huán)境，且通用、兼容、可防錯(cuò)，這是一門相當(dāng)復(fù)雜的學(xué)問。那么如何開發(fā)一個(gè)完善的程序，程序開發(fā)常會(huì)有什么錯(cuò)誤及如何防范呢？

解決方案

通過常規(guī)思路開發(fā)一段程序完成基本需求；然后查找存在的問題并進(jìn)行完善；再對(duì)新的過程審核是否有新問題，繼續(xù)完善，直到?jīng)]有任何問題。

操作方法

步驟1 讓用戶從對(duì)話框輸入一個(gè)值，并對(duì)該值開平方后寫入活動(dòng)單元格。根據(jù)題目需求，編寫以下代碼：

Sub 獲取平方根1()
    Dim Value As Long
    Value = InputBox("請輸入數(shù)值：", "待開方之?dāng)?shù)值", 0)
    ActiveCell.Value = Sqr(Value)
End Sub

步驟2 執(zhí)行以上過程，并輸入100或者789、123.455等數(shù)據(jù)測試，可以發(fā)現(xiàn)實(shí)現(xiàn)了需求的功能。然而如果用戶在對(duì)話框上單擊“取消”按鈕，那么程序會(huì)出錯(cuò)。修改代碼如下：

Sub 獲取平方根2()    '解決單擊“取消”按鈕問題
    Dim Value As Variant
    Value = InputBox("請輸入數(shù)值：", "待開方之?dāng)?shù)值", 0)
    If Len(Value) = 0 Then Exit Sub
    ActiveCell.Value = Sqr(Value)
End Sub

步驟3 再執(zhí)行程序驗(yàn)證，單擊“取消”按鈕后程序會(huì)自動(dòng)退出，具有了防錯(cuò)功能。然而輸入一個(gè)負(fù)數(shù)，程序仍然會(huì)出錯(cuò)。繼續(xù)改進(jìn)代碼：

Sub 獲取平方根3()    '解決負(fù)數(shù)問題
    Dim Value
    Value = InputBox("請輸入數(shù)值：", "待開方之?dāng)?shù)值", 0)
    If Len(Value) = 0 Then Exit Sub
    If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox
    "不能小于0"
End Sub

步驟4 當(dāng)輸入負(fù)數(shù)后，程序會(huì)提示用戶，然后退出。但如果用戶輸入文本，程序仍然會(huì)出錯(cuò)，所以再次對(duì)代碼做優(yōu)化：

Sub 獲取平方根4()    '解決文字問題
    Dim Value
Value = InputBox("請輸入數(shù)值：", "待開方之?dāng)?shù)值", 0)
If Len(Value) = 0 Then Exit Sub
    If VBA.IsNumeric(Value) Then
        If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "
        不能小于0"
    Else
        MsgBox "不能輸入文本", 64, "提示"
    End If
End Sub

步驟5 如果輸入文本，程序具有了識(shí)別并警告用戶的功能。然而，活動(dòng)表是圖表時(shí)，執(zhí)行程序仍然會(huì)出錯(cuò)。完善的程序需要處理所有意外，那么程序可以做如下改進(jìn)：

Sub 獲取平方根5()    '解決圖表問題
    Dim Value
    If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要選擇圖表": Exit Sub Value = InputBox("請輸入數(shù)值：", "待開方之?dāng)?shù)值", 0)
If Len(Value) = 0 Then Exit Sub
    If VBA.IsNumeric(Value) Then
      If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "
      不能小于0"
    Else
      MsgBox "不能輸入文本", 64, "提示"
    End If
End Sub

步驟6 如果工作表被保護(hù)狀態(tài)下執(zhí)行以上程序仍然會(huì)出錯(cuò)，繼續(xù)完善代碼：

Sub 獲取平方根6()    '解決工作表保護(hù)問題
    Dim Value
    If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要選擇圖表": Exit Sub
    If ActiveSheet.ProtectContents Then MsgBox "工作表已保護(hù)": Exit Sub
Value = InputBox("請輸入數(shù)值：", "待開方之?dāng)?shù)值", 0)
If Len(Value) = 0 Then Exit Sub
    If VBA.IsNumeric(Value) Then
      If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "
      不能小于0"
    Else
      MsgBox "不能輸入文本", 64, "提示"
    End If
End Sub

步驟7 如果活動(dòng)單元格處于數(shù)組區(qū)域之間，程序仍然會(huì)產(chǎn)生錯(cuò)誤，所以最后將代碼優(yōu)化為：

Sub 獲取平方根7()    '解決數(shù)組區(qū)域問題
    Dim Value
    If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要選擇圖表": Exit Sub
    If ActiveSheet.ProtectContents Then MsgBox "工作表已保護(hù)": Exit Sub
Value = InputBox("請輸入數(shù)值：", "待開方之?dāng)?shù)值", 0)
If Len(Value) = 0 Then Exit Sub
    If VBA.IsNumeric(Value) Then
      On Error Resume Next
      Debug.Print ActiveCell.CurrentArray
      If Err = 0 Then MsgBox "請不要選擇數(shù)組區(qū)域": Exit Sub
      If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "
      不能小于0"
  Else
      MsgBox "不能輸入文本", 64, "提示"
  End If
End Sub

原理分析

編程的基本條件是準(zhǔn)確性。然而程序除了準(zhǔn)確以外，還必須具備防錯(cuò)、通用的功能，否則代碼在當(dāng)前狀態(tài)下正確執(zhí)行，環(huán)境稍加變化就出現(xiàn)錯(cuò)誤，將會(huì)增加很多維護(hù)成本。一個(gè)好的程序應(yīng)該盡量通用于所有狀況，而本例正是通過一個(gè)典范來展示程序的完善過程，讓讀者對(duì)程序可能出現(xiàn)的錯(cuò)誤進(jìn)行了解，并提供所有錯(cuò)誤的解決之道。在實(shí)際工作中，都應(yīng)該嚴(yán)格按此思路編寫代碼，提升程序的準(zhǔn)確性、糾錯(cuò)性，同時(shí)也減少維護(hù)成本。

知識(shí)擴(kuò)展

※ 數(shù)組區(qū)域?qū)BA程序的影響 ※

區(qū)域數(shù)組公式是同時(shí)存在于多個(gè)連續(xù)單元格中的帶有“{}”標(biāo)志的公式，將它輸入到工作表后就將占據(jù)一個(gè)區(qū)域，而非一個(gè)單元格，該區(qū)域即為數(shù)組區(qū)域。它的特性是不能單獨(dú)修改區(qū)域中任意一個(gè)單元格，如果代碼修改其中一個(gè)單元格，程序會(huì)中斷。

本例中也可以利用“On Error Resume Next”一次性解決所有問題，其代碼如下。不過如果執(zhí)行程序后得不到結(jié)果，就無法知道出錯(cuò)的原因。

Sub 獲取平方根8()    '解決所有問題
    On Error Resume Next
    ActiveCell.Value = Sqr(Application.InputBox("請輸入數(shù)值：", "開平方",
    0, , , , , 1))
End Sub

注意

本書中編程的主題是準(zhǔn)確性、效率、防錯(cuò)性和兼容性，代碼一定要對(duì)所有錯(cuò)誤進(jìn)行防范，確保程序通用。然而為了節(jié)約篇幅，讓書中展現(xiàn)更多內(nèi)容，所以盡量減少重復(fù)代碼，對(duì)于工作表是否保護(hù)、當(dāng)前表是否為圖表，以及活動(dòng)單元格是否在數(shù)組區(qū)域之間就不再對(duì)每個(gè)案例都進(jìn)行判斷了，只對(duì)其他出錯(cuò)的可能性進(jìn)行防錯(cuò)。但讀者在實(shí)際工作中應(yīng)該全面進(jìn)行防錯(cuò)。

疑難8 防錯(cuò)語句在程序或者函數(shù)中應(yīng)用有哪些優(yōu)勢

運(yùn)行代碼時(shí)出現(xiàn)錯(cuò)誤絕非程序員喜聞樂見之事。然而在特殊情況下，正如蛇的毒液也有它的良性作用一樣，編程時(shí)也會(huì)有意地利用代碼錯(cuò)誤來工作。那么如何在編輯過程中體現(xiàn)防錯(cuò)語句的優(yōu)勢呢？

解決方案

本例通過一個(gè)SUB過程和一個(gè)Function過程展示如何利用錯(cuò)誤讓程序更完善。有意地制造錯(cuò)誤，并根據(jù)錯(cuò)誤來判斷工作表是否存在及單元格是否存在數(shù)據(jù)有效性設(shè)置。

操作方法

步驟1 以指定日期為基數(shù)，創(chuàng)建一個(gè)月的工作表，每個(gè)工作表按每天的日期命名，代碼如下：

Sub 按月創(chuàng)建新表()       '創(chuàng)建一個(gè)月的工作表
  Dim Date1 As Date    '聲明一個(gè)日期變量
  Date1 = #1/15/2009#  '指定基數(shù)
'從基數(shù)日期開始，到下月同日的前一天，
  For i = Date1 * 1 To DateAdd("m", 1, Date1) * 1 - 1
    Sheets.Add after:=Sheets(Sheets.Count)    '創(chuàng)建一個(gè)新表，放到末尾
    ActiveSheet.Name = Format(i, "mm月dd日")  '命名為日期
  Next
End Sub

步驟2 以上程序基本可以實(shí)現(xiàn)需要的功能，然而程序并不完善，如果工作簿中有某個(gè)工作表與該時(shí)間段的日期同名，那么程序會(huì)出錯(cuò)并中斷執(zhí)行。為了提升程序兼容性，應(yīng)該判斷是否存在同名工作表，如果沒有就新建工作表并命名，否則忽略該日期。優(yōu)化后的代碼如下：

Sub 按月創(chuàng)建新表2()      '創(chuàng)建一個(gè)月的工作表
  Dim Date1 As Date    '聲明一個(gè)日期變量
  Date1 = #1/15/2009#  '指定基數(shù)
  On Error Resume Next'錯(cuò)誤時(shí)繼續(xù)執(zhí)行下一句
  '從基數(shù)日期開始，到下月同日的前一天
  For i = Date1 * 1 To DateAdd("m", 1, Date1) * 1 - 1
    Debug.Print Sheets(Format(i, "mm月dd日")).Name '輸出工作表名
    '如果無錯(cuò)誤（表示存在名為Format(i, "mm月dd日")的工作表），則執(zhí)行l(wèi)ine后面的語句
    If Err = 0 Then GoTo line
    Sheets.Add after:=Sheets(Sheets.Count)     '創(chuàng)建一個(gè)新表，放到末尾
    ActiveSheet.Name = Format(i, "mm月dd日")   '命名為日期
    line: '指定一個(gè)標(biāo)簽，在指定條件時(shí)可以跳轉(zhuǎn)至此處
  Next
End Sub

步驟3 在自定義函數(shù)中，錯(cuò)誤同樣有利用價(jià)值。例如判斷單元格是否設(shè)置了數(shù)據(jù)有效性，沒有直接的函數(shù)或者屬性來判斷，例如，HasFormula可以一步直接判斷單元格是否有公式。本例以開發(fā)一個(gè)判斷單元格是否存在有效性設(shè)置來演示防錯(cuò)的應(yīng)用，代碼如下：

'定義一個(gè)函數(shù)，判斷單元格是否有數(shù)據(jù)有效性設(shè)置，結(jié)果為True或者False
Function IsValidation(rng As Range) As Boolean
  On Error Resume Next  '錯(cuò)誤時(shí)繼續(xù)執(zhí)行
  Dim Str As String
  '提取單元格的有效性公式，如果參數(shù)為區(qū)域則取左上角單元格
  Str = rng(1).Validation.Formula1
  IsValidation = (Err.Number = 0)  '公式結(jié)果由是否產(chǎn)生錯(cuò)誤來決定
End Function

步驟4 對(duì)單元格A1設(shè)置任意數(shù)據(jù)有效性，并在B1輸入公式“=IsValidation(A1)”，那么公式的結(jié)果將為“TRUE”，表示有數(shù)據(jù)有效性設(shè)置，如圖1-24所示；而圖1-25所示的新工作表則是執(zhí)行過程“按月創(chuàng)建新表”的結(jié)果，創(chuàng)建了數(shù)量等于一個(gè)月天數(shù)的新工作表，且以日期命名。

█ 圖1-24 驗(yàn)證函數(shù)IsValidation

█ 圖1-25 創(chuàng)建一個(gè)月的工作表

原理分析

單元格是否有公式、是否保護(hù)等有現(xiàn)成的屬性可以直接判斷，而是否有批注或者是否設(shè)置數(shù)據(jù)有效性則沒有現(xiàn)成的函數(shù)或者屬性進(jìn)行判斷。

如果單元格有數(shù)據(jù)有效性設(shè)置，那么讀取有效性設(shè)置的 Formula1屬性會(huì)失敗。基于這一特性，那么在代碼中讓程序出錯(cuò)時(shí)繼續(xù)執(zhí)行，然后檢查Err.Numner屬性是否等于0就可以判斷是否存在有效性了。而檢查指定名稱的工作表、工作簿是否存在也基于這個(gè)道理。

知識(shí)擴(kuò)展

※ 利用Err.Number屬性判斷錯(cuò)誤類型 ※

程序中沒有“運(yùn)行時(shí)錯(cuò)誤”時(shí)，Err.Number屬性值為0；而如果產(chǎn)生了錯(cuò)誤，則根據(jù)錯(cuò)誤類型會(huì)產(chǎn)生不同的編號(hào)，可能是正數(shù)，也可能是負(fù)數(shù)。

如果程序有多個(gè)錯(cuò)誤，要注意Err.Number取最近一次錯(cuò)誤編號(hào)。那么有可能該錯(cuò)誤不是預(yù)想中的錯(cuò)誤，此時(shí)需要判斷具體的Err.Number，而不是用“<>0”來判斷結(jié)果。