Excel user-defined functions (UDF)
and RTD tips for VB.NET and C#

Add-in Express™
for Microsoft® Office and .net

Add-in Express Home > Add-in Express for Office and .NET > Online Guide > Tips and notes > Excel UDF and RTD tips

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.

What Excel UDF type to choose?

Excel Automation add-ins are supported starting from Excel 2002; XLL add-ins work in Excel 2000 - 2019.

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.

  • Due to a bug in the 64-bit version of .NET Framework 2.0, your XLL add-ins developed in .NET Framework 2.0, 3.0 or 3.5 will crash Excel 64-bit; the workaround is to use .NET Framework 4.0. Excel Automation add-ins aren't affected by this issue.
  • When developing a combination of Excel extensions (see Developing multiple Office extensions in the same project), Add-in Express loads all of them into the same AppDomain. The only exception is the Excel Automation Add-in, which is loaded into the default AppDomain. You can bypass this by calling any public method of your Excel Automation add-in via ExcelApp.Evaluate(...) before Excel invokes the Automation add-in. ExcelApp.Evaluate(...) returns an error code if the Automation add-in isn't loaded; if it is the case, you need to call that method later, say in WorkbookActivate. We assume, however that this approach will not help in the general case. There's no such problem with XLL add-ins; they always load into the AppDomain shared by all Office extensions in your assembly.
  • An XLL add-in doesn't have a description. The description of an Automation add-in is the ProgId attribute of the Excel Add-in Module (of the ADXExcelAddinModule type). According to this page, ProgId is limited to 39 characters and can contain no punctuation other than a period.
  • On the other hand, neither functions nor their arguments can have descriptions in an Automation add-in. For using descriptions in an XLL add-in, see Step 4. Configuring UDFs. See also My XLL Add-in doesn't show descriptions.
  • You cannot hide a function in an Automation add-in. Moreover, in the Insert Function dialog, the user will see all public functions exposed by ADXExcelAddinModule, such as GetType and GetLifetimeService. In an XLL add-in, you hide a function by setting ADXExcelFunctionDescriptor.IsHidden=True, see Step 4. Configuring UDFs.
  • Only functions (=methods returning a value) are acceptable in an Automation add-in. An XLL add-in may contain a procedure (=method, the return type of which is void); you can hide it in the UI (see above) and call it from say, a COM add-in, via ExcelApp.Evaluate(...).
  • XLL add-ins provide access to low-level Excel features through the ADXXLLModule.CallWorksheetFunction method; this method is a handy interface to functions exported by XLCALL32.DLL. No such feature is available for Automation add-ins.
  • Automation add-ins cannot modify arbitrary cells; XLL add-ins may do this, see Can an Excel UDF modify multiple cells?

My Excel UDF doesn't work

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.

My XLL Add-in doesn't show descriptions

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. 


			  
			  Imports AddinExpress.MSO
...
Public Shared Function GetParameterNames(ByVal fName As String)
   Dim names As String = "not found"
   For Each comp As Object In _Module.components.Components
      If TypeOf comp Is ADXExcelFunctionDescriptor Then
         Dim func As ADXExcelFunctionDescriptor = comp
         If func.FunctionName.ToLower = fName.ToLower Then
            names = ""
            For Each desc As ADXExcelParameterDescriptor In _
               func.ParameterDescriptors
               names += IIf(desc.ParameterName Is Nothing, "", _
                  desc.ParameterName) + ";"
            Next
            names = names.Substring(0, names.Length - 1)
            names = names.Length.ToString() + "=" + names
         End If
      End If
   Next
   Return names
End Function

Public Shared Function GetParameterDescriptions(ByVal fName As String)
   Dim descriptions As String = "not found"
   For Each comp As Object In _Module.components.Components
      If TypeOf comp Is ADXExcelFunctionDescriptor Then
         Dim func As ADXExcelFunctionDescriptor = comp
         If func.FunctionName.ToLower = fName.ToLower Then
            descriptions = ""
            For Each desc As ADXExcelParameterDescriptor In _
               func.ParameterDescriptors
               descriptions += IIf(desc.Description Is Nothing, "", _
                  desc.Description) + ";"
            Next
            descriptions = descriptions.Substring(0, descriptions.Length - 1)
            descriptions = descriptions.Length.ToString() + "=" + descriptions
         End If
      End If
   Next
   Return descriptions
End Function

Can an Excel UDF return an object of the Excel Object Model?

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.

Why using a timer in an XLL isn't recommended?

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.

Parameterless UDFs?

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".

The 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:

Function Arguments dialog

That is, to bypass that issue, you need to leave the ADXExcelFunctionDescriptor.Description property empty.

Can an Excel UDF modify multiple cells?

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.

Can an Excel UDF return an empty cell?

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.

Using the Excel Object Model in an XLL

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
2. As the result of Excel's Function Wizard being called on to help with the XLL function
3. As the result of a VBA macro calling the Application.Run Automation method of Excel

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.

Determining the cell, worksheet or workbook your UDF is called from

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.

