Functions in the Stock Web Service DLL
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The folder \SAMPLES\STOCK\ contains a Microsoft Office Excel 2007 add-in XLAM (stock.xlam), and a Microsoft Visual Studio 2005 project (stock.vcproj), which builds to an XLL (stock.xll). These demonstrate the use of the Framework and also the variety of possible projects enabled by the Microsoft Office Excel 2007 XLL Software Development Kit. The XLL provides an interface to retrieve stock data from a Web service, or optionally from static local data tables. The XLL contains all the user-defined functions (UDFs) that are used by the XLAM add-in. The XLL can also be used as a stand-alone add-in without the XLAM.
.gif "Note") Note |
|---|
Stock.xlam can only be opened with Excel 2007. However, stock.xll can be loaded in earlier versions of Excel too. |
STOCK.xlam
When instructed to do so by the user, the XLAM can create a workbook that calls UDFs in the XLL to obtain quotes from the Stock Service and to calculate metrics for two imaginary stocks, Stock1 and Stock2. The workbook also displays graphs of the resulting data series and rates the stocks’ performances. The XLAM supports creating both an Excel 2007 workbook, which takes advantage of the formatting capabilities in Excel 2007, and an Excel 97/2003 workbook, which can be saved directly in a legacy file format but which lacks some 2007 workbook format features. Once created, the workbook no longer requires the XLAM to be present: the XLL is all that is needed to retrieve the data. The XLAM is basically a design-time tool, and the XLL provides the run-time functionality, although the XLAM provides an interface that enables the easy switching of the stock data source between online and offline.
When Excel 2007 loads the XLAM, it installs an extra item on the Data tab of the ribbon, through which the user can access Microsoft Visual Basic for Applications (VBA) commands that control the generation of a workbook and the control of the Stock Service. You can view the XLAM’s VBA code, which makes calls into the XLL, by clicking the Visual Basic editor on the Developer tab. The added menu items are encoded within the XLAM document using XML. The XML code instructs Excel to call the VBA code when the user clicks it. You can view and edit it by opening the XLAM document in the Custom UI Editor Tool. This tool is downloadable from OpenXMLDeveloper.org.
(For more information about customizing the Office Fluent Ribbon in Excel 2007, see Customizing the 2007 Office Fluent Ribbon for Developers).
Stock Web Service
The Web service is set to poll http://localhost:1618/ and assumes the service lives at http://localhost:1618/Service.asmx. To change these assumptions, you can either edit the files in the SAMPLES\STOCK\StockService sub directory, or open the project in Visual Studio and add a Web reference pointing to the desired address. The name of the Web service must be StockService.h unless the include files and makefile have been changed.
You do not have to have the Web service running. The default behavior is the remote polling of data from the Web service, but this can be changed via one of the XLAM commands on the Data tab of the Office Fluent Ribbon, or through a Visual Basic call into the XLL (as seen in the VBA code of the XLAM). Disabling the Web service causes data to be polled from local data: the numbers in StockServiceProxy.cpp.
STOCK.xll Exports
XLL interface functions (called by the Excel Add-in Manager):
Worksheet functions:
Accessor functions (called by the XLAM’s VBA code):
If the XLL is loaded by versions of Excel earlier than Microsoft Office Excel 2007, it registers versions of the worksheet functions that do not use Excel 2007 data types.
The FuncStock and GetStockMetric functions are registered as volatile. In Excel 2007, all three worksheet functions are coded and registered as thread safe, enabling Excel to call them concurrently on different threads depending on its configuration. You can configure the number of threads that Excel uses to recalculate in the Options dialog box on the Stock menu on the Data tab of the Office Fluent Ribbon, or through the configuration dialog boxes in Excel.