Skip to main content

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

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.

simple function

View Example

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Functions;
using DevExpress.XtraSpreadsheet;
//...

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

    public SphereMassFunction()
    {   
        // Missing optional parameters do not result in an 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; } }
    bool IFunction.Volatile { get { return false; } }

    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();
var globalFunctions = workbook.Functions.GlobalCustomFunctions;
if (!globalFunctions.Contains(customFunction.Name))
    globalFunctions.Add(customFunction);


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)";
    worksheet["C3"].NumberFormat = "#.##";
}
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

See Also