1.4 提升代碼的兼容性

編寫程序通常有自用和他人使用兩種狀況。如果程序是他人使用，那么就存在不同用戶使用不同版本帶來的兼容性問題。本節展示如何讓自己的程序可以在多個不同版本中兼容的思路，讀者可以借鑒。

疑難19 程序的兼容性體現在哪些方面

如果編寫自定義函數或者開發的插件發送給不確定用戶使用，且不能限制其使用指定的 Office 版本和Windows版本，那么就一定存在代碼兼容性問題。通常兼容性問題體現在哪些方面呢？

解決方案

操作系統不同、Office版本不同將帶來兼容性問題，不同用戶處理的對象不同，例如圖形對象個數不同、單元格區域大小不同等，在使用相同代碼處理不同對象時也一定會產生兼容性問題。本例從三方面進行分析。

操作方法

※ 操作系統對VBA的影響 ※

不同操作系統對VBA是有影響的。例如Windows 2000、XP和7中API函數有部分不同；由于桌面主題不同造成對所有窗口的寬度也不同，那么設計需要嚴格計算邊距與高度的窗體時就會產生偏差；部分程序的參數也不同，例如Windows 2000中DOS命令的“CD”命令不支持“CD/D”這個參數，而XP開始，以后的各種操作系統都支持；最后是Windows中一些默認程序也有變化。例如Windows 2000和XP都有“Outlook Express”，而Vista和Windows 7中刪除了該程序，所以操作系統的版本也對VBA調用郵件程序有著極大的影響。

如果需要在執行主體程序前判斷當前操作系統的版本，以當前常用的四大操作系統Windows 2000、XP、Vista和7為例，判斷操作系統的方式如下：

Sub 當前系統()
MsgBox WorksheetFunction.HLookup(Split(Application.OperatingSystem, " ")(UBound(Split(Application.OperatingSystem, " "))),
[{"5.0","5.01","6.0","6.01";"WIN 2000","WIN XP","VISTA","WIN7"}], 2, 0)End Sub

執行程序后，將在對話框中告知當前操作系統版本名稱。Windows 2000、XP、Vista和7的版本號分別對應5.0、5.01、6.0、6.01。

而對于Office版本不同帶來的兼容性問題則遠遠多于Windows版本帶來的兼容性問題。例如：

· 工作表函數不同：每次版本更新都會加入新的函數。當VBA中使用該函數后，程序在低版本中就無法運行。

· 單元格行列數不同：Excel 2007以前的版本有65536行×256列，而Excel 2007和2010有1048576行×16384列。調用單元格時會產生兼容性問題。

· VBA中的對象有增減：例如Excel 2007開始增加了圖標集、色階條件格式，VBA中也相應地增加對象和方法。那么調用這些新對象的代碼在2007以前的版本一定會出錯；而Excel 2003所具備的FileSearch屬性也從Excel 2007版開始刪除了，通常在新版中利用DIR方法來替代FileSearch進行文件搜索。

· 對部分內置方法進行修改、完善：例如Excel 2007開始對早期版本的排序Sort做了修改，新版的Sort和早期版本完全不兼容；對于定義名稱Name也有不同的處理方式，從Excel 2007開始可以對名稱添加備注，而早期版本無此屬性。

· 顏色不同：Excel 2003的單個工作簿可以使用的顏色數量是56種（索引顏色），而Excel 2007開始采用43億種（32位真彩色）。對顏色的處理，在 Excel 2003中通常采用ColorIndex，而鑒于ColorIndex本身的限制，它無法準確地體現Excel 2007的顏色值，所以為了體現兼容屬性，通常改用 Color。例如獲取單元格的顏色編碼，那么必須使用以下代碼才具有兼容性：

MsgBox ActiveCell.Interior.Color

除了以上版本差異帶來的兼容性問題外，不同用戶在同一版本中使用同一程序時也會有兼容性問題。因為不同用戶制表時會有不同習慣，標題行數不同，或者待匯總的工作表數量不同、路徑不同等，那么在開發工具時，為了體現兼容性，通常不使用硬編碼，而用動態引用方式，使代碼可以適應變化。

