This example implements a custom FPScript function that can be used in FlexPro FPScript formulas like a built-in function after registration. It is supported in the wizard of the FPScript Editor and is automatically available in all loaded databases.

Notes:
The example shows how to implement this type of function in VBA. The complete VBA source text for the example is in the CustomFunction.fpd project database. However, you can also use any other automation-compatible programming language, such as C# or C++. A variant of the example that has been developed as an add-in in C++, is also included with FlexPro. The path to the project database is usually C:\Users\Public\Documents\Weisang\FlexPro\2021\Examples\VBA\CustomFunction\CustomFunction.fpd or C:>Users>Public>Public Documents>Weisang>FlexPro>2021>Examples>VBA>CustomFunction>CustomFunction.fpd.
 
You can implement a custom FPScript function in FPScript as well. See Custom FPScript Functions Tutorial.

Background

The example defines a function with three arguments that adds the first two arguments together and dependent on the third argument. It is constructed from several built-in functions. The example covers the essential elements for implementing a custom FPScript function:

•Defining arguments

•Argument type and structure restrictions

•Using default arguments

•Registering and unregistering the function

•Accessing the arguments after the function call

•Defining custom FPScript constants

Registering a Custom Function

Before you can register a function, you have to define its arguments and other properties. The registration itself then appears at the end of the function definition.

First the function is added to the UserDefinedFPScriptFunctions collection. You should then assign it a description. This is then automatically displayed when the wizard window is used, for instance.

With UserDefinedFPScriptFunctions.Add("MyFunction")

     .Description = "Adds or subtracts two values"

     .Indeterministic = False

     …
End With

The Indeterministic property defines whether the function always returns the same result for the same input. If Indeterministic is set to True, formulas that use this function have to be calculated at each update interval. Therefore, this value should be set to True only if absolutely required, such as when there are changing external dependencies.

After the function has been accepted into the UserDefinedFPScriptFunctions collection, the required arguments can be added. Only the name of the argument is required, all other settings are optional. However, it is recommended that you provide a description so that a note regarding the argument can be displayed in the wizard window. In addition, the argument data types and structures are usually limited to the exact degree required. The advantage of this procedure is that FlexPro already handles checking for conformance with data type and structure restrictions before the custom FPScript function routine is called. This makes it possible to limit the amount of code necessary for implementing the function and allows FlexPro to handle the output of error messages.

With .Parameters.Add("Arg1")

     .Description = "First argument"

     .AllowedTypes = fpParameterTypeNumeric

     .AllowedStructures = fpParameterStructureScalar Or _

         fpParameterStructureDataSeries Or fpParameterStructureSignal

End With

In the case above, the first argument is limited to numeric data types and scalar values, data series and signals are permitted as data structures. Data types and structures can be linked for the assignment with the OR operator as shown. If the argument deviates from the specified restrictions when the function is used, an error message will appear.

The default value is used for the last argument of the function. This means that the argument can be omitted. In this case, the default value for this argument can be used internally.

With .Parameters.Add("Operation")

     .Description = "Type of operation"

     .AllowedTypes = fpParameterTypeNumeric

     .AllowedStructures = fpParameterStructureScalar

     .DefaultValue = "MYFUNC_OPERATION_ADD"

End With

The case above has a unique feature. The type of Operation parameter is numeric, but text is specified as the default value. The text contains the name of a previously included custom FPScript constant whose value is determined automatically as a default value in this case. The advantage of this type of definition is that the more accessible name of the constant is displayed in the wizard window.

Custom FPScript constants can be inserted as follows:

With UserDefinedFPScriptConstants

     .Add "MYFUNC_OPERATION_ADD", "Selects the add operation", 1

     .Add "MYFUNC_OPERATION_MIN", "Selects the subtract operation", 2

End With

A new unit is simply added to the UserDefinedFPScriptConstants collection by specifying a name, description and scalar value. Conflicts with the names of existing built-in or custom constants will result in a relevant error message.

After the function parameters are declared, you can register the function for use. The Register method is called for this purpose. As an argument, it will receive a link to an object that implements the IUserDefinedFunctionCalculate interface.

.Register oMyFunction

The IUserDefinedFunctionCalculate interface includes the Calculate method that is called by FlexPro when calculating a formula that uses MyFunction, as long as it complies with the parameter restrictions.

Notes:
Only after registering a function are the defined parameters checked for consistency. If an error occurs during this check, you should review the code that defines the parameters for problems.
 
Typically, custom FPScript functions are registered in an automatically executed function like AutoOpen or AutoExec so that they are available at a defined time early on.

Unregistering is the opposite of registering a function, so that usually in AutoOpen or AutoClose, the following occurs:

Sub AutoClose()

    '   unregister function

    UserDefinedFPScriptFunctions.Item("MyFunction").Delete

    '   unregister constants

    With UserDefinedFPScriptConstants

        .Item("MYFUNC_OPERATION_ADD").Delete

        .Item("MYFUNC_OPERATION_SUB").Delete

    End With

End Sub

Deregistration occurs automatically by deleting the function from the UserDefinedFPScriptFunctions collection. Constants are unregistered in the same manner.

Note:   Unregistering custom FPScript elements is not absolutely necessary, since they are automatically removed once the program is closed. Since, however, just an object reference is supplied when a function is registered, explicitly unregistering it results in controlled termination of the object that executes the function calculation. This makes it possible to avoid unwanted side-effects (such as FlexPro hanging).

Using a Custom Function

When calling the registered FPScript function MyFunction the Calculatemethod of the IUserDefinedFunctionCalculate interface is called. The arguments are passed in the form of a Variance array. The implementation example demonstrates how to access arguments. Due to the possible variety of data structures and types, the example has been simplified in some spots.

The array with the arguments SafeArrayOfArguments is one-based, as is typical in VBA. You can therefore access the desired operation by using index three:

'   MYFUNC_OPERATION_ADD = 1

'   MYFUNC_OPERATION_SUB = 2

nOperation = SafeArrayOfArguments(3)

The third argument has been restricted to scalar values of the numeric type. However, a void value can still be specified for the operation to be executed. Therefore, a check is carried out:

'   check operation

If nOperation <> 1 And nOperation <> 2 Then

'   will be propagated as error 0x800a0002 to FlexPro

    Err.Raise 2, "IUserDefinedFunctionCalculate_Calculate", "Invalid operation value"

               , "Invalid operation value"

End If

In the event of an error, an exception occurs. The message "Invalid operation value" then appears together with the specific error code either in the FlexPro Event Log or in an error dialog box.

The remaining part of the function calculation shows how you conduct case differentiations using the first argument with the help of the VB functions IsObject, TypeOf and IsArray and then execute the desired operation.

Notes on using VBA for development

•If after registering a function, its Calculate method is changed, the function has to be unregistered and then reregistered. This is necessary because otherwise the entry point for the function is no longer valid and an error message will be displayed.

•At least when testing a Calculate method, you should make sure that under File > Options on the System Settings tab, the option Update objects in the background is disabled. Otherwise, FlexPro will crash if you use breakpoints in the Calculate method. The reason for this is that problems occur in the VBA environment when calling the Calculate method in the background program thread.

•For custom FPScript functions in VBA, the runtime environment ensures that the Calculate method runs only once at a certain time. This limitation is in place for reasons of safety. For one thing, the VBA runtime environment does not support multiprocessing very well, and for another, the necessary limitation to one running instance in VBA is not possible using the language as the sole medium.