小朱® 的技術隨手寫

VBA 和 Visual Studio Tools for Office (VSTO) 的關係，有點像是瑜亮情節一樣，VBA 已經在 Office 上生存了超過十年 (1997-2010)，而在 Office 2003 時，剛好適逢 .NET Framework 大展長才的時刻，微軟當然會想要將 .NET 平台延伸到更大的領域，而自家的 Office 產品沒有理由不支援它，因此微軟特別設計了 Visual Studio Tools for Office (最早的版本是 VSTO 1.0)，並且在 Visual Studio 2005 時包裝在一起發表，而 Office 2007 發表時，VSTO 也升級到 SE (second edition)，到了 2008 年時，VSTO 修改為支援 Ribbon 的應用程式開發，Visual Studio 2010 則對 Office 2007 與 Office 2010 的支援度更高。然而長久以來依靠 Office 生存的 VBA，微軟仍然持續給予支援的承諾，讓數以萬計的 VBA application 在 Office 2010 仍然可以使用。所以 VBA 和 VSTO (managed code) 的合作也已經是開發人員所關注的了。

VBA 是一個 COM-based 的語言，它所依賴的 Office 平台的核心也是一個 COM-based 的平台，VSTO 將 .NET 與 COM 間的轉換巧妙的包起來了，但如果不用 VSTO 的話，開發人員就要自己處理 COM Interoperability 的工作，但還好的是這個工作並不難，本文就是這樣的一個例子。請依下列步驟來建立

1. 建立一個類別庫的專案，筆者用的是 ExcelFunction，C# 的類別庫：

 

2. 修改預設的Class1.cs，更名為Example，並且加入下列的程式碼：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;

namespace ExcelFunctions
{
    [ComVisible(true)]
    public class Example
    {
        public double AddCS(double A, double B)
        {
            return A + B;
        }
    }
}

 

其中最重要的是要被 VBA 存取的類別物件，必須要加上 [ComVisible(true)]，否則 VBA 會看不到這個物件。

3. 設定專案屬性，第一個部份是組件資訊，要勾選 “讓組件成為 COM-Visible” 。

 

再來是在建置的設定，要將 “註冊 COM Interop” 也勾選：

 

設定完成後即可建置，Visual Studio 會幫你完成註冊 COM Interop 型別程式庫的工作 (但若要部署出去時，這個工作要交由安裝程式做，可參考 .NET Framework SDK 的 regasm.exe 的工具說明)。

4. 開啟 Excel，並由開發人員頁籤進入 VBA 編輯器，接著用工具＼設定引用項目 (Add Reference)，可以在清單中找到剛由 Visual Studio 註冊的 Managed code 組件：

 

5. 在 VBA 編輯器的 ThisWorkbook 類別加入下列的程式碼：

Private Sub Workbook_Open()

    Dim managedObject As New ExcelFunctions.Example
    MsgBox managedObject.AddCS(1.1, 2.2)

End Sub

 

其中的 ExcelFunctions.Example 就是 Managed Code 類別庫中的類別物件，這支程式會呼叫 Example.AddCS() 方法，並傳入 1.1 與 2.2 兩個參數。

撰寫完成，請將它儲存為 xlsm (可執行巨集的 Excel 活頁簿檔案)

6. 儲存完後將 Excel 關閉，再打開剛才存好的 XLSM 檔案，你會看到執行 Workbook_Open 事件常式的結果 (若巨集未啟用，請將它啟用)：

 

由上面的例子顯示，只要適當的修改一下 managed code，它是可以直接被 VBA 取用的。

PS: 本範例基本上應可適用於 Office 97-2010，但筆者只在 Office 2003, 2007 以及 2010 上測試過。