Add-in Express™
for Microsoft® Office and .netAdd-in Express Home > Add-in Express .NET > Online Guide > 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.
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.
Can an Excel UDF change multiple cells?
Usually a UDF returns a single value. When called from an array formula, the UDF can return a properly dimensioned array. Changing arbitrary cells from a UDF may crash or hang Excel.
Using the Excel Object Model in an XLL
Microsoft guys say 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 what 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 Excel 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 Excel user-defined function. Say in your XLL, you can use the AddinExpress.MSO.ADXXLLModule.IsInFunctionWizard property to return a string describing the actual return value. Unfortunately, there's no way to do this in an Excel Automation add-in unless you find the wizard window via Win32 API.
Returning an error value from an Excel UDF
In Excel Automation add-ins, you use AddinExpress.MSO.ADXExcelError. In XLL 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.
Your Excel UDF in the registry
Registering a user-defined function adds a value to the following key:
HKEY_CURRENT_USER\Software\Microsoft\Office\{Office version}.0\Excel\Options
The value name is OPEN or OPEN{n} where n is 1, if another UDF is registered, 2 - if there are two other XLLs registered, etc. The value contains a string, which is constructed in the following way:
str = "/R " + "" + pathToTheDll + ""
XLL and shared add-in support update
If you develop an XLL in VS 2005 or VS 2008, you may need to add Shared Add-in Support Update (KB908002) to prerequisites of your setup project. While the article clearly states that it relates to VS 2005 only, it does apply to development of an XLL in VS 2008. To add the update to the Prerequisites dialog of VS 2008, install the update and copy the following folder C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\KB908002\ to the following one: C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages.
This adds Shared Add-in Support Update to the list of pre-requisites in VS 2008. If you do not have the source folder on your PC after installing the update, try finding the folder named KB908002 in your system. If this does not help, just download the archive containing that folder.
The update shows an unpleasant dialog whenever you install your XLL; alas, you have to live with this.
My UDF doesn't show descriptions
When the user enters a formula in the Formula Bar, neither Excel Automation add-ins nor XLL add-ins show function descriptions as well as descriptions of function parameters (available for XLLs only). Debugging this problem shows that Excel just doesn't call any methods responsible for providing that info.
Advanced XLL: 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 demonstrating the following aspects of XLL programming:
- Determining if your UDF is called from the Insert Formula dialog
- Determining what cell / worksheet / workbook your Excel UDF Is called from
- Returning values when your Excel UDF is called from an array formula
- Returning an error value from an Excel UDF
- It is safer to work with Excel in the "en-US" context. See also the following article on our technical blog: How to avoid “Old format or invalid type library” error in Excel.
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.
My Excel UDF doesn't work
You start finding the cause from Always use the actual version of the Add-in Express Loader. If your Excel UDF isn't shown in the Add-in Manager dialog, then it isn't registered - see Your Excel UDF in the registry.
Then you need to check the log file for errors. If there are no errors but both .NET Framework 1.1 and 2.0 are mentioned in the log, read Office extensions based on different .NET Framework versions. Another typical problem is described in XLL and shared add-in support update.
No RTD servers in .exe
Add-in Express currently supports Excel 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.
How to get actual parameters of the RTD function when using an asterisk in the string## properties of a topic?
Strings passed to the RTD function allow identifying the topic. That is their only purpose. When there is no topic corresponding to the identifying strings, Add-in Express creates a new topic and passes it to the RefreshData event handler of the topic containing an asterisk (*). Therefore, you need to cast the sender argument to AddinExpress.RTD.ADXRTDTopic and get actual strings.
Advanced RTD: Inserting the RTD function in a user-friendly way
The format of the RTD function (see Excel RTD Servers) 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
Note. If you didn't find the answer to your questions on this page, please see the HOWTOs section:
Back to Add-in Express .NET homepage |