Your custom worksheet functions can take optional parameters. The users of your worksheet can then decide whether to supply them or leave them blank. Optional parameters can be indicated in one of two ways, as with parameter help strings that were explained in a previous article.
Either using the [Optional] attribute, as shown above. Or by using the IsOptional parameter of the [ParameterDetails] attribute as shown below.
If an optional parameter is not supplied to the worksheet function then you get passed a zero value for all numeric types and an empty string for string types. Sometimes you may wish to provide your own default value for a missing optional parameter. In that case you can use the [DefaultValue] attribute or the DefaultValue parameter to the [ParameterDetails] attribute. See below:
Default values can also be supplied for array parameters, see below:
Finally, you may prefer to indicate to your users which parameters are optional and which are not, you can do this by setting the OptionalParameterEmbelishment parameter of the [AddinClass] attribute to UseSquareBrackets which causes all optional parameters to be displayed in the function wizard within square brackets.
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.
Recently in JetXLLPro Category
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.
As you saw in the previous article, it's easy to build a custom worksheet function for Excel with JetXLL. Of course, whilst you are developing your worksheet functions you may make mistakes and wish to debug the code whilst it is running inside Excel; luckily this too is very easy!
- Open the SimpleAddin solution file, select the solution explorer tab and select the solution.
- Right click and select properties.
- Select the Debug tab and check the 'start external program' button.
- Browse for the Excel executable (usually located at Program Files\Microsoft Office\Office12\Excel.exe).
- Build the addin and copy the JetXLL.xll file and JetXLL.xml file into the same directory as the addin's dll file that you have just built.
- Start debugging and Excel will start up; you may get a warning about Excel not containing debugging information, you can ignore this.
- Place a breakpoint in your addin.
- Within Excel select File, Open and browse to the directory where you built your addin, select the JetXLL.xll file and open it. This will load JetXLL and your addin into Excel.
- Enter your worksheet function into a cell and when the cell is calculated you will find yourself in the debugger inside your worksheet function.
You may want to set up Excel so that you always load JetXLL and your addins, a future article will show you how to do this. If you set Excel up in this way then debugging is even easier, as soon as Excel is loaded by the debugger it will load JetXLL and your addin and you can simply load a test sheet and step into your functions.
JetXLL lets you easily expose managed code as worksheet functions in Microsoft Excel. This article will walk you through the process of creating an addin which exports some worksheet functions and show you how to load this with JetXLL. We will assume that you are using Visual Studio 2008 to create the addin and that you know how to build C# projects.
There are some other specialist types that we can also use, but we'll deal with those a little later.
Now simply double click on the XLL file (either JetXLL.xll or JetXLLPro.xll) and Excel will start and will load your addin and your custom worksheet function is ready to use.
Create a new sheet and type =AddTwoInts(1,1) into a cell and you should see the result.
- First Open Visual Studio and create a new C# class library project.
- Next add a reference to the JetXLL.dll assembly. Browse for where you installed this dll, or copy it into your addin project's directory and browse for it there.
- Add a using statement for the JetByte.JetXLL namespace.
- Add the [AddinClass] attribute to the class that was created by the new project wizard.
- Add a public static method to the class and decorate it with the [WorksheetFunction] attribute as shown below:
- Build your addin.
- System.Boolean
- System.Int32
- System.DateTime
- System.Double
- System.String
There are some other specialist types that we can also use, but we'll deal with those a little later.
Once you have built your addin you can run Excel, and have JetXLL load your addin. There are several ways to do this but the easiest is to simply copy the JetXLL.xll (or JetXLLPro.xll) file into the same directory as your addin's dll file and the JetXLL.dll file. Next create a configuration file; like the one shown here, and place that in the same directory:
Create a new sheet and type =AddTwoInts(1,1) into a cell and you should see the result.
You can download the source code for this simple addin from here. You will need a copy of JetXLL to be able to build and run the code.
JetXLL is a quick, easy and affordable way to create custom worksheet functions and complete XLL addins for Excel using managed code (such as C#). There are plenty of other ways to do this (see the links at the end of this article), but, we feel that JetXLL makes it very easy for people who are comfortable writing managed code to expose it to Excel. With JetXLL you can access functionality that Excel's 'C API' exposes without needing to understand the complexity of writing an XLL from scratch in a language such as C or C++.
Managed code can return 'results objects' to Excel which can be passed by name to other JetXLL functions and which can be queried and manipulated within the sheet.
It's very easy to expose your managed code as an Excel worksheet function using JetXLL. First add a reference to the JetXLL.dll to your code and then, simply decorate the class with the [AddinClass] attribute and the functions that you want to expose to Excel with the [WorksheetFunction] attribute. Edit the JetXLL configuration file so that JetXLL will load your assembly and that's it. When the JetXLL addin is loaded it will load your assembly, parse your code for its attributes and expose your functions to Excel.
The simple example above shows how to expose a static function as an Excel worksheet function. You can now type =AddTwoInts() into an Excel cell and provide the two numbers that the function requires (=AddTwoInts(A1, B2) for example) and Excel will call your C# code and place the value that you return into the spreadsheet cell for you.
There's much more to JetXLL, you can expose member functions and indicate which constructor that Excel should call on your classes. You can create Command Functions which can be called from menus, toolbar buttons and macros. You can build custom Excel menus or Toolbars which can call your Command functions. If you have functions that take a while to execute you can use the [AsyncWorksheetFunction] attribute to have JetXLL call the function on a separate thread and you can use the IAsyncWorksheetFunctionCall interface to pass back interim results which are displayed by Excel as your calculation progresses.
If you need to push Real Time Data into your Excel spreadsheets then you can use JetXLL's RTD support to implement a data source without the need to register any COM objects on your workstation and without needing to understand the complexity of the Excel RTD API.
JetXLL provides direct support for "trigger" arguments to Excel worksheet functions. Trigger arguments are dummy arguments that are passed to worksheet functions purely to trigger them (by providing an otherwise impossible dependency to another part of the sheet), they can also be used to prevent 'expensive' calls being made whilst you are configuring the worksheet, or to allow you to 'turn off' aeas of the worksheet using a single trigger cell which can be set to FALSE. Trigger arguments are supported by JetXLL in such a way that they can be automatically added to a managed functions worksheet function signature and then managed completely by JetXLL. Your managed code is never called and marshalling of additional arguments is not done if the trigger argument evaluates to FALSE.
This worksheet function takes an implicit trigger argument. By default the name of the argument is 'Trigger', the help string is 'Trigger the function call if TRUE' and the message returned if the call is not made is 'Call not made'. All of these are configurable. If the optional trigger argument is not TRUE then the call is not made.
JetXLL is currently in a beta testing phase. If you'd like to be involved with the beta test and try out JetXLL during this time then please get in touch with us by emailing us at beta (at) jetxll.net.
Some alternative ways to create managed XLLs
Alternatively you might want to take a look at Visual Studio Tools for Office (VSTO).