November 29, 2006

Dealing with CVErr Values in .NET – Part II: Solutions

The previous post, Dealing with CVErr Values in .NET – Part I: The Problem, discussed the difficulties we face when manipulating CVErr values such as #N/A when using .NET. If you have not read that article you should start there so that you have the appropriate background. This post will discuss how to overcome the obstacles and properly deal with CVErr values when using .NET as your platform.

The key to a solution can be found in the MSDN article Default Marshaling for Objects. The section titled “Marshaling System Types to Variant” shows how to pass, or “marshal,” a .NET Type that will be received by COM as a Variant holding a CVErr value. The Type that allows this marshalling to occur is the ErrorWrapper Class found in the System.Runtime.InteropServices namespace. This special class allows us to pass any CVErr value we wish from .NET to COM. In short, a .NET method can return an instance of the ErrorWrapper class which is marshaled to COM as a Variant holding a CVErr value. Perfect.

To create an example of this using VB.NET code, let’s start off by including an Imports statement so that we can access the ErrorWrapper class by name directly:
Imports System.Runtime.InteropServices

This Enum is a convenience for us so that we do not have to remember such large negative values.

We can then make our CVErr method:
Function CVErr(whichCVErr as CVErrEnum) _
As ErrorWrapper
Return New ErrorWrapper(whichCVErr)
End Function

Now that wasn’t too bad, was it? So let’s see it in action:
Dim rng As Excel.Range = xlApp.Range("A1")
rng.Value = CVErr(CVErrEnum.ErrNa) ' #N/A

This works as expected, assigning #N/A to the range “A1″. But as we know from our discussions in the previous post, we could have also placed a #N/A value in the range by assigning “#N/A” to the Range.Formula property. For example:
Dim rng As Excel.Range = xlApp.Range("A1")
rng.Formula = "#N/A"

So what is the advantage of our newly-created CVErr() method? The advantage is that we can now return CVErr values such as #N/A from a User Defined Function! Remember that User Defined Functions are not allowed to make assignments to the Range.Formula and so this .Formula = “#N/A” approach is not permitted from within a UDF. However, using our new CVErr() method, a UDF can now use code such as the following to return #N/A:
Return CVErr(CVErrEnum.ErrNa)

This would not be possible without the ErrorWrapper class found within System.Runtime.InteropServices, the creation of which we have wrapped within our CVErr() method. If you want to test this out yourself, once you have the CVErrEnum and the CVErr() method defined as we have above, you can then create the following UDF which takes an Integer 1 through 7 and returns one of the seven CVErr value results:
Function CVErrUDF(ByVal whichError As Integer) _
As Object
Select Case whichError
Case 1: Return CVErr(CVErrEnum.ErrDiv0)
Case 2: Return CVErr(CVErrEnum.ErrNA)
Case 3: Return CVErr(CVErrEnum.ErrName)
Case 4: Return CVErr(CVErrEnum.ErrNull)
Case 5: Return CVErr(CVErrEnum.ErrNum)
Case 6: Return CVErr(CVErrEnum.ErrRef)
Case 7: Return CVErr(CVErrEnum.ErrValue)
Case Else
Return "Oops! Input out of range!"
End Select
End Function

The UDF code above would have to be placed in a Managed COM Automation Add-in, within a class exposed to COM via the ComVisible and ClassInterface attributes, and registered with Regasm.exe. Then to test it, you could enter the following formula into a worksheet cell:
=CVErrUDF(2)

And the result returned is #N/A. So, using .NET, we can in fact return bona fide CVErr values either by using Automation code or via the result returned from a User Defined Function.

But what about reading CVErr values from .NET? For example, what if we had a UDF that summed up the values held within the cells of a range, as follows:
Function SumIt(rng As Excel.Range) As Object
Dim array2D As Object(,) = _
CType(rng.Value, Object(,))
Dim sum as Double = 0
For r As Integer = 1 To rng.Rows.Count
For c As Integer = 1 To rng.Columns.Count
sum += CType(array2D(r,c), Double)
Next c
Next r
Return sum
End Function

