Skip to main content

How to: Create a Simple Custom Function

  • 4 minutes to read

This example demonstrates how to create a custom worksheet function. A custom function is an object that implements the ICustomFunction interface. The IFunction.Evaluate method performs all required calculations. To use a custom function, add it to the IWorkbook.CustomFunctions collection.

Note

Custom functions are not saved in workbook files.

The following code sample demonstrates how to implement a custom function called SPHEREMASS, which calculates the mass of a sphere made of a material with a specified density. If the density is not provided, the density of water is used.

function in a workbook

View Example

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Functions;

public class SphereMassFunction : Object, ICustomFunction
{
    const string functionName = "SPHEREMASS";
    readonly ParameterInfo[] functionParameters;

    public SphereMassFunction()
    {   
        // Missing optional parameters do not result in error message.
        this.functionParameters = new ParameterInfo[] { new ParameterInfo(ParameterType.Value, ParameterAttributes.Required), 
            new ParameterInfo(ParameterType.Value, ParameterAttributes.Optional)};
    }

    public string Name { get { return functionName; } }
    ParameterInfo[] IFunction.Parameters { get { return functionParameters; } }
    ParameterType IFunction.ReturnType { get { return ParameterType.Value; } }

    // Reevaluate cells on every recalculation.
    bool IFunction.Volatile { get { return true; } }

    ParameterValue IFunction.Evaluate(IList<ParameterValue> parameters, EvaluationContext context)
    {
        double radius;
        double density = 1000;
        ParameterValue radiusParameter;
        ParameterValue densityParameter;

        if (parameters.Count == 2)
        {
            densityParameter = parameters[1];
            if (densityParameter.IsError)
                return densityParameter;
            else 
                density = densityParameter.NumericValue;                
        }

        radiusParameter = parameters[0];
        if (radiusParameter.IsError)
            return radiusParameter;
        else
            radius = radiusParameter.NumericValue;

        return (4 * Math.PI) / 3 * Math.Pow(radius,3) * density;

    }
    string IFunction.GetName(CultureInfo culture)
    {
        return functionName;
    }
}
//...

// Create a custom function and add it to the global scope.
SphereMassFunction customFunction = new SphereMassFunction();
if (!workbook.CustomFunctions.Contains(customFunction.Name))
    workbook.CustomFunctions.Add(customFunction);

// Add data to a workbook
workbook.BeginUpdate();
try
{
    Worksheet worksheet = workbook.Worksheets[0];
    worksheet.Range["A1:H1"].ColumnWidthInCharacters = 12;
    worksheet.Range["A1:H1"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

    worksheet.DefinedNames.Add("seawater", "1025");

    worksheet["A1"].Value = "Radius, m";
    worksheet["B1"].Value = "Material";
    worksheet["C1"].Value = "Mass, kg";
    worksheet["A2"].Value = 0.1;
    worksheet["B2"].Value = "";
    worksheet["C2"].FormulaInvariant = "=SPHEREMASS(A2)";
    worksheet["C2"].NumberFormat = "#.##";
    worksheet["A3"].Value = 0.1;
    worksheet["B3"].Value = "Seawater";
    worksheet["C3"].FormulaInvariant = "=SPHEREMASS(A3,seawater)";
}
finally
{
    workbook.EndUpdate();
}

Tip

Refer to the following article for information on how to create a custom function that uses a cell range as a parameter:

Read Tutorial: How to: Create a Custom Function that Returns an Array