如何為 Excel 工作表函數建立 Visual Basic Automation 增益集

文章翻譯 文章翻譯
文章編號: 285337 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

結論

在 Microsoft Excel 2000 中,您無法從工作表的儲存格公式,直接呼叫元件物件模型 (Component Object Model,COM) 增益集中的函數。相反地,您必須為 COM 增益集函數建立 Visual Basic for Applications (VBA) 包裝函式,這樣才可以間接地呼叫函數。

在 Excel 2002 中整合的 COM 增益集 (稱為 Automation 增益集) 已於稍後經過增強,例如 COM 增益集函數現在不需要 VBA 包裝函式,就可直接從工作表公式直接呼叫。本文將告訴您,如何使用 Visual Basic 建立 Automation 增益集,其公開的函數可以從 Excel 2002 及更新版本的工作表公式進行呼叫。

其他相關資訊

建立範例 Automation 增益集

  1. 在 Visual Basic 中,開始新的增益集專案。
  2. 根據預設,會將名稱為 frmAddIn 的表單新增至專案中。為了便於本示範說明,可以從專案移除該表單。在 [專案總管] 中,用滑鼠右鍵按一下該表單,然後按一下快顯功能表上的 [移除 frmAddIn]
  3. 按一下 [專案] 功能表中的 [MyAddin 屬性]。將 [專案名稱] 變更為 AutomationAddin,然後按一下 [確定]
  4. 在 [專案總管] 中,選取 [連接] 設計工具。將其 Name 屬性變更成 XLFunctions。
  5. 在 [專案總管] 中,按兩下 [XLFunctions] 設計工具。在 [一般] 索引標籤上,對設計工具設定進行下列變更:
    • [應用程式] 清單,選取 [Microsoft Excel]
    • [應用程式版本] 清單,選取 [Microsoft Excel 10.0]
      注意:使用 Microsoft Office Excel 2003 時,請從 [應用程式版本] 清單選取 [Microsoft Excel 11.0]
    • [初始載入行為] 設定變更為 [視需要載入]
  6. 在 XLFunctions 設計工具仍然開啟的狀態下,選取 [檢視] 功能表的 [程式碼]。使用下面的程式碼來取代模組中的程式碼:
    Option Explicit
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    
    Dim oApp As Object  'The Excel Application object
    
    Private Sub AddinInstance_OnAddInsUpdate(custom() As Variant)
       Exit Sub
    End Sub
    
    Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
       Set oApp = Application
    End Sub
    
    Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
       Set oApp = Nothing
    End Sub
    
    Private Sub AddinInstance_OnStartupComplete(custom() As Variant)
       Exit Sub
    End Sub
    
    Public Function TickCount() As Long
       '----------------------------------------------------------------------
       '** A volatile function that is called each time the sheet is calculated.
       '   Call with =TICKCOUNT().
       '----------------------------------------------------------------------
       oApp.Volatile
       TickCount = GetTickCount
    End Function
    
    Public Function Add1(Num1 As Variant, Num2 As Variant) As Variant
       '----------------------------------------------------------------------
        '** A function with two required arguments.
        '   Can be called with formulas such as =Add1(1,3) or =Add1(A1,A2).
       '----------------------------------------------------------------------
        On Error Resume Next
        Add1 = "The sum of " & Num1 & " and " & Num2 & " is " & _
            CDbl(Num1) + CDbl(Num2)
        If Err <> 0 Then Add1 = CVErr(2036)  'xlErrNum = 2036
    End Function
    
    Public Function Add2(Num1 As Variant, Num2 As Variant, Optional Num3 As Variant) As Variant
       '----------------------------------------------------------------------
       '** A function with two required arguments and a third optional argument.
       '   Can be called with formulas such as =Add2(1,2), =Add2(A1,A2,A3).
       '----------------------------------------------------------------------
        Dim Sum As Double, sMsg As String
        On Error GoTo Handler
        Sum = CDbl(Num1) + CDbl(Num2)
        If IsMissing(Num3) Then
            sMsg = "The sum of " & Num1 & " and " & Num2 & " is "
        Else
            Sum = Sum + CDbl(Num3)
            sMsg = "The sum of " & Num1 & ", " & Num2 & " and " & Num3 & " is "
        End If
        Add2 = sMsg & Sum
        Exit Function
    Handler:
        Add2 = CVErr(2036)  'xlErrNum = 2036
    End Function
    
    Public Function Add3(ParamArray Nums()) As Variant
       '----------------------------------------------------------------------
       '** Demonstrates a function with a variable number of arguments.
       '   Can be called with formulas like =Add3(1), =Add3(1,2,3,4),
       '   or =Add3(A1,A2).
       '----------------------------------------------------------------------
        Dim Sum As Double, i As Integer
        On Error GoTo Handler
        For i = 0 To UBound(Nums)
            Sum = Sum + CDbl(Nums(i))
        Next
        Add3 = "The sum is " & Sum
        Exit Function
    Handler:
        Add3 = CVErr(2036)  'xlErrNum = 2036
    End Function
    
    Public Function ReturnArray(nRows As Long, nCols As Long) As Variant
       '----------------------------------------------------------------------
       '** Demonstrates how to return an array of values (for use in Excel
       '   "array formulas").
       '   Can be called with a formula such as =ReturnArray(1,3).
       '----------------------------------------------------------------------
        On Error GoTo Handler
        ReDim a(0 To nRows, 0 To nCols) As Variant
        Dim r As Long, c As Long
        For r = 0 To nRows - 1
            For c = 0 To nCols - 1
                a(r, c) = "r" & r + 1 & "c" & c + 1
            Next c
        Next r
        ReturnArray = a
        Exit Function
    Handler:
        ReturnArray = CVErr(2015)  'xlErrValue = 2015
    End Function
    
    Public Function GetArray(Nums As Variant) As Variant
       '----------------------------------------------------------------------
       '** Demonstrates how to use an array(or range of multiple cells) as
       '   a function argument.
       '   Can be called with formulas such as =GetArray(A1:B5), GetArray(A1),
       '   or GetArray({1,2,3;4,5,6}).
       '----------------------------------------------------------------------
        Dim Sum As Double, v As Variant
        On Error GoTo Handler
        If IsArray(Nums) Then
            For Each v In Nums
                Sum = Sum + CDbl(v)
            Next
        Else
            Sum = CDbl(Nums)
        End If
        GetArray = "The sum is " & Sum
        Exit Function
    Handler:
        GetArray = CVErr(2036)  'xlErrNum = 2036
    End Function
    
    					
  7. 將此增益集建置為 AutomationAddin.dll。

