How To Use Custom Functions with the Spreadsheet Component (248822)


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Office Spreadsheet Component 9.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Office 2003 Web Components
This article was previously published under Q248822

SUMMARY

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.

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 select Microsoft Office Web Components 9.0.

    Note In Microsoft Office 2003, click Components on the Project menu, and then select Microsoft Office Web Components 11.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
    
ErrHandler:
    CustomTrend = "#VALUE!"
    
End Function

Private Sub Class_Initialize()
    Set oExcel = CreateObject("Excel.Application")
End Sub

Private Sub Class_Terminate()
    oExcel.Quit
    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 MSOWC.dll is missing dependency information, click OK.

    Note In Microsoft Office 2003, click OK when you receive a message that OWC11.dll is missing dependency information.
  6. In the list of included files, deselect MSOWC.dll, and then click Next.
    Note In Microsoft Office 2003, in the list of included files, deselect OWC11.dll, 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:
    <HTML>
<HEAD>

<! --- 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 --------------------------------------->

</HEAD>

<BODY>

<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("A1:D13").AutoFitColumns
      .Range("D2:D5").NumberFormat = "0.00"
   End With
End Function

</SCRIPT>

</BODY>
</HTML>
    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.

    Note In Microsoft Office 2003, you must replace the clsid tag for the Spreadsheet1 object with 0002E559-0000-0000-C000-000000000046.

  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:

    =GetTicks()

    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



REFERENCES

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

Also, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k/html/webfunc.asp

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

Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbAutomation kbhowto kbOfficeWebSpread KB248822 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.