XLL+ Class Library

Integration Sample

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:

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:

    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:

    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.

    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:

    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:

    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:

    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:

    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().

    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;
    }

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

Project files

If you are using MS Developer Studio 6, then you should open the project file Integration.dsp.

If you are using MS Visual Studio .NET 2002, then you should open the solution file Integration.sln or the project file Integration.vcproj.

If you are using MS Visual Studio .NET 2003, then you should open the solution file Integration71.sln or the project file Integration71.vcproj.

If you are using MS Visual Studio 2005, then you should open the solution file Integration8.sln or the project file Integration8.vcproj.

See Also

List of Sample Projects | Samples and Walkthroughs