How to use custom functions with the Spreadsheet component

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.

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

   <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.
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:

For more details on working with the Office 2000 Spreadsheet component, please see the following article in the Microsoft Knowledge Base: For information on deploying your solutions that use the Office 2000 Web components, please see the following articles in the Microsoft Knowledge Base: