Add-in Express™
for Microsoft® Office and .netAdd-in Express Home > Add-in Express for Office and .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.
My Excel UDF doesn't work
You start finding the cause from Use the latest version of the loader.
If your UDF isn't shadd-ins in the registry.
Then you need to check the log file (see Loader's log) for errors.
If there are no errors but both .NET Framework 1.1 and 2.0 are mentioned in the log, read
Conflicts with Office extensions developed
in .NET Framework 1.1. Another typical problem is described in XLL and shared add-in support update.
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.
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
(see Returning values when your Excel UDF is called from an array formula).
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 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.
Say in your XLL, you can use the AddinExpress.MSO.ADXXLLModule.IsInFunctionWizard property to return a string describing the
actual return value
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:
private bool InFunctionWizard
{
get
{
return (ExcelApp.CommandBars["Standard"].Controls[1].Enabled == false);
}
}
Returning an error value from an Excel UDF
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 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 mModel in an XLL), some things are possible to do. Below is a sample 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.
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 UDV 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 one.
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 for Office and .NET homepage | 
