Demonstrates how to create a meta-function from the relationship between two cells and use standard numerical methods to integrate the function

Overview

This sample add-in contains a dialog which allows the user to integrate a mathematical function f(x).

A function can be implied by the relationship between two cells: we treat the contents of the input cell as x and the value in the output cell as f(x).

We can then use standard numerical methods to integrate the function between two x values.

Features

The sample displays the following features:

• Displays a dialog to let the user select cells for: x, f(x) and the lower and upper bounds of integration, and a cell in which to put the result. The dialog also lets the user select an integration algorithm.

• The implied function f(x) is calculated by (i) putting the value of x into the input cell, (ii) recalculating the worksheet and (iii) reading the value of the output cell. (This mechanism is referred to as the meta-function.)

• The integration of the meta-function is performed by standard methods from 'Numerical Recipes in C'. The meta-function has the correct signature to be used by NR methods: double func(double).

• As a time-saving feature, if the active cell is a cell that has been used in a previous integration operation, the dialog automatically fills in all the fields as they were filled in on the last occasion. (In addition, this information is saved with the worksheet when it is closed.)

Implementing the meta-function

The CMetaFunction class is used to represent the meta-function. The following points are important to the implementation.

1. The class is initialized with the addresses of the input and output cells:

   CopyC++

   CMetaFunction::CMetaFunction(const CXlOper& xloInput, const CXlOper& xloOutput)
     : m_xloInput(xloInput),
       m_xloOutput(xloOutput)
   {
       // Register self as global instance
       mc_instance = this;
   }

2. The class implements a call to the meta-function using CXlOper::SetValue(), CXlMacros::CalculateNow() and CXlOper::Coerce(), as follows:

   CopyC++

   double Func_(double x)
   {
       ...
   
       // Set x into m_xloInput 
       if (!m_xloInput.SetValue(x))
       {
           strErr.Format("Unable to set the value of X in cell %s",
               (LPCSTR)m_xloInput.GetRef().ToString(TRUE));
           throw CMetaFunctionException(strErr);
       }
   
       // Recalculate 
       CXlMacros::CalculateNow();
   
       // Read y (as a number) from m_xloOutput
       CXlOper xloValue;
       if (xloValue.Coerce(m_xloOutput) != 0)
       {
           strErr.Format("Calculation failed for value %f", x);
           throw CMetaFunctionException(strErr);
       }
       if (xloValue.ChangeType(xltypeNum) != 0)
       {
           strErr.Format("Result of calculation was not a number for value %f", x);
           throw CMetaFunctionException(strErr);
       }
       return xloValue.ToDouble();
   }

Preserving state

In calling the meta-function, we change the contents of the worksheet. It is polite to restore it to its original state after the operation is complete.

1. The CMetaFunctionState class is used to save the state of the worksheet before the meta-function is used to restore it after the meta-function has completed for the last time.

2. The class records (i) the contents of the input cell, which may be a formula or a value, and (ii) the calculation mode, since we will be setting it to Manual.

3. In addition, the CMetaFunctionState class is responsible for switching off warnings, and switching them back on when the operation is complete.

Integrating with Numerical Recipes code

The following steps were significant in integrating the application with code from 'Numerical Recipes'.

1. The "Numeric Recipes" code was used without any changes.

2. The nrerror() function was implemented to throw an exception.

   CopyC++

   void nrerror(const char* err)
   {
       throw CMetaFunctionException(err);
   }

   This technique is safe to use, because these NR functions do not allocate any memory. The exception must be caught by all functions that use NR code.

3. The following static method was added to the CMetaFunction class, with the correct signature for use by NR methods.

   double CMetaFunction::Func(double);

   Thus, lines like the following can be used:

   CopyC++

   dResult = qgauss(CMetaFunction::Func, dLower, dUpper);

Implementing the Dialog

The CIntegrationDialog class contains all the logic for populating and displaying a dialog.

1. The CIntegrationDialog class is descended from the CXlDialog class. (For more details on using CXlDialog, see the Dialogs sample.)

2. Five RefEdit fields are used to let the user assign cells to x, f(x), Lower x, Upper x and the result. Each field is assigned a sequential ID.

3. The layout and creation of all fields is done in the constructor.

4. The virtual method OnOK() is implemented, to trap the pressing of the OK button. The method first validates the input fields; if any errors are found, the dialog is left open, and the focus is set to the aberrant field. If validation succeeds, EndDialog(TRUE); is called, to close the dialog and return TRUE from the CXlDialog::Show() method. (The OnOK() method is also responsible for saving the contents of the input fields for later reuse.)

