Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return special values from function? (Excel 2007 + VS 2008)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return special values from function? (Excel 2007 + VS 2008)
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) On Wed, 22 Jul 2009 19:42:57 +0200, "Tomasz Jastrzebski" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return special values from function? (Excel 2007 + VS 2008)
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/archiv...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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return special values from function? (Excel 2007 + VS 2008)
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: . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I move documents between Excel 2007 and 2008 | Excel Discussion (Misc queries) | |||
Function & paste special values | Excel Worksheet Functions | |||
2007 Excel Paste Special Values Deletes Comment | Excel Discussion (Misc queries) | |||
Excel 2003/2007 paste special values shows bizarre results | Excel Worksheet Functions | |||
Excel 2007 VB function call return value syntax question | Excel Programming |