Microsoft KB Archive/248822

From BetaArchive Wiki

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

  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:

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

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



Additional query words: owc formula excel2007 excel2k7 excel12 xl2007 xl2k7 xl12 excel2003 excel2k3 excel11 xl2003 xl2k3 xl11 excel2002 excel2k2 excel10 xl2002 xl2k2 xl10 excel2000 excel2k excel9 xl2000 xl2k xl9 XL2007

Keywords: kbhowto kbautomation kbofficewebspread kbexpertiseinter KB248822