Use the Analysis ToolPak function add-in

The Spreadsheet component supports an additional set of functions that provide the same functionality as the functions in the Analysis ToolPak add-in in Microsoft Excel. To use the Analysis ToolPak functions, you must add two tags to the Web page that contains the Spreadsheet component to connect the Analysis ToolPak add-in.

1. Open the Web page file that contains a Spreadsheet component in a text editor or Web page editor.
2. Add the following tags:

   <OBJECT id=atp
      classid="CLSID:3F98D457-551B-48C5-BDE8-7FDECCD5AFA5">
   </OBJECT>
    
   <SCRIPT language="VBScript">
      Spreadsheet1.AddIn(atp)
   </SCRIPT>

   Where Spreadsheet1 is the value of the ID attribute for the OBJECT tag for the Spreadsheet component on the page.
3. Save these changes to the Web page.

Once you make these changes, you can use the Analysis ToolPak functions in formulas in your Spreadsheet component. For example, the following formula that uses the ISEVEN function to test whether a numeric value is an even number will return FALSE:

=ISEVEN(7)

Notes

• You can also instantiate the Analysis ToolPak function add-in and add formulas entirely from script as shown in the following example.

     Dim Spreadsheet1
     Dim atp
  
     ' Instantiate a Spreadsheet component object.
     Set Spreadsheet1 = CreateObject("OWC11.Spreadsheet")
  
     ' Instantiate the Analysis ToolPak function add-in.
     Set atp = CreateObject("OWCATP.OWCATP")
  
     ' Call the AddIn method to connect the add-in to the
     ' spreadsheet.
     Spreadsheet1.AddIn(atp)
  
     ' Add a formula to cell A1.
     Spreadsheet1.Range("A1").Formula = "=ISEVEN(7)"

• Using the XMLData or XMLUrl methods in script to load new values into the spreadsheet will disconnect the Analysis ToolPak function add-in. To address this, call the AddIn method again to reconnect the add-in, and then call the CalculateFull method to recalculate the formulas in the spreadsheet.

     ' Assign new XmlData to the spreadsheet.
     Spreadsheet1.XmlData ="XMLDataString"
  
     ' Reconnect the function add-in
     Spreadsheet1.AddIn(atp)
  
     ' Recalculate all formulas in the spreadsheet.
     Spreadsheet1.CalculateFull

  Where XMLDataString is a string of XML data in the XML Spreadsheet format, such as the output from saving as XML Spreadsheet (.xml) from Microsoft Office Excel 2003.