例如多表匯總，雖然工作表名稱是“Sheet1”、“Sheet2”、“Sheet3”，但仍然不使用“Sheet1”方式引用工作表，而是“Sheets(1)”，即通過索引號調用工作表，避免改名后程序出錯；而對于單元格的引用也不使用“[A1:c20]”這種硬編碼，而是“ActiveSheet.Usedrange”實現動態調用，適應數據的增減變化。對于文件路徑，也不能使用“d:\生產表”這種明文方式，而是調用對話框讓用戶自由選擇，從而大大提升程序的兼容性，保證在不同環境下都可以正確執行。

原理分析

VBA程序的使用對象不確定時，操作系統環境、Office環境和工作表對象不同都會產生兼容性問題。實際工作中避免這種問題的方法只有兩種：通過IF判斷當前環境，再根據環境執行不同的代碼；而對于不確定的文件路徑、工作表對象和區域地址等，通過動態引用來適應變化。

知識擴展

開發通用性的工具時，所有工作表名、單元格地址、圖形對象都不能使用硬編碼引用，而改用索引號或者 UsedRange 、CurrentRegion 這類具有自動適應區域變化的屬性，否則數據、路徑變化時程序無法通用，會增加維護成本。

疑難20 如何讓程序適應不確定對象

同一個程序在不同時候使用，其需要處理的對象是不同的。那么如何才能讓程序通用呢？即程序可以適應環境的變化。例如圖1-32所示的“一班”、“二班”、“三班”和“四班”的A1:D13區域存放各班級的成績表。要求將各班成績合并到“總表”中，并將工作簿另存為“已合并”，代碼如下：

Sub 合并所有工作表()
    '新建工作表，命名為“總表”
    With Sheets.Add(after:=Sheets(Sheets.Count)).Name = "總表"
    End With
    '合并4個班級的成績
    Sheets("一班").[a1:d13].Copy Sheets("總表").Cells(Rows.Count, 1).End
    (xlUp)
    Sheets("二班").[a2:d13].Copy Sheets("總表").Cells(Rows.Count, 1).End
    (xlUp).Offset(1, 0)
    Sheets("三班").[a2:d13].Copy Sheets("總表").Cells(Rows.Count, 1).End
    (xlUp).Offset(1, 0)
    Sheets("四班").[a2:d13].Copy Sheets("總表").Cells(Rows.Count, 1).End
    (xlUp).Offset(1, 0)
    ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\已合并.xlsm", xlOpenXML
    WorkbookMacroEnabled
End Sub

█ 圖1-32 成績表

以上程序可以完成需求，然而通用性極差，如何修正可以使其適應各種變化呢？

解決方案

對所有可能出現的錯誤、沖突都進行判斷，并將硬編碼的區域引用改為動態引用。包括創建工作表前判斷“總表”是否存在、讓用戶自行選擇標題行數、動態引用工作表、動態引用已用區域、另存文件前判斷當前表是否保存等。從所有可能產生影響的方面進行防范，避免環境變化時程序需要重新編寫。

操作方法

步驟1 對以上程序加入防錯語句，以及將靜態引用修改為動態引用，完整代碼如下：