The function above is straight-forward and will return the correct sum so long as there are no #N/A, #Value! or other CVErr values present within the range passed in as an input. But if there are any CVErr values present, these would marshal themselves to .NET as large negative integers such as -2146826246 and would skew the summed results dramatically.

So how can .NET code discern if a COM method is returning a valid -2146826246 Integer result versus a Variant CVErr value? Unfortunately, both values are marshaled to .NET as Int32 values. On the surface, there would seem to be no way of telling the difference.

The article that helped us write CVErr values from .NET to COM, the MSDN article Default Marshaling for Objects, appears to confirm our worst suspicions. Within the section titled “Marshaling Variant to Object”, the article shows that a COM Variant holding a ‘VT_ERROR’, that is, a CVErr value, is marshaled to .NET as an unsigned UInt32. This value is then received by C# and VB.NET as signed Int32 values. There is no “ErrorWrapper” or any other Type that can help us here. Integers are all we get.

It seems as if we are dead in the water here. (But stay tuned, we are not…) So I proceeded to write up my thoughts, hoping that someone from Microsoft might notice one day and realize that we needed a little help.

My thinking on the matter was that the .NET Framework could change its approach regarding the coercion of COM Variant CVErr values from its current procedure of converting them to Int32 values, to instead convert CVErr values to an enumerated type. For example, the following would appear to be a .NET Framework compatible solution:
Enum CVErrEnum As Int32
ErrDiv0 = -2146826281
ErrNA = -2146826246
ErrName = -2146826259
ErrNull = -2146826288
ErrNum = -2146826252
ErrRef = -2146826265
ErrValue = -2146826273
End Enum

Having COM CVErr values marshaled to .NET as an enumerated type should be viable in a backward-compatible manner because converting from an Enum to Integer is a widening conversion. This means that any existing .NET code that might be checking for Integer values such as -2146826246 for #N/A or -2146826273 for #Value! would still be 100% valid, and would not even require a CType() or other conversion. All existing code would be 100% fine.

So the previous Int32 values are preserved, but callers that are aware of the new CVErr Enum could check for a CVErr such as #N/A with code such as the following:
Function IsNA(obj As Object) As Boolean
If TypeOf(obj) Is CVErrEnum Then
Return CType(obj, CVErrEnum) = CVErrEnum.ErrNa
End If
End Function

So my thinking was that the best that Microsoft could do within the current Framework, without breaking existing code, would be to create a custom Enum as described above. Unfortunately, as it currently stands, without such improvements, we as .NET programmers would seem to be incapable of distinguishing between Int32 values and CVErr values passed in from COM.

Or are we?

We are able to use the System.Runtime.InteropServices’s ErrorWrapper class to write CVErr values to COM. Is it actually impossible for us to read in CVErr values from COM with certainty? Well, in many cases it really is. There are no clues hidden within the Int32 that tells us on the .NET side of the fence what this value represents on the COM side. An Int32 is an Int32.

However, therein lies the clue…

Now I cannot speak for other COM servers. In fact, I am sure that in most cases, the trail goes cold right here. And unless Microsoft improves the .NET Framework to better marshal such values, there is no way for .NET code to properly identify and handle CVErr values that are passed in from COM.

However, as Excel programmers, we can discern the difference.

The key is that in Microsoft Excel, the Range.Value property cannot actually return an Integer data type – not on the COM side of the fence. Using a COM programming environment such as VBA or VB 6.0, Excel’s Range.Value property can only return a Variant holding either a Boolean, Currency, Date, Double, String or a CVErr value. (Note that .NET marshals the COM Currency data type as System.Decimal.) We can even narrow this further by making use of the Range.Value2 property, which can only return Boolean, Double, String or CVErr values. The point is that, on the COM side, neither of these properties can actually return an Integer Type. “Integer” values such as 0, 1, -1, etc. can be represented, of course, but they are returned from the Range.Value property as a Double data type. And this is the key.

So as Excel .NET programmers we can discern between large “Integer” results such as -2146826246 versus the equivalent CVErr value such as #N/A. The solution is to focus not on the magnitude of the value returned, but to test instead for the Type that is returned from COM to .NET. For example, if -2146826246 is returned by the Range.Value property to .NET as a Double data type, then the underlying value is in fact -2146826246. But if this same value is returned as an Int32, then we know with 100% certainty that the Range.Value result is in fact a COM Variant CVErr value.

