Microsoft KB Archive/248822

= How to use custom functions with the Spreadsheet component =

Article ID: 248822

Article Last Modified on 5/31/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Office Web Components
 * Microsoft Office Web Components
 * Microsoft Office Spreadsheet Component 9.0
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



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
 Start Microsoft Visual Basic and create a New ActiveX DLL project. Name the project OWCAddin . Name the class MyFunctions . 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</li> For Microsoft Office 2000, click Microsoft Office Web Components 9.0.</li></ul> </li>  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 </li> On the File menu, select Make OWCAddin.dll and build the DLL.</li> Save the project as OWCAddin.vbp.</li></ol>

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:
 * 6) * Owc12.dll
 * 7) * Owc11.dll
 * 8) * Owc10.dll
 * 9) * Msowc.dll
 * 10) In the list of included files, click to clear the check box of the file from step 5, and then click Next.
 * 11) Click Next to accept the file sources.
 * 12) For safety settings, select Yes for both Safe for Scripting and Safe for Initialization, and then click Next.
 * 13) Click Finish to build the CAB file.

Create an HTML Page with a Spreadsheet Component that Uses the Custom Functions in the COM Object
<ol>  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. </li> Save the text file as CustomFunction.htm.</li> Double-click CustomFunction.htm in Windows Explorer to view it in your browser.</li> Enter the following formulas in cells D2:D5:

<pre class="fixed_text">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:

<pre class="fixed_text">D2:    146171.52 D3:    147189.70 D4:    148207.88 D5:    149226.06

</li> In cell F1, enter the formula:

<pre class="fixed_text">=GetTicks

The formula returns a value representing the number of milliseconds that have elapsed since your system was started.</li></ol>

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

<div class="references_section">