Reusing dialog data

The DIALOG_DATA structure is used to save the contents of the dialog's entry fields, and to repopulate them later, if the user invokes the dialog from a cell that was used in a previous intergration operation.

1. The DIALOG_DATA structure is a single continuous block of memory. It can therefore be used as the data assigned to a binary name (see Persistent Data below).

2. The SerializeDataset() method can read the contents of input fields into a DIALOG_DATA structure, or it can populate the input fields from a DIALOG_DATA structure that was created earlier.

3. The FindCurrentDataset() method is used to search a set of DIALOG_DATA structures for a data set that includes the address of the active cell.

Persistent data

The dialog saves the current list of DIALOG_DATA sets as binary data within the active worksheet, using binary names. For a complete example using binary names, see the BinaryData sample.

1. The entire vector of DIALOG_DATA objects is treated as a single BLOB, and saved as a binary name:

   CopyC++

   void CIntegrationDialog::SaveDatasets()
   {
       CXllApp::DefineBinaryName(mc_pszDataSetName, &m_datasets[0],
           m_datasets.size() * sizeof(DIALOG_DATA));
   }

2. The BLOB is restored using GetBinaryName() and copied into the dialog's vector:

   CopyC++

   void CIntegrationDialog::RestoreDatasets()
   {
       CXlBinaryNameData data;
       if (CXllApp::GetBinaryName(mc_pszDataSetName, data))
       {
           m_datasets.assign((DIALOG_DATA*)data.GetData(),
               (DIALOG_DATA*)(((char*)data.GetData()) + data.GetCount()));
       }
   }

3. The data saved in the current sheet is completely removed by calling the ClearBinaryName() method:

   CopyC++

   static void CIntegrationDialog::ClearDatasets()
   {
       CXllApp::ClearBinaryName(mc_pszDataSetName);
   }

   This function is declared as static so that it can be called without the hassle of instantiating a CIntegrationDialog object. (It's not beautiful, but it is convenient, and it works because CXllApp::ClearBinaryName is also a static method.)

Menu

The following steps were significant in creating and completing the application's menu.

1. The project was created with the Add a Menu option switched on, so that the code to display a menu was automatically added to the project.

2. The menu code was amended to call our two add-in functions: ShowIntegrationDialog() and ClearIntegrations().

   CopyC++

   BOOL CIntegrationApp::OnXllOpenEx()
   {
       // Set up menu
       m_menu.SetTexts("Integ&rate");
       m_menu.AddItem("&Run integration...", "ShowIntegrationDialog");
       m_menu.AddItem("&Clear previous integrations", "ClearIntegrations");
       m_menu.Create();
       return TRUE;
   }

Excel 2007

Under Excel 2007, the restoration of the serialized data fails, and the dialog fields remain empty. This is because of a known bug with BinaryName data in Excel 2007 which, it has been announced, will be fixed in Office 2007 SP1.

Classes and functions used

CXlDialog | CXlDialog::Show | CXlDialog::SetFocus | CXlDialog::ControlFromID | CXlControlRefEdit | CXlControlRefEdit::GetRef | CXlControlRefEdit::SetRef | CXlControlListBox | CXlControlListBox::AddString | CXlControlListBox::GetCurSel | CXlControlListBox::SetCurSel | CXllApp::DefineBinaryName | CXllApp::GetBinaryName | CXllApp::ClearBinaryName | CXllApp::SetCalculationMode | CXllApp::SetErrorHandling | CXllApp::GetUserAbort | CXllApp::CancelUserAbort | CXllApp::XlMessageBox | CXlMacros::CalculateNow | CXlOper::MakeRef | CXlOper::Coerce | CXlOper::ChangeType | CXlOper::GetRef | CXlOper::GetFormula | CXlOper::SetFormula | CXlOper::SetValue | CXlRef::ToString | CXlMenu | CXlMenu::SetTexts | CXlMenu::AddItem | CXlMenu::Create | CXlMenu::Destroy

Sample project

Each sample project is located in a sub-directory of the Samples directory of the XLL+ installation. To use the sample project, open the solution file Integration.sln or the project file Integration.vcproj.

You can enable debugging under Excel by using the Setup Debugging command in the XLL+ ToolWindow.

When delivered, the help files are excluded from the build. You can enable the help build by selecting the files Integration.help.xml and Integration.chm in the Solution Explorer, and using the right-click menu to view Properties. Select the page "Configuration Properties/General" and set the "Excluded from build" property to "No". See Generating help in the User Guide for more information.

See Also

List of Sample Projects