Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to use custom functions with the Spreadsheet component

View products that this article applies to.

This article was previously published under Q248822

↑ Back to the top


You can create your own custom functions in a COM object (or ActiveX DLL) and expose those functions to the Microsoft Office Spreadsheet Component for use in cell formulas. This article describes how you can create, deploy, and use a COM object with the Spreadsheet component.

↑ Back to the top

More information

The sample COM object discussed in this article exposes two public functions: GetTicks() and CustomTrend().
  • GetTicks

    This function returns a value that represents the number of milliseconds that have elapsed since the system was started. The value that it returns is obtained from a call to the GetTickCounts API function.
  • CustomTrend

    The Spreadsheet component does not support the use of array formulas in cells. Therefore, it does not support any Excel worksheet functions that return an array of values. Commonly used Excel worksheet functions that return an array of values and that are unsupported in the Spreadsheet component are LINEST, LOGEST, TREND and TRANSPOSE.

    The CustomTrend function demonstrates how you can automate Excel to call one of these functions that the Spreadsheet component does not support: the TREND function. With Automation, the Excel TREND function returns an array of values to the COM object; CustomTrend then returns an element of that array as determined by one of the function's arguments. Because a COM object function cannot return an array of values to the Spreadsheet component, you can call the CustomTrend function as many times as are needed to retrieve all the elements from the resulting array.

    Note Because the sample COM object automates Microsoft Excel, clients that use this sample COM object must have Microsoft Excel installed.

Create the COM Object

  1. Start Microsoft Visual Basic and create a New ActiveX DLL project.
  2. Name the project OWCAddin.
  3. Name the class MyFunctions.
  4. On the Project menu, click Components, and then click to select one of the following check boxes:
    • For 2007 Microsoft Office, click Microsoft Office Web Components 12.0
    • For Microsoft Office 2003, click Microsoft Office Web Components 11.0
    • For Microsoft Office XP, click Microsoft Office Web Components 10.0
    • For Microsoft Office 2000, click Microsoft Office Web Components 9.0.
  5. Add the following to the code module of the MyFunctions class:
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    Dim oExcel As Object
    Public Function GetTicks() As Long
        GetTicks = GetTickCount()
    End Function
    Private Function GetValues(xRange As IXRangeEnum) As Variant()
        Dim nCols As Long
        Dim nRows As Long
        Dim objRange As Object
        ' QI for IDispatch interface
        Set objRange = xRange
        ' Get unsigned longs and assign it to signed longs
        ' This is not always a good idea. You can use it here
        ' because the number of rows or columns cannot be greater
        ' than maximum value of a signed long
        nCols = objRange.ColCount
        nRows = objRange.RowCount
        ' Get values in an array of variants
        ReDim vVals((nRows * nCols) - 1) As Variant
        objRange.Next nRows * nCols, vVals(0), vbNull
        ' Return the array
        GetValues = vVals
    End Function
    Public Function CustomTrend(ByVal KnownY As IXRangeEnum, ByVal KnownX As IXRangeEnum, _
        ByVal NewX As IXRangeEnum, ByVal Idx As Variant) As Variant
        Dim XVals() As Variant, YVals() As Variant
        Dim NewXVals() As Variant, NewYVals() As Variant
        On Error GoTo ErrHandler
        'Get the values of Range in an array
        YVals = GetValues(KnownY)
        XVals = GetValues(KnownX)
        NewXVals = GetValues(NewX)
        'Now automate Excel to get an array of new Y Values using the TREND function
        NewYVals = oExcel.WorksheetFunction.Trend(YVals, XVals, NewXVals, True)
        'Return the requested index (Idx)
        CustomTrend = NewYVals(Idx)
        Exit Function
        CustomTrend = "#VALUE!"
    End Function
    Private Sub Class_Initialize()
        Set oExcel = CreateObject("Excel.Application")
    End Sub
    Private Sub Class_Terminate()
        Set oExcel = Nothing
    End Sub
  6. On the File menu, select Make OWCAddin.dll and build the DLL.
  7. Save the project as OWCAddin.vbp.

Package the COM Object

  1. Start the Package and Deployment Wizard.
  2. Select OWCAddIn.vbp and click Package.
  3. Select Internet Package for the Package Type and click Next.
  4. Select a Package folder and click Next.
  5. When you receive a message that a .dll file is missing dependency information, click OK. The file name of the .dll file that is missing dependency information is one of the following:
    • Owc12.dll
    • Owc11.dll
    • Owc10.dll
    • Msowc.dll
  6. In the list of included files, click to clear the check box of the file from step 5, and then click Next.
  7. Click Next to accept the file sources.
  8. For safety settings, select Yes for both Safe for Scripting and Safe for Initialization, and then click Next.
  9. Click Finish to build the CAB file.