Sub 合并所有工作表2() '通過6個方面完善程序，使其具有通用性
    Dim i As Integer, sht As Worksheet, Bt As Integer   '聲明變量
    '如果當前工作簿已經是“已合并”則退出程序
    If ActiveWorkbook.Name = "已合并.xlsm" Then Exit Sub
      On Error Resume Next
    '手法1：創建工作表中判斷是否具有同名工作表，避免出錯
    Set sht = Sheets("總表")    '將“總表”賦予變量
    If err <> 0 Then    '如果出錯（表示不存在“總表”），
      With Sheets.Add(after:=Sheets(Sheets.Count))  '新建工作表命名為“總表”
          .Name = "總表"
      End With
    Else
      Sheets("總表").Move Sheets(Sheets.Count)  '否則，將原有的“總表”移至最后
          Sheets("總表").Cells.Clear '手法2：如果已有總表，清除其原有數據，避免多重合并
      End If
      '手法3：讓用戶選擇標題行數，避免標題變化時，無法正確地引用正文區域
      Bt = Application.InputBox("標題行數為：", "請指定標題行數", 1, , , , , 1)
      If Bt > 0 And Bt < 10 Then '如果標題行數在1到10之間，則執行復制
          Sheets(1).Rows("1:" & Bt).Copy Sheets("總表").[a1]  '先復制標題到總表
      '手法4：利用Sheets.Count計算待匯總工作表數量，避免班級數量變化或者工作表名變化
      '時引用出錯
          For i = 1 To Sheets.Count - 1    '循環“總表”以外的所有工作表
          '手法5：如果非空表則復制，否則跳過
              If Not IsEmpty(Sheets(i).UsedRange) Then    '如果工作表為非空表
                '再復制正文數據到“總表”中。復制數據時忽略標題行
                Intersect(Sheets(i).UsedRange, Sheets(i).UsedRange.Offset
                (Bt, 0)).Copy _
                        Sheets("總表").Cells(Rows.Count, 1).End(xlUp).Offset
                        (1, 0)
              End If
          Next i
          '手法6：判斷當前工作簿是否保存過。如果沒有則讓用戶選擇保存路徑，否則另存到
          '當前文件同路徑下。避免文件未保存時出錯
