How to return special values from function? (Excel 2007 + VS 2008)

[Tomasz Jastrzebski]

Hello developers,

I have a class like this one below, registered as COM server and added to
Add Ins.

[Guid("some guid here")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class Functions {
public object MyFunction() {
return ...
}
}

How do I return special values like #NAME!, #VALUE!, etc from my function?

Another question: how do I force Excel workbook to recalculate (F9) from my
VSTO Add In?

Thank you for any hints.

Tomasz

 

[Chip Pearson]

In VBA, there is a function named VBA.Conversion.CVErr that accepts a
numeric value indicating an error and converts it to a real Variant
Error object. The values you feed to CVErr are enumerated within the
Excel.XlCVError enum and have the values:

Excel.XlCVError.xlErrDiv0 = 2007 => #DIV/0!
Excel.XlCVError.xlErrNA = 2042 => #N/A
Excel.XlCVError.xlErrName = 2029 => #NAME?
Excel.XlCVError.xlErrNull = 2000 => #NULL
Excel.XlCVError.xlErrNum = 2036 => #NUM!
Excel.XlCVError.xlErrRef = 2023 => #REF
Excel.XlCVError.xlErrValue = 2015 => #VALUE!

The typelib for Excel is within the Excel exe file, so you can get the
enumerated values from the exe. The CVErr function resides in the VBA
DLL, typically

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

If you need to find the DLL in another location, take a trip through
the registry using the GUID {000204EF-0000-0000-C000-000000000046}.

Specify the return type of the function as an Object and then write
code in the caller to determine whether the returned value is an error
object or something else.

In VSTO, you get an reference to the host application at startup.
Store that reference is some global variable and then use that
reference to call the desired calculation. E.g,

// Declare
Excel.Application XLApp;

// Set
XLApp = HostApplication;

// Calculate
XLApp->Calculate; // or
XLApp->CalculateFull; // or
XLApp->CalculateFullRebuild;





Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Tim Li - MSFT]

Hello Tomasz,

I¡¯m not quite clear about you said ¡°return special values form myfunction
¡±, are you trying to

creating a UDF in managed code or you are trying to expose this class to
the other solutions

such as VBA.

If you are creating a UDF in managed code would you please talk about what
is your objective of

return special values like #NAME!, #VALUE! from MyFunction? Do you mean you
want to handle the

exception in MyFunction or something else?
I think I need more details about your scenario to clarify this question.

Due to my test the error occurs when call a UDF won¡¯t reach the managed
code, the break point in

managed side will never hit after you give a wrong type parameter when I
call MyFunction in

Excel. To work this around, I suggest you call your UDF(MyFunction) through
VBA, thus, we could

return the Error Code by using
VBA.Conversion.CVErr method which receives a parameter of Excel.XlCVError
type. About the

details of Excel.XlCVError you could refer to this link:
http://msdn.microsoft.com/en-

us/library/microsoft.office.interop.excel.xlcverror(office.11).aspx

Following sample demonstrate a usage of CVErr method:

Public Function TestErr(TestPara As Variant) As Variant
TestErr = CVErr(xlErrNA)
End Function

To call UDF in managed code from VBA please follow the steps in this link:
http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx

If you are trying to expose Functions class, please follow this walk
through article in MSDN:
http://msdn.microsoft.com/en-us/library/bb608614.aspx
Briefly, Functions class need to inherits a interface you defined, this
interface will be used

to interop with VBA.

To your last question ¡°Another question: how do I force Excel workbook to
recalculate (F9) from

my
VSTO Add In?¡°
In Excel Object Model we could call Application.Calculate() method to force
Excel workbook to

recalculate, for more information about Calculate() method please refer to
this link:
http://msdn.microsoft.com/en-us/library/bb211549.aspx


Best regards,
Tim Li
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how

we can improve the support we provide to you. Please feel free to let my
manager know what you

think of the level of service provided. You can send feedback directly to
my manager at:[email protected].

 

[Tomasz Jastrzebski]

Thanks Chip,

So, if I correctly understand I have to return some object/struct returned
by the VBA.Conversion.CVErr function.
The question is how do I call this function from my C# written COM server?

Thanks,

Tomasz

In VBA, there is a function named VBA.Conversion.CVErr that accepts a
numeric value indicating an error and converts it to a real Variant
Error object. The values you feed to CVErr are enumerated within the
Excel.XlCVError enum and have the values:

Excel.XlCVError.xlErrDiv0 = 2007 => #DIV/0!
Excel.XlCVError.xlErrNA = 2042 => #N/A
Excel.XlCVError.xlErrName = 2029 => #NAME?
Excel.XlCVError.xlErrNull = 2000 => #NULL
Excel.XlCVError.xlErrNum = 2036 => #NUM!
Excel.XlCVError.xlErrRef = 2023 => #REF
Excel.XlCVError.xlErrValue = 2015 => #VALUE!

The typelib for Excel is within the Excel exe file, so you can get the
enumerated values from the exe. The CVErr function resides in the VBA
DLL, typically

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

If you need to find the DLL in another location, take a trip through
the registry using the GUID {000204EF-0000-0000-C000-000000000046}.

Specify the return type of the function as an Object and then write
code in the caller to determine whether the returned value is an error
object or something else.

In VSTO, you get an reference to the host application at startup.
Store that reference is some global variable and then use that
reference to call the desired calculation. E.g,

// Declare
Excel.Application XLApp;

// Set
XLApp = HostApplication;

// Calculate
XLApp->Calculate; // or
XLApp->CalculateFull; // or
XLApp->CalculateFullRebuild;





Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

Hello developers,

I have a class like this one below, registered as COM server and added to
Add Ins.

[Guid("some guid here")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class Functions {
public object MyFunction() {
return ...
}
}

How do I return special values like #NAME!, #VALUE!, etc from my function?

Another question: how do I force Excel workbook to recalculate (F9) from
my
VSTO Add In?

Thank you for any hints.

Tomasz

 

[Tomasz Jastrzebski]

Hi Tim,

I have a COM server written in C# containing UDFs I want to use in my Excel
formulas.
In special cases my functions should return special values, analogously to
Excel build-in functions. Yes, it is part of error handling.

Because of deployment reasons I'd rather avoid creating another "pure VBA"
wrapper around my C# written COM server - if I understood you suggestion
correctly.

My function class (technically a COM server) is already properly written,
exposed and working.

The only remaining part is how to return those special values.

Thanks,

Tomasz

 

[Tim Li - MSFT]

Hello Tomasz,

I have researched this question for another couple hours, but the result is
still we need to return the special code in VBA side, have you considered
directly return string from UDF?
As far as I know VBA is the only way, I'll continue my study if there's any
new clew I'll keep you updated.

Best regards,
Tim Li
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).