Create an HTML Page with a Spreadsheet Component that Uses the Custom Functions in the COM Object

  1. Using Notepad, create a new text file that contains the following:
    <! --- COMMENT BEGIN ------------------------------------->
    <! --- Modify the codebase and clsid items below --------->
    <OBJECT classid = clsid:FFB16550-E40D-11D3-BB97-00C04FAEB609 
    codebase="http://MyServer/OWCAddin.CAB" id=OWCAddin></OBJECT>
    <! --- COMMENT END --------------------------------------->
    <OBJECT classid=clsid:0002E510-0000-0000-C000-000000000046 height="50%" 
    id=Spreadsheet1 width="80%"></OBJECT>
    <SCRIPT Language=VBScript>
    Function Window_OnLoad()
       'Reference the COM object so that its functions can be called from
       'formulas in cells on the Spreadsheet
       Spreadsheet1.AddIn OWCAddin.Object
       'Populate the Spreadsheet with data
       With SpreadSheet1
          .Range("A1:D1").Value = Array("Known X-Values", "Known Y-Values", _
                                        "New X-Values", "New Y-Values")
          .Range("A2:A13").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
          .Range("B2:B13").Value = Array (133890, 135000, 135790, 137300, _
                                          138130, 139100, 139900, 141120, _
                                          141890, 143230, 144000, 145290)
          .Range("C2:C5").Value = Array(13, 14, 15, 16)
          .Range("D2:D5").NumberFormat = "0.00"
       End With
    End Function
    Note You need to modify the <OBJECT> tag for the OWCAddin object so that it contains the correct values for clsid and codebase. You can determine the class id for the COM object by examining the registry key HKEY_CLASSES_ROOT\OWCAddin.MyFunctions\Clsid in the Registry Editor (Regedit.exe). The codebase tag should contain the location of the CAB file you created using the Package and Deployment Wizard.
  2. Save the text file as CustomFunction.htm.
  3. Double-click CustomFunction.htm in Windows Explorer to view it in your browser.
  4. Enter the following formulas in cells D2:D5:
    D2:     =CustomTrend(B2:B13, A2:A13, C2:C5, 1)
    D3:     =CustomTrend(B2:B13, A2:A13, C2:C5, 2)
    D4:     =CustomTrend(B2:B13, A2:A13, C2:C5, 3)
    D5:     =CustomTrend(B2:B13, A2:A13, C2:C5, 4)
    The formulas return:
    D2:     146171.52
    D3:     147189.70
    D4:     148207.88
    D5:     149226.06
  5. In cell F1, enter the formula:
    The formula returns a value representing the number of milliseconds that have elapsed since your system was started.

Note on Using Range Arguments for Custom Functions

The Spreadsheet Component passes Range arguments to custom functions using the IXRangeEnum interface. IXRangeEnum is listed as a hidden member in the Office Web Components Type Library (MSOWC.dll) and, therefore, is undocumented in the Help for the Office Web Components object model. IXRangeEnum provides methods and properties that allow you to retrieve the values from the Range passed into a custom function. The GetValues function in the COM object illustrates how you can build an array from the values in a range using IXRangeEnum.

For more details on using IXRangeEnum, see Chapter 11, "Building Solutions with the Office Web Components" in the following book:
"Programming Microsoft Office 2000 Web Components"
by Dave Stearns - ISBN 0-7356-0794-X

↑ Back to the top


For more details on working with the Office 2000 Spreadsheet component, please see the following article in the Microsoft Knowledge Base:
216578� XL2000: Calculation differences with the Office spreadsheet
For information on deploying your solutions that use the Office 2000 Web components, please see the following articles in the Microsoft Knowledge Base:
249843� PRB: VB Package and Deployment Wizard includes Office OLB files
243006� OFF2000: Licensing the Office 2000 Web components and Office server extensions

↑ Back to the top

Keywords: kbexpertiseinter, kbautomation, kbhowto, kbofficewebspread, KB248822

↑ Back to the top

Article Info
Article ID : 248822
Revision : 10
Created on : 5/31/2007
Published on : 5/31/2007
Exists online : False
Views : 268