Andrei Smolin

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


Post a comment

Have any questions? Ask us right now!