在 Microsoft Excel 2002 或 Microsoft Office Excel 2003 中使用範例 Automation 增益集

  1. 如果您已經在 Visual Basic 增益集專案的 [應用程式版本] 清單中選取 [Microsoft Excel 10.0],就啟動 Microsoft Excel 2002。
    注意:如果您已經在 Visual Basic 增益集專案的 [應用程式版本] 清單中選取 [Microsoft Excel 11.0],就啟動 Microsoft Office Excel 2003。
  2. [工具] 功能表上,按一下 [增益集] 以顯示 [增益集管理員] 對話方塊。按一下 [Automation],選取清單中的 [AutomationAddin.XLFunctions],然後按一下 [確定]。選取 [AutomationAddin.XLFunctions],然後按一下 [確定] 關閉 [增益集管理員] 對話方塊。
  3. 在儲存格 A1 中,輸入下列公式:
    =TickCount()
    此公式會傳回一個很大的數字,該數字代表自系統啟動所經過的毫秒數。
  4. 在新活頁簿的儲存格 B1 與 B2 中,分別輸入 2 與 5 的數值。
  5. 在儲存格 B3 中,輸入下列公式:
    =Add1(B1, B2)
    然後按 ENTER。該公式會傳回 "The sum of 2 and 5 is 7"。
  6. 在儲存格 B4 中,輸入下列公式:
    =Add2(B1, B2)
    然後按 ENTER。該公式會傳回 "The sum of 2 and 5 is 7"。修改此公式以使用第三個選擇性引數:
    =Add2(B1, B2, 10)
    該公式會傳回 "The sum of 2, 5, and 10 is 17"。
  7. 在儲存格 B5 中,輸入下列公式:
    =Add3(1,2,3,4,5,6)
    然後按 ENTER。此公式會傳回 "The sum is 21"。因為 Add3 函數的參數是宣告為 ParamArray,所以您可以針對此函數使用各種數目的引數。

    注意 Excel 將單一工作表函數限制為 29 個引數。
  8. 在儲存格 B6 中,輸入下列公式:
    =Add1("x","y")
    然後按 ENTER。公式會傳回 #NUM!(xlErrNum),因為使用 CDbl 函數的類型轉換無法將字串 "x" 與 "y" 轉換為 double 類型。當函數發生執行階段錯誤時,預設的傳回值是 #VALUE!。如果您希望傳回不同的錯誤,請使用錯誤處理,這樣就可以傳回任何 Excel 內建錯誤值 (xlErrDiv0xlErrNAxlErrNamexlErrNullxlErrNumxlErrRefxlErrValue)。
  9. 選取儲存格 E1:G5。輸入下列公式:
    =ReturnArray(5,3)
    然後按 CTRL+SHIFT+ENTER,以陣列公式的形式輸入公式。函數會傳回 5 x 3 陣列的唯一值。
  10. 在儲存格 I1:J3 中輸入任何數字。在儲存格 I4 中,輸入下列公式:
    =GetArray(I1:J3)
    該公式將會傳回類似於 "The sum is n" 的結果 (其中 n 是 I1:J3 中數字的總和)。在 I5 中,輸入下列公式:
    =GetArray({1,2,3,4})
    然後按 ENTER。此公式會傳回 "The sum is 10"。

