Whilst it's very easy to create a worksheet function using C# with JetXLL simply by adding the [WorksheetFunction] attribute to your code you can add additional attributes and set some parameters on the [WorksheetFunction] attribute to allow your code to integrate better with Excel. This article will explain how you can make your worksheet function integrate nicely with the Excel Function Wizard.
Note that you can create multiple addins and place functions from different addins into the same custom categories. The custom names are not unique between addins, or even to JetXLL addins.
Once you've placed your custom worksheet functions in appropriate categories you might wish to give your users a little more help whilst they are selecting their functions.
The second is to use the [ParameterDetails] attribute at the function level as shown below:
As you can see, using the [ParameterDetails] attribute keeps the parameters themselves uncluttered; some users prefer this style. Since all of the parameter details attributes for all of the parameters appear together they need to contain the name of the parameter that they are affecting; the Name parameter serves this purpose. Note that you can provide parameter details for all or just for some parameters and that if a parameter has both a [ParameterDetails] attribute and a parameter level [HelpString] attribute applied to it then the parameter level attribute takes precedence.
One thing to note from the code example above is that by default all parameter names have the first letter capitalised. That is, in the function wizard, the second parameter will be referred to as "Second value" rather than "second value". This is so that for normal code, without any parameter naming attributes, JetXLL will make the parameters look similar to how most other Excel function parameters look. You can change this behaviour by setting the ParameterEmbelishment value of the [AddinClass] attribute to NoEmbelishment.
As you enter each of the optional values in the function wizard you'll see the result of the calculation so far. It's not often that this technique is required and it's more efficient to leave the default setting of 'false' for CallWhenFunctionWizardIsActive.
The function wizard provides help for you whilst you are inserting a function into a cell. The simple addin we built in the previous article can be accessed from the function wizard like any other but the user experience can be made better by adjusting the attributes that you apply to your code.
The first way that you can integrate with the function wizard is by placing your functions in your own categories; or, if it's appropriate, by adding them to existing categories.
By default a JetXLL worksheet function is placed in a function wizard category that consists of "JetXLL: " followed by the name of the class in which the function was defined. So, our AddTwoInts() function in the SimpleAddin will be in the category "JetXLL: SimpleAddin".
You can select the category that your worksheet functions appear in by applying the [ExcelCategory] attribute either at the class level, so that it applies to all worksheet functions within that class, or at the function level. If you apply the attribute at the class level then you can override it at the function level if you need to.
The [ExcelCategory] attribute takes a parameter to indicate the category to place the functions in. This is either a member of the StandardExcelCategory enumeration; such as "Database", "Information", "Logical", etc or a string which is the name of your own custom category. See the example below for more details.
Note that you can create multiple addins and place functions from different addins into the same custom categories. The custom names are not unique between addins, or even to JetXLL addins.
Once you've placed your custom worksheet functions in appropriate categories you might wish to give your users a little more help whilst they are selecting their functions.
We can add these help strings for our own functions. First we can add a function help string, this is the string that reads "Returns the accrued..." in the dialog above. We do this by adding a HelpString parameter to the [WorkSheetFunction] attribute. Next we can add individual help strings for each parameter. There are two ways to do this, both are equivalent and the choice of which method to use is purely based on the aesthetics of the resulting code; some people prefer the look of one method over the other.
The first method is to add [HelpString] attributes to each parameter as shown below:
The second is to use the [ParameterDetails] attribute at the function level as shown below:
As you can see, using the [ParameterDetails] attribute keeps the parameters themselves uncluttered; some users prefer this style. Since all of the parameter details attributes for all of the parameters appear together they need to contain the name of the parameter that they are affecting; the Name parameter serves this purpose. Note that you can provide parameter details for all or just for some parameters and that if a parameter has both a [ParameterDetails] attribute and a parameter level [HelpString] attribute applied to it then the parameter level attribute takes precedence.
As you can see from the example above, the parameter name that is exposed to Excel need not be the same as the variable name in the function declaration itself. Once again there are two methods of changing this name. The first is by using the NameAs parameter of the [ParameterDetails] attribute as shown above. The second is to apply a [NameAs] attribute at the parameter level as shown below:
One thing to note from the code example above is that by default all parameter names have the first letter capitalised. That is, in the function wizard, the second parameter will be referred to as "Second value" rather than "second value". This is so that for normal code, without any parameter naming attributes, JetXLL will make the parameters look similar to how most other Excel function parameters look. You can change this behaviour by setting the ParameterEmbelishment value of the [AddinClass] attribute to NoEmbelishment.
By default JetXLL does not call your worksheet functions when the Excel function wizard is active. This is because in most situations you don't want your functions called when the user is using the wizard to enter parameter values. All of the values may not be present and the work that your custom worksheet function performs may require all parameters and may be costly to perform. You can change this with the CallWhenFunctionWizardIsActive parameter of the [WorksheetFunction] attribute. See the following code example:
As you enter each of the optional values in the function wizard you'll see the result of the calculation so far. It's not often that this technique is required and it's more efficient to leave the default setting of 'false' for CallWhenFunctionWizardIsActive.
As you can see it's easy to add attributes to your code so that your functions are easier to use by your users and look more like built in Excel functions.
You can download the source code for this example code from here. You will need a copy of JetXLL to be able to build and run the code.