If Len(Dir(ActiveWorkbook.Path, vbDirectory)) <2 Then  '如果工作簿沒有保存過
              With Application.FileDialog(msoFileDialogFolderPicker)
              '顯示打開文件夾的對話框
                If .Show = -1 Then  '如果選擇了文件夾，則將當前工作簿移到該文件中
                    ActiveWorkbook.SaveAs .SelectedItems(1) & IIf(Right$
                    (.SelectedItems(1), 1) = "\", "", "\") & "\已合并.xlsm",
                    xlOpenXMLWorkbookMacroEnabled
                End If
              End With
          Else    '否則當前工作簿另存到當前工作簿同路徑下，工作簿名為“已合并.xlsm”
              ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\已合并.xlsm",
              xlOpenXMLWorkbookMacroEnabled
          End If
      End If
End Sub

步驟2 將光標定位于代碼中任意位置，按【F5】鍵執行代碼，程序首先會彈出圖1-33所示的對話框讓用戶確認成績表的標題行數。然后將“總表”以外的工作表數據除標題行外全部復制到“總表”中，如圖1-34所示。如果活動工作簿已保存過，那么會將活動工作簿另存到同路徑下，且命名為“已合并.xlsm”；如果未保存過則會彈出對話框讓用戶選擇路徑。

█ 圖1-33 確定標題行數

█ 圖1-34 合并各班成績

原理分析

如果 VBA 代碼僅需當前使用一次即可，那么編程時不需要考慮通用性。反之則需要對各種不同的意外狀況進行防錯，避免數據變化時程序出錯。所謂意外狀況，通常包括文件夾路徑不存在、待創建的文件名已經存在、工作表數據行列數增減、引用的待計算區域為空白、待排序區域具有合并單元格等，在程序中應該對每個方面都加以判斷，并對可能產生的意外設置進行處理。通過本例，讀者可以大致了解將普通程序提升為通用程序的常規思路。

本例中DIR語句用于判斷工作簿是否保存，如果保存過，DIR方法從FullName中獲取的文件名的長度一定大于0。

知識擴展

※ 使用動態區域引用讓代碼具有更強的通用性 ※

常見的區域動態引用包括“UsedRange”（已用區域），“CurrentRegion”（當前區域），“Cells(Rows.Count, 1).End (xlUp)”（第一列最后一行非空行），“Selection”（當前選擇的區域）。在開發通用型程序時，特別是加載宏，盡量使用動態引用，而“Range("a1:b20")”或者“[c:d]”之類引用則兼容性極差，不能適應數據變化。對于工作表的引用，為了體現通用性，均采用索引號方式，例如“sheets(1)”代表第一個工作表，而不能使用第一個工作表的實際名稱。對于文件夾的動態引用，最好的方式是讓用戶從對話框中選擇，防止指定的路徑不存在。

疑難21 如何讓程序兼容Excel多版本

Excel目前最常用的3個版本是Excel 2003、2007和2010，如何讓自己的程序可以兼容多個版本呢?例如 Excel 新版本相對2003增加了行列數、增加了 Sort 對象強化排序、增加了去重復值功能、去除了FileSearch對象、對單元格顏色大大改進等，如何讓程序在Excel 2003、2007和2010中都可以順利執行呢？

解決方案

為了體現兼容性，通常采用3種思路：一是讓程序自動判斷Excel版本變化后的實際數據，例如行列數變化；二是寫兩段代碼，讓程序根據當前Excel版本調用對應的一段代碼；三是使用低版本的方式，因為通常低版本Excel無法使用高版本Excel的部分功能，但高版本可以調用低版本的代碼。

操作方法

步驟1 對于行列數不同引起的兼容性問題，可以利用動態引用的方式處理。Excel 2003及早期版本是65536行×256列，Excel 2007和2010是1048576行×16384列，那么在引用最后一個非空行時，通常采用[a65536].end(xlup)和[a1048576].end(xlup)。然而，兩種引用方式都不能在多個版本中兼容，正確的引用方式如下。

· cells(rows.Count,1)：第一列最后一個非空單元格。其中rows.Count用于計算總行數，可以隨Excel的版本變化而變化，相對于65536和1048576有更大的通用性。

· cells(1,columns.Count)：第一行最后一個非空單元格。其優點同上。

步驟2 對于排序，Excel 2003及早期版本使用Sort方法，而從Excel 2007開始增加了一個Sort對象。Sort對象比Sort方法功能更強大，但無法在Excel 2003及早期版本中使用。所以為了兼容性，可以如下方式處理：

Sub 排序()  '根據版本號執行不同代碼
  Dim rng As Range
  Set rng = ActiveSheet.UsedRange  '將當前表已用區域賦予變量rng
  '根據版本號執行對應的排序代碼，Excel 2007的版本號是12，Excel 2010的版本號是14
  If Application.Version * 1 < 12 Then GoTo line1 Else GoTo line2
line1:    '2007以前的版本使用
  rng.Offset(1, 0).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:
  =xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
  DataOption1:=xlSortNormal
  Exit Sub
line2:    'Excel 2007和2010專用
  With ActiveSheet.Sort  '利用排序對象進行排序
    .SortFields.Clear  '清除原有的SortFields對象，該對象存儲了排序狀態
    .SortFields.Add Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=
    xlAscending, DataOption:=xlSortNormal  '添加排序條件
    .SetRange rng  '設置排序的區域為rng
    .Orientation = xlTopToBottom  '排序方向為縱向
    .Apply    '根據當前應用的排序狀態對區域進行排序
  End With
End Sub

步驟3 對于查找重復值，Excel 2007和2010有專用的RemoveDuplicates方法實現，而Excel 2003及更早的版本只能通過高級篩選完成。那么為了通用性，同樣可以采取步驟2相同的思路。代碼如下：

Sub 消除重復值()   '根據版本號執行不同代碼
  Dim rng As Range
  Set rng = Range([a1], Cells(Rows.Count, 1).End(xlUp)) '將A列所有已用單元
  格賦值給變量rng
  If Application.Version * 1 < 12 Then GoTo line1 Else GoTo line2
  '根據版本號執行對應的排序代碼
line1: '2007以前的版本使用（雖然2007和2010也可以使用，但效率不如Remove Duplicates
方法）
  With rng
  '高級篩選不重復值，將它保存在最后一列
    .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1,
    Columns.Count), Unique:=True
    .Clear  '清除原有數據
    Cells(1, Columns.Count).CurrentRegion.Copy .Item(1)
    '將篩選后的不重復值復制到A列
    Cells(1, Columns.Count).CurrentRegion.Delete  '清除輔助區數據
  End With
  Exit Sub  '退出過程，避免執行Line2
line2:    'Excel 2007和2010專用
  Intersect(rng, rng.Offset(1, 0)).RemoveDuplicates Columns:=1, Header:
  =xlNo '取不重復值
End Sub

步驟4 Excel 2007以前的版本有FileSearch對象，可以方便地進行文件查找；Excel 2007及2010刪除了該功能，但可以利用DIR實現相同功能。本例通過DIR方法完成文件查找，替代FileSearch對象，且代碼在所有版本中通用：