With this knowledge in hand, we can construct the following VB.NET method which will return True only if the result passed in from a Range.Value result holds a CVErr value:
Function IsXLCVErr(obj As Object) As Boolean
Return TypeOf(obj) Is Int32
End Function

To see it in action, the following code opens up two dialog boxes, the first reporting “True” and the second reporting “False”:
Dim rngA1 As Excel.Range = xlApp.Range("A1")
Dim rngB1 As Excel.Range = xlApp.Range("B1")
rngA1.Formula = "#N/A"
rngB1.Value= -2146826246
MessageBox.Show(IsXLCVErr(rngA1.Value).ToString)
MessageBox.Show(IsXLCVErr(rngB1.Value).ToString)

The #N/A value is successfully read as a CVErr value while the value -2146826246 is not confused with a CVErr value. So we can distinguish between an actual CVErr value held by the range versus a negative number that .NET would otherwise identify as the “same.” Note that we’ve named this method “IsXLCVErr(),” that is, including “XL” in the name. This is because the assumption that an Int32 result necessarily implies a COM CVErr value does not generally hold. It is only when checking the values held within an Excel Range that we can be sure of this inference. So naming this method “IsCVErr()” would be very misleading if one is not using Excel, for this method is not guaranteed to give correct result in all circumstances. So be careful here.

We can improve our method to check for specific CVErr values. Utilizing the CVErrEnum that we defined earlier, we can overload the IsXLCVErr() method as follows:
Function IsXLCVErr(obj As Object) As Boolean
Return TypeOf(obj) Is Int32
End Function
Function IsXLCVErr(obj As Object, _
whichError As CVErrEnum) As Boolean
If TypeOf(obj) Is Int32 Then
Return CType(obj, Int32) = whichError
End If
End Function

To use it, let’s make an IsNA() method that returns True if the Range.Value passed in holds #N/A:
Function IsNA(obj As Object) As Boolean
Return IsXLCVErr(obj, CVErrEnum.ErrNa)
End Function

To see it in action, the following opens up a MessageBox reporting “True”:
Dim rng As Excel.Range = xlApp.Range("A1")
rng.Formula = "#N/A"
MessageBox(IsNA(rng.Value).ToString) ' True

Note that although our current version of IsXLCVErr() can check for a specific value such as CVErrEnum.ErrNa, which equals -2146826246, the main test that it is using is a simple test for if the TypeOf(obj) Is Int32. This test is enough to be 100% certain of the presence of a CVErr value when the value passed in comes from a Range.Value result. However, a safer approach would be for the method to also check the actual Integer value held by the argument, in addition to checking its Type. The following IsXLCVErr() version could protect against the caller passing in an Integer value from an invalid source:
Function IsXLCVErr(obj As Object) As Boolean
If TypeOf(obj) Is Int32 Then
Select Case CType(obj, Int32)
Case CVErrEnum.ErrDiv0, CVErrEnum.ErrNa, _
CVErrEnum.ErrName, CVErrEnum.ErrNull, _
CVErrEnum.ErrNum, CVErrEnum.ErrRef, _
CVErrEnum.ErrValue
Return True
Case Else
Throw New ArgumentException( _
"The 'obj' passed in is an Int32" & _
"from a non-COM source.")
End Select
End If
End Function
Function IsXLCVErr(obj As Object, _
whichError As CVErrEnum) As Boolean
If TypeOf(obj) Is Int32 Then
If CType(obj, Int32) = whichError Then
Return True
End If
End If
Call IsXLCVErr(obj) ' Throw Error if invalid input.
Return False ' Return 'False' if not invalid.
End Function

With our IsXLCVErr() method in hand, we can now refine our previous SumIt() UDF to throw an error if any CVErr values are found within the range of values to be summed:
Function SumIt(rng As Excel.Range) As Object
Dim array2D As Object(,) = _
CType(rng.Value, Object(,))
Dim sum as Double = 0
For r As Integer = 1 To rng.Rows.Count
For c As Integer = 1 To rng.Columns.Count
If IsXLCVErr(array2D(r,c)) Then
Return New ErrorWrapper(obj)
Else
sum += CType(array2D(r,c), Double)
End If
Next c
Next r
Return sum
End Function

