RadSpreadSheet Custom Functions

[Valentin]

Hi guys,

For my RadSpreadSheet component, I have implemented some custom functions using the FunctionWithArguments implementation. I have a BaseCustomFunction custom class which inherits from the FunctionWithArguments telerik class, and for example, I have a Q1QFunction class which inherits from my BaseCustomFunction, as below:

Imports MAAObject.AerodynamicObject
Imports Telerik.Windows.Documents.Spreadsheet.Expressions
Imports Telerik.Windows.Documents.Spreadsheet.Expressions.Functions

Namespace Aerodynamic.Calculator

    Public Class Q1QFunction
        Inherits BaseCustomFunction

#Region "Properties"

        Public Overrides ReadOnly Property Type As ECustomFunction
            Get
                Return ECustomFunction.Q1_Q
            End Get
        End Property

#End Region

        Public Sub New(name As String, description As String)
            MyBase.New(name, description)
        End Sub

#Region "Methods"

        Protected Overrides Function GetFunctionInfos() As FunctionInfo
            Dim requiredArguments As IEnumerable(Of ArgumentInfo) = New ArgumentInfo() {
                New ArgumentInfo("Altitude_ft", "The value of the Altitude, in ft.", ArgumentType.Number),
                New ArgumentInfo("Mach", "The value of the Mach.", ArgumentType.Number),
                New ArgumentInfo("ISA_ºC", "The value of the ISA, in Celsus degrees.", ArgumentType.Number),
                New ArgumentInfo("Mach_Option", "The value of the Mach Option.", ArgumentType.Text),
                New ArgumentInfo("Cpressure", "The value of the Cpressure.", ArgumentType.Number),
                New ArgumentInfo("Mach1_Known", "The value of the known Mach1.", ArgumentType.Number)
            }

            Dim optionalArguments As IEnumerable(Of ArgumentInfo) = New ArgumentInfo() {}

            Return New FunctionInfo(Me.Name, FunctionCategory.MathTrig, Me.Description, requiredArguments, optionalArguments, 0, True)
        End Function

        Protected Overrides Function EvaluateOverride(context As FunctionEvaluationContext(Of Object)) As RadExpression
            If context.Arguments.Length <> Me.FunctionInfo.RequiredArgumentsCount Then Return Nothing

            Dim altitude As Decimal
            If Not Decimal.TryParse(context.Arguments(0), altitude) Then Return Nothing 'tester le return nothing

            Dim mach As Decimal
            If Not Decimal.TryParse(context.Arguments(1), mach) Then Return Nothing

            Dim isa As Decimal
            If Not Decimal.TryParse(context.Arguments(2), isa) Then Return Nothing

            Dim machOption As String = CStr(context.Arguments(3))
            If Not {"YES", "NO"}.Contains(machOption) Then
                Throw New ExpressionException("Mach_Option parameter must be ""YES"" or ""NO""")
            End If

            Dim cPressure As Decimal
            If Not Decimal.TryParse(context.Arguments(4), cPressure) Then Return Nothing

            Dim mach1Known As Decimal
            If Not Decimal.TryParse(context.Arguments(5), mach1Known) Then Return Nothing

            Return New NumberExpression(AerodynamicCalculator.Q1_Q(altitude, mach, isa, machOption, cPressure, mach1Known))
        End Function

#End Region

    End Class

End Namespace

 

> I have a question about the machOption parameter inside the EvaluateOverride method: is it possible to raise an error if the parameter hasn't the desired value? For example I want to return #VALUE or #REF or another error message, and avoid the calculation of the formula if something provided is wrong.

I tried to use the ErrorExpression or RadExpression base classes, but I can't build them because no New Constructor method is available.

 

Do you know how I can do that?

 

Thank you!

Valentin M.

[Dimitar]

Hi Valentin,

It's a static class, you can return the error expression like this: 

 Return ErrorExpressions.NumberError

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,
Dimitar
Progress Telerik

[Valentin]

Hello Dimitar,

You're right, I don't see the ErrorExpressionS static class, thank you. Do you know if there is any way to display a custom error message? For example in my case the parameter must be "YES" or "NO". 

If it's not possible I just will return a Value or Reference error I guess.

Thank you,

Valentin M.

[Dimitar]

Hi Valentin, 

You can create a custom error expression. For example:

Public Class MyErrorExpression
	Inherits ConstantExpression(Of String)

	Private Property Message() As String
	Public Sub New(ByVal message As String, ByVal value As String)
		MyBase.New(value)
		Me.Message = message
	End Sub

End Class

I hope this helps. Should you have any other questions do not hesitate to ask.

[Valentin]

Hello Dimitar, thanks for your feeback.

Should the CustomErrorExpression be registered or something else? I don't find any documentation about that. -> When I return the custom expression, I got the following exception:

If Not {"YES", "NO"}.Contains(machOption) Then Return New CustomErrorExpression("Mach_Option parameter must be YES or NO", "#INV") 

The method or operation isn't implemented.

   at Telerik.Windows.Documents.Spreadsheet.Model.CellValueFactory.GetCellValueType(ConstantExpression value)
   at Telerik.Windows.Documents.Spreadsheet.Model.FormulaCellValue.get_ResultValueType()
   at Telerik.Windows.Controls.Spreadsheet.Controls.RadSpreadsheetFormulaBar.UpdateFormulaBarTextFromSelectedCell()
   at Telerik.Windows.Controls.Spreadsheet.Controls.RadSpreadsheetFormulaBar.<>c__DisplayClass9.<Cells_CellPropertyChanged>b__8()
   at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
   at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)

 

Thank you !

Valentin M.

[Dimitar]

Hi Valentin, 

I was able to reproduce this. Unfortunately, our internal logic only recognized the known expression types. This means that you need to use one of the ErrorExpreessions in this case. 

I want to apologize for the inconvenience this is causing you.

[Valentin]

Hello Dimitar,

Alright, no problem, I'll use the known expression types.

Thank you for the assistance.

Valentin M.