Sub 查找文件()  'DIR替代FileSearch對象實現文件查找
    Dim 路徑 As String, 文件對象 As String, 文件名稱 As String
路徑 = "D:"  '搜索D盤文件
文件對象 = "*.*"  '搜索所有文件
文件名稱 = Dir(路徑& "\" &文件對象)  '獲取文件名
    Do  '開始循環，直到
    Range("A" & i + 1) =文件名稱  '將找到的文件名寫入A列單元格
    文件名稱= Dir  '查找其他文件
        i = i + 1
    Loop Until 文件名稱 = ""
End Sub

步驟5 Excel 2007開始對單元格的可用顏色從56種升級到43億種，在Excel 2003中可以使用ColorIndex來表示所有顏色值，但對于Excel 2007和2010則遠遠不足。如果利用ColorIndex獲取或者設置單元格背景，大部分情況下會產生顏色偏差。例如將A1:A5的背色復制到B1:B5，使用以下代碼在Excel中完全可行，但在新版中則部分正確、部分錯誤，如圖1-35所示。

Sub 復制顏色()
    For i = 1 To 5
      Cells(i, 2).Interior.ColorIndex = Cells(i, 1).Interior.ColorIndex
    Next
End Sub

█ 圖1-35 復制顏色

如果需要讓程序兼容多版本，那么可以將代碼中“ColorIndex”修改為“Color”。“Color”所能引用的顏色值不像“ColorIndex”那樣受限于56種，而且對每個版本都通用。

原理分析

微軟公司在升級Office時，總是采取向下兼容的方式，即早期版本無法使用新版本的部分功能，但新版本通常可以使用早期版本的所有功能，少數例外。所以為了讓程序通用于多個版本，可以一律采用早期版本的代碼來使程序提升兼容性。但為了新版用戶能使用新功能，則可以通過Application.Version判斷當前用戶的Excel版本，根據版本號執行對應的代碼。而對于有替代方法的，如Color替代ColorIndex和DIR替代FileSearch，則通過替代方式讓程序通用于多版本。

知識擴展

※ 識別Excel的版本號 ※

Application.Version用于判斷Excel程序的版本號，Excel 2002是10.0，Excel 2003是11.0，Excel 2007是12.0，而Excel 2010跳過了13.0，版本號為14.0。

也有一些對象或者屬性是Excel 2003之后的版本才提供的，在Excel 2003中不可使用，也不存在替代品，那么唯一做法是判斷版本號，小于12.0則在提示用戶后直接退出程序。

疑難22 如何讓程序兼容英文和中文系統

假設只有英文和簡體中文兩種需求，如何實現VBA程序在英文系統中執行時顯示英文，而在中文系統中執行則顯示中文?。

解決方案

利用API函數判斷當前計算機的語言系統，在需要顯示文字時，根據實際語言調用不同的語句。與疑難21中兼容Excel多版本時采用相同思路。

操作方法

步驟1 編寫一個判斷操作系統語言的自定義函數，其值為布爾值，代碼如下：

Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long Function language() As Boolean '開發一個語言函數，用于判斷當前操作系統是否為英文language = (GetSystemDefaultLCID = &H409)
End Function

步驟2 以創建名為“Summation”的工作表為例，如果在創建表時發現有同名工作表，那么需要提示用戶，該提示需要自動適應當前系統的語系。那么在彈出對話框前需要調用language函數：

Sub NewSheet() '創建新工作表，名為“Summation”
    Dim sht As Worksheet
    On Error Resume Next
    Set sht = Sheets("Summation") '將工作表“Summation”賦予變量
    If Err <> 0 Then '如果有錯誤（表示不存在“Summation”工作表）
      Sheets.Add after:=Sheets(Sheets.Count) '那么在最末處新建一個工作表
      Sheets(Sheets.Count).Name = "Summation" '改名為“Summation”
    Else '否則
      If language Then '如果系統是英文，則用英文提示用戶
          MsgBox "There has been one worksheet named “Summation”"
      Else '否則用中文提示（中英文系統由函數計算得來）
          MsgBox "已經有名為“Summation”的工作表"
      End If
    End If
End Sub

步驟3 光標置于代碼中任意位置并按【F5】鍵執行，如果當前工作簿已經存在“Summation”工作表，將會彈出對話框提示用戶。如果用戶操作系統是中文，那么對話框如圖1-36所示；如果用戶操作系統是英文，則對話框如圖1-37所示。

█ 圖1-36 中文提示

█ 圖1-37 英文提示

原理分析

※ 利用API函數識別簡體、繁體中文與英文 ※

“kernel32.dll”文件位于“系統盤符:\Windows\System32”下，它是一個動態鏈接庫文件，屬于內核級文件。它控制著系統的內存管理、數據的輸入/輸出操作和中斷處理，集成了很多系統相關的 API 函數，GetSystemDefaultLCID 屬于其中之一，用于判斷操作系統的語系。其中GetSystemDefaultLCID值為“&H404”時表示系統為繁體中文，值為“&H804”時表示系統為簡體中文，而值為“&H409”時表示系統為英文。

知識擴展

本例中演示的對話框為中英文切換，如果用戶窗體中的控件的“Caption”屬性也需要中英文切換的話，可以利用窗體的Activate事件對控件的“Caption”屬性進行控制。例如：

Me.CommandButton1.Caption = IIf(language, "OK", "確定")

疑難23 如何讓程序兼容簡體與繁體中文

如何實現VBA程序在簡體中文系統和繁體中文系統中打開時分別顯示簡體中文和繁體中文，而不是亂碼？

解決方案

利用API函數GetSystemDefaultLCID判斷當前操作系統的語言是簡體中文還是繁體中文，然后根據語系執行不同的語句。

操作方法

步驟1 編寫一個判斷操作系統語言的自定義函數，其值為布爾值，代碼如下：

Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As
Long
Function language() As Boolean '開發一個語言函數，用于判斷當前操作系統是否為繁
體中文
language = (GetSystemDefaultLCID = &H404)
End Function

步驟2 以在狀態欄顯示主機開機后到現在的使用時間為例，調用函數 language 判斷當前操作系統語言是簡體中文還是繁體中文，然后調用對應的代碼，在狀態欄顯示開機時間，代碼如下：

Private Declare Function GetTickCount Lib "kernel32" () As Long
Sub 狀態欄顯示電腦使用時間()    '自動適應簡繁體
  Application.OnTime Now + TimeValue("00:00:10"), "狀態欄顯示電腦使用時間"
  '每10秒鐘更新一次
  If language Then    '如果是繁體
      Application.StatusBar = "您的電腦已使用" & Round(GetTickCount / 1000
      / 60, 0) & "分鐘"
  Else  '否則
      Application.StatusBar = "您的電腦已使用" & Round(GetTickCount / 1000
      / 60, 0) & "分鐘"
  End If
End Sub

API函數的聲明語句必須置于模塊的頂部，所以以上兩段代碼中的兩句API聲明應排在所有過程前面。代碼見光盤文件夾“第1章”中的案例文件“疑難23.xlsm”。

步驟3 執行過程“狀態欄顯示電腦使用時間”，如果當前系統語言是簡體中文，那么狀態欄效果如圖1-38所示；如果當前系統語言是繁體中文，那么狀態欄效果如圖1-39所示。

█ 圖1-38 簡體顯示開機時間

█ 圖1-39 繁體顯示開機時間

原理分析

※ 讓代碼適應簡體與繁體系統 ※

API 函數 GetSystemDefaultLCID 獲取的系統語言編碼“&H404”代表繁體中文，“&H804”代表簡體中文。本例中通過判斷系統語系執行不同的代碼來實現簡繁兼容。不過根據筆者的經驗，在簡體系統中編寫繁體的代碼在繁體操作系統中打開會顯示亂碼，而在繁體系統中編寫的簡體代碼和繁體代碼都可以在簡體系統中正常執行。所以需要代碼簡繁通用時，可以在繁體操作系統中書寫代碼，其中簡體部分利用Office的簡繁轉換功能轉換為簡體即可。

知識擴展

如果需要讓窗體中各種控件顯示的字符也自動適應簡繁系統，那么可以在窗體的 Activate事件中調用language函數，讓顯示的文字可以適應操作系統的語系。