The key change in the above from our previous SumIt() method is in this section:
If IsXLCVErr(array2D(r,c)) Then
Return New ErrorWrapper(obj)
Else
sum += CType(array2D(r,c), Double)
End If

What happens here is that if a CVErr value is detected, the loop immediately exits, returning the same CVErr value that is held in the cell. In this manner, #N/A error values will propagate as #N/A, and #Div0! error values will propagate as #Div0!, etc.

A caveat here is to be careful of passing a Range object into the IsXLCVErr() method instead of the value returned by the Range.Value property. For example:
Dim rng As Excel.Range = xlApp.Range("A1")
rng.Formula = "#N/A"
MessageBox.Show(IsXLCVErr(rng.Value).ToString) ' True
MessageBox.Show(IsXLCVErr(rng).ToString) ' False

The reason that IsXLCVErr(rng) returns ‘False’ is that an Excel.Range Type can never be an Int32 Type. That is, the test TypeOf(obj) Is Int32 will always return ‘False’. This could be confusing to the coder to find that IsXLCVErr() returns ‘False’ no matter what is held by the range. We should also be carful in how we handle multi-celled ranges, which have Range.Value return a two dimensional array as Object(,). And, lastly, we should consider protecting against passing in arbitrary types that could never be returned from the Range.Value property.