Determining if your UDF is called from the Insert Formula dialog

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!):

	  
			  
				private bool InFunctionWizard
				{
					get
					{
						return (ExcelApp.CommandBars["Standard"].Controls[1].Enabled == false);
					}
				}

Nevertheless, this code requires polishing since it leaves a number of COM objects non-released; please check Releasing COM objects.

Returning an error value from an Excel UDF

In XLL add-ins, you use AddinExpress.MSO.ADXExcelError. In Excel Automation add-ins, see AddinExpress.MSO.ADXxlCVError.

Returning values when your Excel UDF is called from an array formula

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:


// - select 3 consequent cells in a row, 
// - enter "=GetRow()"
// - press Ctrl+Shift+Enter
public static object[] GetRow()
{
    object[] retVal = new object[3] { 1, 2, 3 };
    return retVal;
}

// - select 3 consequent cells in a column, 
// - enter "=GetColumn()"
// - press Ctrl+Shift+Enter
public static object[,] GetColumn()
{
    object[,] retVal = new object[3, 1] { { 0 }, { 1 }, { 2 } };
    return retVal;
}

Returning dates from an XLL

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.

...
Imports AddinExpress.MSO
Imports System.Threading
Imports System.Globalization
...
Public Shared Function GetCurrentDate() As Object
    If Not _Module.IsInFunctionWizard Then
        Dim caller As ADXExcelRef = _Module. _
            CallWorksheetFunction(ADXExcelWorksheetFunction.Caller)
        'returns [Book.xls]Sheet1!$A$1 or [Book.xls]Sheet1!$A$1:$B$2
        Dim callerAddress As String = caller.ConvertToA1Style
        Dim range As Excel.Range = _Module.ExcelApp.Range(callerAddress)
        Dim oldCultureInfo As CultureInfo = _
          Thread.CurrentThread.CurrentCulture
        Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US")
        range.NumberFormat = "mm/dd/yyyy"
        Thread.CurrentThread.CurrentCulture = oldCultureInfo
        If caller.ColumnFirst = caller.ColumnLast And _
            caller.RowFirst = caller.RowLast Then
            Return System.DateTime.Today.ToOADate()
        Else
            Dim v(2, 2) As Object
            v(0, 0) = "The current date is"
            v(0, 1) = System.DateTime.Today.ToOADate()
            v(1, 0) = "A sample error value)"
            v(1, 1) = ADXxlCVError.xlErrValue
            Return v
        End If
    Else
        Return "This UDF returns the current date."
    End If
End Function

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.

Multi-threaded XLLs

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):

  • Don't read values of anuncalculated cell (including the calling cell);
  • Don't write any values to a cell;
  • Use ADXXLLModule.CallWorksheetFunction to call functions such as xlfGetCell, xlfGetWindow, xlfGetWorkbook, xlfGetWorkspace, etc.;
  • Don't define or delete XLL-internal names calling xlfSetName via ADXXLLModule.CallWorksheetFunction;
  • Use critical section when accessing thread-unsafe data such as static variables, etc.;
  • Don't make calls to thread-unsafe functions.

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.

No RTD servers in .exe

Add-in Express supports developing RTD Servers in DLLs only.

Update speed for an RTD server

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.

Asynchronous XLLs

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:

  • It checks input parameters
  • It returns a value to Excel if the UDF is called from the Insert Function Wizard
  • in case of error, it returns an ADXExcelError or any suitable value
  • it calls other XLL functions
  • it initiates an asynchronous operation and supplies it with parameters

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:


                public static void AsyncUdf(
                    object arg1,
                    object arg2,
                    ADXExcelAsyncCallObject asyncCallObject) {
                }

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:


                public bool ReturnResult(object value);

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:


                private void AsyncWork(object sender, DoWorkEventArgs e) {
                    ADXExcelAsyncCallObject asyncCallObject =
                        //retrieve the ADXExcelAsyncCallObject from e.Argument
                    try {
                        object result = //do dome job here
                        asyncCallObject.ReturnResult(result);
                    } catch (Exception) {
                        asyncCallObject.ReturnResult(ADXExcelError.xlErrorValue);
                    }
                }

Inserting the RTD function in a user-friendly way

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:

Public Function CurrentPrice(ByVal topic1 As String) As Object
    Dim wsFunction As Excel.WorksheetFunction = ExcelApp.WorksheetFunction
    Dim result As Object = Nothing
    Try
        result = wsFunction.RTD("Stock.Quote", "", topic1, "Last")
    Catch
    Finally
        Marshal.ReleaseComObject(wsFunction)
    End Try
    Return result
End Function

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:

Public Shared Function CurrentPrice(ByVal topic1 As String) As Object
    If Not _Module.IsInFunctionWizard Then
        Return _Module. _
            CallWorksheetFunction( _
                ADXExcelWorksheetFunction.Rtd, _
                "Stock.Quote", _
                Nothing, _
                topic1, _
                "Last")
    Else
        Return "This UDF calls an RTD server."
    End If
End Function

Useful resources

If you didn't find the answer to your questions on this page, please see the HOWTOs section:

Deploying and debugging tips <<

>> Troubleshooting tips