Excel user-defined functions (UDF)
and RTD tips for VB.NET and C#
Excel UDF and RTD tips
On this page you can find some tips that will hopefully make your work with Excel user-defined functions (UDF) and real-time data servers (RTD) a bit easier.
Excel Automation add-ins are supported starting from Excel 2002; XLL add-ins work in Excel 2000 - 2013.
Automation add-ins are suitable if your UDF deals a lot with the Excel object model; XLL add-ins are faster in financial and mathematical calculations. Note however that native code XLL add-ins work faster than managed UDFs.
Information below applies to the Add-in Express implementation of Excel Automation add-ins and XLL Add-ins.
You start finding the cause from Use the latest version of the loader.
If your UDF isn't shown in the Add-in Manager dialog, then it isn't registered - see Locating Excel UDF Add-ins in the Registry.
Then you need to check the log file (see Loader's log) for errors. If there are no errors but .NET Framework 1.1 is mentioned in the log, read Conflicts with Office extensions developed in .NET Framework 1.1.
When you enter a formula in the Formula Bar, neither function descriptions nor descriptions of function parameters are shown. Debugging this problem shows that Excel just doesn't call any methods responsible for providing that info.
Also, we've found a non-described restriction in XLLs: the total length of a string containing all parameter names of a given function divided by a separator character minus one cannot be greater than 255. The same restriction applies to parameter descriptions. If any of such strings exceed 255 characters, many strange things occur with the descriptions in the Excel UI. Below there are two useful functions that help checking parameter names and descriptions; add those functions to the XLLContainer class of your XLL module and invoke them in an Excel formula.
An Excel user-defined function may return a value of any object type, of course. However, the UDF is always called in a certain Excel context and this makes impossible some things that are possible in other contexts: say, when called in a UDF returning an Excel.Hyperlink, the Hyperlinks.Add method inserts a hyperlink displaying an error value (#Value!) and working properly in all other respects. The same code works without any problems when called from a button created by a COM add-in.
You should understand that the Excel engine processes several tasks at once to deal with user input as well as recalculation planning and performing. When the OnTime event of the XLL module occurs, Excel is guaranteed to expect your actions. If you use a Timer instead, you can execute some actions when Excel doesn't expect it and this may end with a crash.
If, in the Insert Function dialog, you choose a user-defined function that does not accept parameters, Excel shows the Function Arguments dialog informing the user that "This function takes no arguments".
It is obvious that creating such functions requires that you not add parameters to your UDF.
In an XLL add-in, however, you may run into a bug in the Excel API. The bug shows itself if a parameterless function is mentioned in an ADXExcelFunctionDescriptor that has a non-empty string in the Description property. If this is the case, you'll get another version of the Function Arguments dialog:
That is, to bypass that issue, you need to leave the ADXExcelFunctionDescriptor.Description property empty.
Usually a UDF returns a single value. When called from an array formula, the UDF can return a properly dimensioned array (see Returning values when your Excel UDF is called from an array formula).
With Excel 2007 and above, your XLL function can assign a value to an arbitrary cell by running a command-equivalent Excel 4 macro via the ExecuteExcel4Macro() method of the Excel.Application interface. Add-in Express incorporates this approach by providing the safeMode parameter in constructors of the AddinExpress.MSO.ADXExcelRef type; if that parameter is true, calling the ADXExcelRef.SetValue() method triggers a hidden macro that Add-in Express registers specially for that situation. Still, you use this approach at your risk because changing an arbitrary cell from a UDF may crash or hang Excel.
The answer is "no". A formula does not put its result in a cell. Instead, the formula causes the cell to display the result of the formula. That is, the cell contains the formula, not the result; the cell just displays the result. That is, an UDF cannot return an empty cell; it must return a value and then Excel interprets the value.
Microsoft says that a function that is defined in an XLL add-in can be called under three circumstances:
1. During the recalculation of a workbook
Under the first two circumstances, the Excel Object Model does not expect, and is not prepared for, incoming Automation calls. Consequently, unexpected results or crashes may occur. So, you must be prepared for the fact that some calls to the Excel Object model from your UDF may crash or hang Excel.
In your Excel Automation add-in, you cast the ADXExcelAddinModule.HostApplication property to Excel.Application and get ExcelApp.Caller in VB or call ExcelApp.get_Caller(Type.Missing) in C#. That method typically returns an Excel.Range containing the cell(s) the UDF is called from (see the Excel VBA Help Reference on Application.Caller).
In your XLL add-in, you use the ADXXLLModule.CallWorksheetFunction method. The ADXExcelRef returned by that method allows determining the index (indices) of the cell(s) on the worksheet the UDF is called from. You can also call the ADXExcelRef.ConvertToA1Style (or ConvertToR1C1Style) method and get a string representing the caller's address, which is convertible to an Excel.Range by passing it to the _Module.ExcelApp.Range method (in C#, the second parameter of the Range method is Type.Missing). The _Module (Module in C#) above is an automatically generated property of the XLLContainer class. The ExcelApp above is an automatically generated property of the ADXXLLModule class.
The Insert Formula dialog starts a one-step wizard that calls your UDF in order to provide the user with the description of the UDF parameters (XLL only), the current return value as well as with an entry point to the help reference for your UDF. If you develop an XLL, you can use the ADXXLLModule.IsInFunctionWizard property, see Step #3 - Creating a user-defined function for a code sample.
In an Excel Automation add-in, you can use the Win API to find if the wizard window is shown. You can also try another approach suggested by a customer (thank you, Chris!):
Nevertheless, this code requires polishing since it leaves a number of COM objects non-released; please check Releasing COM objects.
In XLL add-ins, you use AddinExpress.MSO.ADXExcelError. In Excel Automation add-ins, see AddinExpress.MSO.ADXxlCVError.
Just return a properly dimensioned array of a proper type. You can find the array dimensions from the range the UDF is called from - see Determining What cell / worksheet / workbook your UDF is called from. Here are two useful XLL samples:
Despite the restrictions introduced by internal context management in Excel (see Using the Excel Object Model in an XLL), some things are possible to do. Below is a sample (thank you, Thilo!) demonstrating the following aspects of XLL programming:
To convert the code below to C#, call ExcelApp.get_Range(callerAddress,Type.Missing) instead of calling ExcelApp.Range(callerAddress) in VB.NET. Other changes are obvious.
Nevertheless, you should be very accurate when using this approach because the Excel Object Model doesn't expect such calls to be made when a formula is calculated. If you ever run into a problem with the code above, you can create a COM add-in that uses the SheetChange event in order to parse the formula just entered and format the corresponding cells as required.
In Excel 2007 and higher, your XLL UDF can be registered as multi-threaded. This allows Excel to call your UDF simultaneously on multiple threads and in this way minimize the time required for recalculation. This is especially useful when your UDF makes a call to a server so that Excel may issue another call while the first one is being executed; in the same situation in Excel 2000-2003, the second call waits for the first one to finish.
A UDF becomes thread-safe formally if you set the IsThreadSafe property of the corresponding function descriptor object (see Step 4. Configuring UDFs, for instance) to true. To be thread-safe in reality, your UDF should comply with several rules (the information below is a compilation of Financial Applications using Excel Add-in Development in C/C++, 2nd Edition):
COM Add-in, Excel UDF and AppDomain
It's very useful to combine an Excel add-in and a COM add-in (supporting Excel): the COM add-in can show controls that, for instance, provide some settings for your Excel UDF. To get the current state of the controls in your UDF, you use the ExcelApp.COMAddins property as shown in Accessing Public Members of Your COM Add-in from Another Add-in or Application. In the COM add-in, you can call any public method defined in your UDF via ExcelApp.Evaluate(...).
If you use both XLL module (ADXXLLModule) and add-in module (ADXAddinModule) in the same project, they are always loaded into the same AppDomain. But Excel Automation add-ins (ADXExcelAddinModule) are loaded into the default AppDomain if you don't take any measures. The need to have them in the same AppDomain can be caused by the necessity to share the same settings, for instance. To load the Automation add-in to the AppDomain of your COM add-in, you need to call any method of your Excel add-in using ExcelApp.Evaluate(...) before Excel (or the user) has a chance to invoke your Excel add-in. If such a call succeeds, your Excel Automation add-in is loaded into the AppDomain of your COM add-in.
The order in which Excel loads extensions is unpredictable; when the user installs another Excel add-in that order may change. We highly recommend testing your solutions with and without Analysis Toolpak installed. Pay attention that ExcelApp.Evaluate(...) returns a string value representing an error code if your UDF is still being loaded. In that case, you can try using several events to call your UDF: OnRibbonBeforeCreate, OnRibbonLoad, OnRibbonLoaded, AddinInitialize, AddinStartupComplete, as well as Excel-related events such as WindowActivate etc. We haven't tested, however, a scenario in which Excel refreshes a workbook containing formulas referencing an Excel Automation add-in. If you cannot win in such a scenario, you need to use an XLL add-in instead of the Automation add-in.
Add-in Express supports developing RTD Servers in DLLs only.
Microsoft limits the minimal interval between updates to 2 seconds. There is a way to change this minimum value but Microsoft doesn't recommend doing this.
You can create asynchronous user-defined functions in Excel 2010 and above.
An asynchronous UDF consists of two parts. One of them, which works synchronously, is quite a typical UDF. Excel calls it when you specify the UDF name in an Excel formula. Typically, it performs the following functions:
The synchronous part is declared as a method with no return value (a Sub in VB.NET). To return anything to Excel, the UDF declaration must include a parameter of the ADXExcelAsyncCallObject type; it must be the last parameter in the declaration. Here is an example:
The ADXExcelAsyncCallObject class is responsible for providing Excel with the result of an asynchronous call. It also wraps a handle that Excel uses to identify any given asynchronous call. ADXExcelAsyncCallObject provides only one method:
This method returns false if Excel rejects the value passed to it. In our experience, this is always the case when calling this method for the second (and subsequent) time during the same call of the asynchronous UDF. In other words, calling this method in a loop doesn't make sense.
In the synchronous part of your asynchronous user-defined function, you call this method to return a value to Excel in case of error or if your UDF is called form the Insert Function wizard. In the asynchronous part, you call this method to return the calculation result or an error.
The asynchronous part of the UDF is what the synchronous part starts to perform the calculation and return the result. For instance, it can be a thread started by the BackgroundWorker class. Note that the asynchronous part must have access to the ADXExcelAsyncCallObject that the synchronous part receives from Excel. In case of the BackgroundWorker class, the synchronous part must pass the ADXExcelAsyncCallObject in the parameters of the RunWorkerAsync method.
The asynchronous part of the asynchronous UDF cannot perform any XLL-related calls!
When the asynchronous part completes or encounters an error, it invokes the ReturnResult method of the ADXExcelAsyncCallObject. Below is a schematic template of the asynchronous part when using the BackgroundWorker class:
The format of the RTD function isn't intuitive; the user prefers to call CurrentPrice("MSFT") rather than RTD("Stock.Quote", "", "MSFT", " Last"). You can do this by wrapping the RTD call in a UDF. Note that calling the RTD function in a UDF makes Excel refresh the cell(s) automatically so you don't need to bother about this.
In your Excel Automation add-in, you use the RTD method provided by the Excel.WorksheetFunction interface:
To access an RTD server in your XLL add-in, you use the CallWorksheetFunction method provided by AddinExpress.MSO.ADXXLLModule. This method as well as the CallWorksheetCommand method is just a handy interface to functions exported by XLCALL32.DLL. Here is a sample:
If you didn't find the answer to your questions on this page, please see the HOWTOs section: