How to use Evaluate to invoke an Excel UDF programmatically
Whether your UDF is a VBA macro or an Excel Automation add-in or even an XLL add-in, you can invoke any method it provides to the user. To do this, you need to get or create an Excel.Application object and invoke ExcelApp.Evaluate() supplying it with the correct syntax for your method and its parameters.
What does this method return? It returns a Variant in VBA and Object in .NET. You can cast the return value to a proper type and save the result to a variable. But you must be prepared that Evaluate() may return a type that differs from the type that your UDF returns.
To prove the statement above, I tested the very simple XLL function:
public static string MyFunc() { return "OK"; }
The UDF returns a string. Let’s see however what occurs if you call Evaluate() in different ways.
| Return value | Return type | Return value | Return type | |
| ExcelApp.Evaluate(“MyFunc”) | -1104543735.0 | double | -1104543735 | Double |
| ExcelApp.Evaluate(“=MyFunc”) | -1104543735.0 | double | -1104543735 | Double |
| ExcelApp.Evaluate(“=MyFunc()”) | “OK” | string | “OK” | String |
| ExcelApp.Evaluate(“=MyFunc(“”ExcessParam””)”) | -2146826273 | int | Error 2015 | Error |
| ExcelApp.Evaluate(“=NonExisting()”) | -2146826259 | int | Error 2015 | Error |
Note. You’ll get one of the error values above if you call the UDF while it is still loading or isn’t registered.
In case your solution includes several Excel extensions such as described in HowTo: Create a COM add-in, XLL UDF and RTD server in one assembly, you’ll can invoke a method defined in the XLL add-in directly, not using ExcelApp.Evaluate(). Find details in the sample project available for download at the end of that post.
And keep in mind that a method in an XLL add-in can be hidden from the end user. It isn’t possible to achieve the same in an Excel Automation add-in.
Hope these bits were helpful.
Good luck!
You may also be interested in:
Developing Excel Automation add-ins step-by-step
Creating Excel XLL add-ins
Excel UDF tips and tricks
2 Comments
Hi,
Lots of good information. Thanks. Could you please be more specific regarding exactly which download contains the sample project that illustrates what you mention (…”you’ll can invoke a method defined in the XLL add-in directly, not using ExcelApp.Evaluate(). Find details in the sample project available for download at the end of that post.”)
Thanks.
Hello Adam,
Sorry for a confusing description. Navigate to https://www.add-in-express.com/creating-addins-blog/2010/03/24/addin-xll-rtd-one-assembly/ and see a link under the title “Available downloads”. HTH