函數繫結

Automation 增益集中函數的繫結,在函數繫結優先順序中列於最後。如果在 Automation 增益集的函數與 Excel 內建函數的名稱相同,Excel 內建函數將具有較高的優先順序。在活頁簿中的 VBA 函數與一般增益集 (.xla) 的優先順序,也高於 Automation 增益集函數。當您建立自己的函數以供在 Excel 中使用時,建議您不要將函數名稱命名成 Excel 內建函數已經使用過的名稱。

如果要特別呼叫 Automation 增益集的函數,您可以使用如 ServerName.ClassName.FunctionName(...) 的語法,在公式中向下切入至函數。例如,如果要呼叫範例中的 Add1 函數,您可以使用下列公式:
=AutomationAddin.XLFunctions.Add1(1,2)
您只能呼叫 Automation 增益集最上層的方法與屬性;您不能呼叫增益集物件模型下層 (即不在最上層) 的函數。

變動函數

變動函數是只要工作表中的任何儲存格有變更時,就會重新計算的函數,不論變更的儲存格是否與函數相依。RAND() 函數便是 Excel 內部的變動函數範例。在您按 F9 時,變動函數也會重新計算。

如果要在 Automation 增益集中將函數變成可變動的,請呼叫 Excel Application 物件的 Volatile 方法。如上方所示範的範例程式碼,在該增益集的 OnConnection 事件期間,可能會擷取 Excel Application 物件的參照。在範例 Automation 增益集中的 TickCount 函數就是可變動的。請注意,如果您對工作表的任何儲存格做變更或是按 F9,包含下列公式的儲存格:
=TickCount()
將會重新計算。

Automation 增益集與增益集管理員

在增益集管理員中,會使用 Automation 增益集的 HKEY_CLASSES_ROOT\<ProgID> 登錄機碼預設值,做為增益集的名稱。請注意,在 Visual Basic 使用者介面 (UI) 中沒有屬性可設定此機碼的預設值;不過,您可以在登錄編輯程式中或在增益集的安裝期間,手動修改此機碼。

在增益集管理員中對於 Automation 增益集的描述永遠都是「增益集的 ProgID」;並沒有辦法變更此行為。

Automation 增益集與函數精靈

每個 Automation 增益集在 Excel 函數精靈中都有自己的類別 (Category)。類別名稱是增益集的 ProgID;您無法為 Automation 增益集函數指定不同的類別名稱。此外,也無法在函數精靈中指定 Automation 增益集函數的函數描述、引數描述或是說明。

?考

如需詳細資訊,請按一下下面的文件編號,檢視「Microsoft 知識庫」中的文件:
256624 HOWTO: Use a COM Add-In Function as an Excel Worksheet Function
如需詳細資訊,請參閱下列 Microsoft 網站:
http://support.microsoft.com/ofd


(c) Microsoft Corporation 2001, All Rights Reserved.本文內容由 Lori B. Turner, Microsoft Corporation 提供。

屬性

文章編號: 285337 - 上次校閱: 2006年8月14日 - 版次: 5.2
這篇文章中的資訊適用於:
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
關鍵字:?
kbhowto KB285337
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com