Taking the above into consideration, the following is a safer version of IsXLCVErr(). Take note of the check if TypeOf (obj) Is System.Decimal; this is necessary because the Currency Data Type in VBA/VB6 is converted to System.Decimal when it is marshaled to .NET. The following code also tests for ‘Nothing’ (or ‘null’ in C#) values because COM Variant Empty values are marshalled to .NET as ‘null’/’nothing':
Function IsXLCVErr(ByVal obj As Object) As Boolean
If TypeOf (obj) Is Int32 Then
Select Case CType(obj, Int32)
Case CVErrEnum.ErrDiv0, CVErrEnum.ErrNa, _
CVErrEnum.ErrName, CVErrEnum.ErrNull, _
CVErrEnum.ErrNum, CVErrEnum.ErrRef, _
CVErrEnum.ErrValue
Return True
Case Else
Throw New ArgumentException( _
"The 'obj' passed in is an Int32" & _
"from a non-COM source.")
End Select
ElseIf TypeOf (obj) Is System.Double OrElse _
TypeOf (obj) Is System.String OrElse _
TypeOf (obj) Is System.Decimal OrElse _
TypeOf (obj) Is System.DateTime OrElse _
TypeOf (obj) Is System.Boolean Then
' Valid Type, not a CVErr:
Return False
ElseIf obj Is Nothing Then
' Valid 'Empty' value; not a CVErr:
Return False
ElseIf TypeOf (obj) Is Excel.Range Then
Throw New ArgumentException( _
"Invalid: Range object passed in; " & _
"Pass in Range.Value instead.")
ElseIf TypeOf (obj) Is System.Array Then
Throw New ArgumentException( _
"Invalid: Array object passed in." & _
"Method valid for single-cell values only.")
Else
Throw New ArgumentException( _
"Agument type cannot be evaluated. " _
& vbCrLf & "Type: " & obj.GetType.Name.ToString)
End If
End Function
Function IsXLCVErr(obj As Object, _
whichError As CVErrEnum) As Boolean
If TypeOf(obj) Is Int32 Then
If CType(obj, Int32) = whichError Then
Return True
End If
End If
Call IsXLCVErr(obj) ' Throw Error if invalid input.
Return False ' Return 'False' if not invalid.
End Function

One could leave out these extra checks in order to have faster execution, but the developer (or you!) might then forget that proper usage for the IsXLCVErr() method is restricted to a Range.Value result held by a single cell. Without such protections, an invalid range, array or other inputs would result in a seemingly-valid ‘False’ result being returned, regardless of the value actually held. This has the potential for some very difficult-to-debug scenarios.

Another issue to be aware of is the assignment of Range.Value property values from one Range to another. Recall from our previous post (“The Problem”), the assignment of a #N/A value from one range to the other failed to operate as expected:
Dim rngA1 As Excel.Range = xlApp.Range("A1")
Dim rngB1 As Excel.Range = xlApp.Range("B1")
rngA1.Formula = "#N/A" ' rngA1.Value = #N/A
rngB1.Value = rngA1.Value ' rngB1.Value = -2146826246

In the above, range “B1″ fails to obtain the #N/A value held in Range “A1″, receiving -2146826246 instead. To handle this case, we can make a conversion method, which we can call the “CCom()” method to be consistent in nomenclature with VB.NET’s other Data Type converters such as CStr(), CInt(), etc. We’ll create such a method as follows:
Function CCom(obj As Object) As Object
If IsXLCVErr(obj) Then
Return New ErrorWrapper(obj)
ElseReturn obj
End If
End Function

And this time, our range “B1″ successfully receives a #N/A value, not -2146826246.

Now we have just one last case to consider. Keep in mind that Range.Value can return a two dimensional array if the range in question is a multi-celled range. (More specifically, Range.Value will return a two dimensional array if the Range.Areas(1) is a multi-celled range.) Additionally, although Range.Value cannot directly return a one dimensional array, some operations such as WorksheetFunction.Transpose() can return a one dimensional array, which if assigned to a Range.Value is interpreted to lie within a single row of cells. This means that we need to adjust our CCom() method to be able to convert not only single values to CVErr types as needed, but also to convert the elements of one dimensional and two dimensional arrays. The following VB.NET code does what we need:
Function CCom(ByVal obj As Object) As Object
If TypeOf (obj) Is Object(,) Then
Dim array2D As Object(,) = _
CType(CType(obj, Object(,)).Clone, Object(,))
For r As Integer = array2D.GetLowerBound(0) _
To array2D.GetUpperBound(0)
For c As Integer = array2D.GetLowerBound(1) _
To array2D.GetUpperBound(1)
If IsXLCVErr(array2D(r, c)) Then
array2D(r, c) = New ErrorWrapper( _
array2D(r, c))
End If
Next c
Next r
Return array2D
ElseIf TypeOf (obj) Is Object() Then
Dim array1D As Object() = _
CType(CType(obj, Object()).Clone, Object())
For i As Integer = array1D.GetLowerBound(0) _
To array1D.GetUpperBound(0)
If IsXLCVErr(array1D(i)) Then
array1D(i) = New ErrorWrapper(array1D(i))
End If
Next i
Return array1D
ElseIf IsXLCVErr(obj) Then
Return New ErrorWrapper(obj)
Else
Return obj
End If
End Function

Note that in the above, if passed in an array, the CCom() method is returning a clone, or a copy of the array. This is in order to be consistent with VBA/VB6 behavior where arrays are passed as copies when returned from methods and properties. For performance reasons one may instead wish to change this CCom() method to be a Sub (in C# this would be a Void method) and then have the method directly convert the elements of the array that is passed in, instead of first copying the array’s elements to a clone.

Also note that the code above is using Array.GetLowerBound() and Array.GetUpperBound() instead of assuming that this is a base-0 or base-1 array. This is because that while .NET arrays can be safely assumed to be base-0, arrays that originate from Range.Value are actually base-1. We could even throw an error if a base-0 array is passed in, that is, throw an error if the array was not returned from a Range.Value result, but I think that it is prudent for the routine to have a little flexibility in this matter. The code above is designed to be able to successfully handle base-0 and base-1 arrays in case the array passed in did originate from .NET or is base-0 for some other reason.

I hope that you have followed along and are now able to make use of these CVErr conversion methods in your own .NET Automation code and User Defined Functions. However, I am a bit mystified that this topic does not seem to have been addressed anywhere before. If someone finds another reference discussing this topic I would be much obliged if they could provide a link. But if there are no others yet out there, then I am happy to have gotten the ball rolling…

And I am most thankful to Dennis for letting me contribute as a guest author on his “.NET & Excel” blog. Thanks Dennis.

Hey Dennis,
Thanks for the compliments and for testing it out. Mostly I’m just glad it works! For a little while there I was scared that we had no way of dealing with CVErr values properly… I think that we .NET Excel guys squeaked through on this one by the skin of our teeth!
— Mike

What a fantastic atricle. I have a requirement to pass NAs betwwen my c# addin and Excel and had a rather clunky solution in place. I couldn’t believe the lack of documentation out there for this! But with your help I now have a very elegant solution indeed. : )

Yeah, this thing bowled me flat at first. And you are right, there really doesn’t seem to be any documentation on this anywhere. So at first I was pretty stumped and gave up. But then a day or two later, while in bed falling asleep, it hit me like a lightening bolt. (It’s always in bed or in the shower when this stuff happens, right?)

I then sent Dennis a giddy email with the solution, which he tested with success. I think we Excel programmers dodged a bullet with this one. Other .NET programmers working with other COM environments might not be able to disambiguate.

I think what MSFT needs to do here is to have the ErrorWrapper class work in both directions. That is, CVErr values should be marshaled from COM to .NET as ErrorWrapper classes not as Int32 values. Of course this would be breaking with existing code that might be checking for Int32 values, but I think they could solve this issue by creating a static CType() operator to convert an ErrorWrapper to an Int32 as a Widening conversion – and I guess ‘null’ would have to be converted as zero (0).

A less invasive way would be to use the CVErrEnum approach that I describe in the article, above. This works fine and does not break existing code. But I would personally prefer a symmetrical approach where the .NET code deals in ErrorWrapper classes exclusively as their version of CVErr, instead of having to hack around and test special cases.

First of all: thank You very much for this superb posting, it helped me a lot!

It works great for me in VB.net but I can’t get it to work in C#
I’m coming from VBA so I’m still in the choosing fase for VB.net or C#
So I did try to convert it to C# but the function always returns #VALUE!
I surely did something wrong here:

Since you are coming over from a VBA background, I think that you will definitely find VB.NET an easier migration than going straight to C#. Eventually, when you get better at .NET you could then move again to C#, but if your primary platform is MS Office, then I think that VB.NET is easiest and best.

For this article I wrote and tested VB.NET code only. Quite simply, I did not have the time to test both VB.NET and C# code. (Mia culpa.) I did, however, *assume* that the VB.NET code would simply convert to C# “as is” and run fine. It appears that I was wrong…

Looking over your code, I can see absolutely nothing wrong with it. So kicked it around and got the same exact #VALUE! result that you reported, so there is a problem in C#. Mind you, what is happening here is not that ‘Case 7′ is being chosen every time… What is actually happening is that there is a run-time error *somewhere* during the execution and Excel handles UDF run-time errors by forcing the UDF’s return value to be #VALUE.

The problem here turns out to be within the CVErr() method. In the article above I used the following VB.NET code, which runs without any trouble:

Private Function ReturnCVErr(ByVal whichCVErr As CVErrEnum) As ErrorWrapper
Return New ErrorWrapper(whichCVErr)
End Function

However, the above mysteriously fails. It almost looks too simple to fail, but it does…

So I took a look at the constructor method for the ErrorWrapper class and it has 3 overloads: The first takes an ‘Exception’ object, the 2nd takes an ‘int’ argument and the third takes an ‘object’. On the other hand, the ‘whichCVerr’ that the code is passing in is technically none of these (it’s an enum), so some implicit conversion is going on. One could only assume that an enum would implicitly convert to ‘int’ and then be properly handled by the ErrorWrapper’s constructor ‘int’ overload, but alas, it does not. In VB.NET this does happen smoothly and automatically. In C# the compiler does not complain, but at run-time the conversion does not occur as expected. My guess is that the conversion to ‘int’ is not occurring at runtime and so it is the ‘object’ overload of the ErrorWrapper’s constructor that is being called. But why this overload would fail, I’m not sure.

In any case, the solution is simple. We merely have to convert the enum to int explicitly:

Yes, 4 years old and still the definitive article on Excel error marshaling in VB.NET — which it will likely remain. It would be hard to do a more complete and accessible job than this.
A series of articles like this would rival works like Bullen, Bovey and Green’s Professional Excel Development for sheer usefulness.