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

Next we’ll create a custom enumerated type:

  Enum CVErrEnum As Int32
    ErrDiv0  = -2146826281
    ErrNA    = -2146826246
    ErrName  = -2146826259
    ErrNull  = -2146826288
    ErrNum   = -2146826252
    ErrRef   = -2146826265
    ErrValue = -2146826273
  End Enum

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)
    Else
      Return obj
    End If
  End Function

We can then make use of our CCom() method as follows:

  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 = CCom(rngA1.Value) ' rngB1.Value = #N/A

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.

To see the improved CCom() method in action, you can run the following:

  xlApp.Range("A1").Formula = "#Div0!"
  xlApp.Range("A2").Formula = "#N/A"
  xlApp.Range("A3").Formula = "#Name"
  xlApp.Range("A4").Formula = "#Null!"
  xlApp.Range("A5").Formula = "#Num!"
  xlApp.Range("A6").Formula = "#Ref!"
  xlApp.Range("A7").Formula = "#Value!"
  xlApp.Range("A8").Value = -2146826246
  xlApp.Range("A9").Value = 100
  xlApp.Range("A10").Value = """Hello"""

  Dim rng1 As Excel.Range = xlApp.Range("A1:A10")
  Dim rng2 As Excel.Range = xlApp.Range("B1:B10")

  Rng2.Value = CCom(Rng1.Value)

Give it a try, I think that you will be pleased with the results. 🙂

Closing

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.

Happy coding everyone :),
Mike

Dealing with CVErr Values in .NET – Part I: The Problem

Introduction:

First of all, I want to thank Dennis for setting me up as a guest author on his “.NET & Excel” blog, which I think is off to a fantastic start. I’m honored to be able to contribute.

I didn’t really chose this topic “Dealing with CVErr Values in .NET,” it chose me. I was experimenting with some User Defined Functions (UDFs) created using a Managed COM Automation Add-in and discovered some serious incompatibilities between Excel’s error values, such as #Value! or #N/A, and .NET’s ability to read and write such values.

I searched using Google extensively, checked for threads on MSDN’s VSTO Forum and even looked specifically for blog posts by Carter, Lippert and/or Whitechapel… but I could find nothing. Dennis searched through the newsgroups and found only one highly-technical thread, which did not appear to resolve the problem.

So I had to go it alone… And the following are my thoughts on some of the issues involved with CVErr values when using .NET and Excel. This turned out to be a little long, so it has been split into two parts:

(1) Dealing with CVErr Values in .NET – Part I: The Problem
(2) Dealing with CVErr Values in .NET – Part II: Solutions

In this post I’ll discuss some of the key issues/problems with CVErr values when accessed via .NET and then in the next post we’ll see what options we have for dealing with them.

Background:

A CVErr value is a special Variant value that can be used in the Component Object Model (COM) to indicate an error. It is a method of “returning an error value” as opposed to “raising an error.” For more on this subject you can read about the CVErr() Function in Microsoft Office documentation. What is important for us to note is that this approach to error handling is now obsolete and is not included in .NET Framework.

However, as Excel VBA programmers know, Microsoft Excel uses CVErr values to indicate cells holding error values such as #Value!, #N/A or #Div0!. For example, Excel has an enumerated type named xlCVErr within which the field xlErrNa is defined as an Integer value of 2042. Using VBA or Visual Basic 6.0, a call to CVErr(2042) or CVErr(xlErrNa) returns an N/A error value that can be held within a Variant. When displayed on the spreadsheet, the result is “#N/A” (without the quotes).

Using VBA or VB 6.0 there are a few different ways available to us for assigning a CVErr value to a range. For example, if we define a range as follows:

  Dim rng As Excel.Range
  Set rng = xlApp.Range("A1")

Then the following two lines in VBA or VB6 would both successfully assign #N/A to the range “A1”:

  rng.Value = CVErr(xlErrNa)
  rng.Value = CVErr(2042)

But note that in Excel, we also have a couple of additional “tricks” available to us in order to assign a CVErr value. For example, the following two Range.Formula property assignments also result in the range holding a #N/A value:

  rng.Formula = "=NA()"
  rng.Formula = "#N/A"

The first assignment “=NA()” works because the formula evaluates using the =NA() worksheet function provided by Excel, which returns a #N/A value. The second assignment operates by assigning the formula “#N/A”, which directly evaluates to #N/A.

This all works as expected when working in a COM environment such as VBA or VB 6.0. However, in .NET, the concept of the CVErr values has been excluded. And since the CVErr values present in VBA/VB6 are special Variant values that are not part of an enumerated type, they cannot be directly passed to or from .NET. Instead, when passed from COM to .NET these CVErr values are converted to unsigned UInt32 values which C# and VB.NET both interpret as signed Int32 values. For example, when using C# or VB.NET, #N/A is converted to the Integer value of -2146826246 and #Value! is converted to -2146826273.

And this can lead to some rather unintuitive results. For example, observe the following VB.NET code:

  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

The result of the above is that that the rngA1 will hold #N/A, but that rngB1 will hold -2146826246 instead! What happens is that the #N/A value returned by rngA1.Value is coerced to an Integer by .NET and then this Integer is assigned to the rngB1.Value.

So now we have the situation where even a simple Range.Value assignment no longer works properly. Errors won’t propagate as #N/A values. Wildly incorrect results can be created as .NET code converts CVErr values to valid, large negative integer results. This is not good…

Here is a table of the seven CVErr values:

Excel.xlCVErr  Range.Value  Coerced to .NET
-------------  -----------  ---------------
    2000         #NULL!       -2146826288
    2007         #DIV/0!      -2146826281
    2015         #VALUE!      -2146826273
    2023         #REF!        -2146826265
    2029         #NAME?       -2146826259
    2036         #NUM!        -2146826252
    2042         #N/A         -2146826246

Note that the calls to CVErr(2042) or CVErr(xlErrNa) cannot be made within .NET, for the CVErr() method is not included within the Microsoft.VisualBasic namespace. And if they could be made, the results would not be interpretable by .NET in any case. In .NET, such a method could only return an Integer value such as -2146826246 for #N/A or -2146826273 for #Value!, etc.

In addition, the IsError() function, which in VB6/VBA is designed to return True if a Variant holds a CVErr value or Missing, has been completely changed in .NET to now check if an object passed in inherits from the System.Exception class. This is so misleading compared to its previous functionality within VBA/VB6 that I think it should have been removed as was IsMissing(), IsNull(), etc.

Preliminary Thoughts:

Ok, so what can we do about it? The answer is that it’s not easy. My initial solutions were clunky, imperfect and/or required a COM intermediary – either a COM DLL or VBA project – acting as a crutch. Fortunately there is a solution to this conundrum, which I’ll get to in the next post, but I think it’s worth discussing some of the preliminary approaches just to get a sense of how difficult this issue can be to the uninitiated.

As an example, consider the situation where we wish to sum all the cell values in a range. Generally, we can do so efficiently by assigning the Range.Value to a two dimensional Object(,) array and then iterating through the array’s elements. The following VB.NET code illustrates:

  Dim rng As Excel.Range = xlApp.Range("A1:Z100")
  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

  Message.Show(sum.ToString)

The above runs just fine… Or does it?

What if we wished to throw an exception if we encountered a #N/A or any other CVErr value? Or what if we wished to quietly ignore all error values and only sum valid numeric data? Directly speaking, we can not, not with .NET. No CVErr values could ever come through the assignment of Dim array2D As Object(,) = rng.Value. Instead, CVErr values such as #N/A would be converted to large negative values such as -2146826246. And these values would have a disastrous effect on the summed result.

So what can be done? Here are a few initial options that come to mind:

The “IsProbablyCVErr” Approach

It would seem that the fastest and easiest approach when using .NET to test for CVErr values is to check for the seven specific CVErr results such as -2146826246 for #N/A, -2146826273 for #Value!, etc. In essence, we could create an IsProbablyCVErr() method that returns True for any of the key values into which a CVErr can be coerced when marshaled from COM to .NET:

  Function IsProbablyCVErr(ByVal obj As Object) _
      As Boolean
    Const ErrDiv0 As Integer  = -2146826281
    Const ErrNA As Integer    = -2146826246
    Const ErrName As Integer  = -2146826259
    Const ErrNull As Integer  = -2146826288
    Const ErrNum As Integer   = -2146826252
    Const ErrRef As Integer   = -2146826265
    Const ErrValue As Integer = -2146826273

    Select Case CType(obj, Integer)
      Case ErrDiv0, ErrNA, ErrName, ErrNull, _
           ErrNum, ErrRef, ErrValue
        Return True
      Case Else
        Return False
    End Select
  End Function

Probabilistically this is a good bet, and is our fastest-executing solution. And the odds of these large negative integer results occurring due to a legitimate non-error calculation are remote. On the other hand, having the “remote chance” of a “very bad result” is understandably unacceptable to most, so what can we do?

To change the probability of an error from “unlikely” to have a problem to “impossible” we can attempt a few things:

The Test Range.Value And Range.Text Approach

We can loop through the individual cells, checking if the Range.Value = -2146826246 and then check if Range.Text = “#N/A”. If both of these conditions are True, then this guarantees that we have a #N/A result. However, there are two problems with this idea. The first is that looping through the cells individually instead of looping through the two dimensional array that is returned from the Range.Value property is massively slower, on the order of 50-fold. The other issue is that this is not fool-proof since the Range.Text property can return “####” or the like if the column width is too narrow. In short, this approach will run slowly and is not guaranteed to work in all circumstances.

The Copy-Paste And Test Approach

A third approach is that we can copy the range to a hidden, temporary worksheet using Range.PasteSpecial(), pasting values only. Then one can create two different arrays returned by (a) tempRange.Value, and (b) tempRange.Formula. Given these two arrays we can then loop through the values array checking for -2146826246 and then we can confirm that this is in fact an actual #N/A value by checking if the respective formula holds the string value “#N/A”. This should operate very quickly and at first blush would seem to be our most efficient “fool-proof” solution. (Fortunately, we can actually do much better, as we will see in the next post.)

The problems we face are compounded when our code is operating from within a User Defined Function. UDF code operates under stricter conditions than does standard Automation code and so the situation becomes a little trickier. If one is using standard Automation code, that is, code which is not operating within a UDF, then the “Copy-Paste And Test Approach”, above, is the best idea we’ve considered so far. However, if calculating values of a Range from within a UDF then this approach cannot be used since the copy-paste operation would not be permitted.

Things get even more complicated when we wish to write a CVErr value such as #N/A from .NET to COM. If using Automation code, we can make use of the Range.Formula property in order to assign a CVErr value. For example, as we saw earlier, the following VB.NET code assigns #N/A to the range “A1”:

  Dim rng As Excel.Range = xlApp.Range("A1")
  rng.Formula = "#N/A"

However, what if we had a User Defined Function from which we needed to return a CVErr value such as #N/A? A UDF cannot make direct formula or value assignments. The UDF can only return a value which the calculation engine places within the caller range. But it does not appear that a UDF written in .NET could return a CVErr value because the .NET Framework does not even seem to know what a CVErr value is, converting them into Int32 values. We cannot have our UDF simply return an Integer and hope that it gets converted to a CVErr, can we? No, an Integer such as -2146826246 returned by a UDF would get stored as a large negative value in the cell, not as a CVErr value such as #N/A.

Do note that a User Defined Function created with .NET can force a #Value! result to be returned by intentionally throwing an exception. However, a #Value! result is generally used to connote an invalid input. If the inputs are valid, but there is no data at the desired data location, then a UDF should return #N/A. Unfortunately, if we need a UDF created in .NET to return #N/A, #Num! or other CVErr value, then we would seem to be completely dead in the water.

But there is hope. Stay tuned for the next post, “Solutions”…

— Mike

The Transition Case – COM Add-in Part II

Developing the DataTool COM Add-in with classic VB

Previously post on the subject:
The Transition Case – VBA Part I

Here in the second post on the subject we will take a closer look on how to create an unmanaged COM  Add-in with classic VB.

Except for the ‘transition discussion’ the downloadable file gives an input on how easy it is to ‘port’ a XLA project to a COM project. The major difference is that we need to change the strategy on how we implement the Hot Keys and also which Hot Keys to use.

Hot Keys in unmanaged COM Add-ins

In Excel it’s quite easy to add Hot Keys via the OnKey method of the Application object. When developing unmanaged COM Add-ins I usually don’t use Hot Keys due to the heavy use of Commandbar(s) and Menu option(s). However, a customer to me asked for some week ago if it was possible or not to add Hot Keys to unmanaged COM Add-ins. I remembered  that I read something that it was not doable.  But before giving a reply to the customer I checked it with my good friend Mike Rosenblum who pointed me to some posts he made on different forums last year on the subject. Unfortunately no solution was given so I decided to consult Dan Appleman’s Visual Basic Programmer’s Guide to the Win32 API where I found an API to start with:

‘Determine the state of the specified virtual key at the time
‘the function is called.
Public Declare Function GetAsyncKeyState Lib “user32.dll” _
(ByVal vKey As Long) As Integer

In the next stage I added a hidden form with a timer object to the project which didn’t work out well due to slow response. Next I added a reference to the CCRP’s High Speed Timer which gave a satisfied solution for the time frame I wanted.

The tests with Excel 2003 went well but I noticed some other issues in that:

• When running more instances then one of Excel 2003 the Hot Keys executed the same instruction in all instances.
• When running both Excel 2002 and 2003 the Hot Keys also executed the same instructions in all instances.

The solution to get the active instance of Excel ‘isolated’ so that the Hot Keys are only executed in that instance is to use subclassing. Subclassing with earlier versions of Excel like 97  2000 and 2002 is not doable due to slow response. It’s beyond the scope of this blogpost to dive deeper into subclassing and Excel. Anyway,  based on my tests it seems to work with Excel 2003 and 2007. I didn’t consider to create my own subclassing solutions as I have previously used msghook.dll with good result. Therefore I just added a reference to the msghook.dll in the project.

The following procedures are part of the Connection class and only the relevant code for the Hot Keys are showed:

 Option Explicit
‘The classes for Timer and SubClassing.
Private WithEvents Timer1 As ccrpTimer
Private WithEvents XLHook As MsgHook

‘Variable that hold the value if a running instance of Excel is
‘Activated (=True) or Deactivated (=False)
Private bFlagActivate As Boolean

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
            ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
            ByVal AddInInst As Object, custom() As Variant)

‘Create a new instance of MsgHook.
Set XLHook = New MsgHook
‘Subclassing
XLHook.StartSubclass CONST_XLWIND

‘Create a new instance of CCRP Timern.
Set Timer1 = New ccrpTimer

‘Manipulate the two most important properties.
With Timer1
    .Enabled = True
    .Interval = 0
End With

End Sub

Private Sub AddinInstance_OnDisconnection( _
        ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _
        custom() As Variant)

‘Stop the subclassing and release the variable.
XLHook.StopSubclass
Set XLHook = Nothing
‘Stop the timer and release the variable.
Timer1.Enabled = False
Set Timer1 = Nothing

End Sub

Private Sub Timer1_Timer(ByVal Milliseconds As Long)
If bFlagActivate Then
    ‘If the user use CTRL+ALT+C —– Chart report
    If GetAsyncKeyState(vbKeyC) Then
        If GetAsyncKeyState(vbKeyControl) Then
        If GetAsyncKeyState(vbKeyMenu) Then Create_Chart_Report
        End If
    End If    
   ‘If the user use CTRL+ALT+R —– Data report
    If GetAsyncKeyState(vbKeyR) Then
        If GetAsyncKeyState(vbKeyControl) Then
        If GetAsyncKeyState(vbKeyMenu) Then Create_Data_Report
        End If
    End If    
    ‘If the user use CTRL+ALT+P —– Pivot table report
    If GetAsyncKeyState(vbKeyP) Then
        If GetAsyncKeyState(vbKeyControl) Then
        If GetAsyncKeyState(vbKeyMenu) Then Create_Pivot_Report
        End If
    End If
End If
End Sub

Private Sub XLHook_BeforeMessage(uMsg As Long, wParam As Long, _
            lParam As Long, retValue As Long, Cancel As Boolean)
‘Check to see if the message from VMI is equal the message we are
‘looking for.
If uMsg = WM_ACTIVATEAPP Then
    ‘ The running instance of Excel is being activated.
    If wParam Then
        ‘Set the flag to True.
        bFlagActivate = True
    Else
        ‘The running instance of Excel is being deactivated.
        ‘Set the flag to False.
        bFlagActivate = False
    End If
End If
End Sub

The following picture shows the references for the COM Add-in case:

The project for the COM Add-in case can be downloaded here.

All comments are most welcome.

Edit note:
I’m consider to not create a shimmed managed COM Add-in for the case but before taking any decision please let me know what You think. The reason for my consideration is my published 9 articles about managed COM Add-ins.

Kind regards,
Dennis

VSTO 2005 SE

With Visual Studio.NET 2005 (VS.NET 2005) we can create standalone applications that automate and control Excel. We can also create so called managed COM add-ins, either based on the template ‘Shared Add-In’ or from scratch with VS.NET 2005.

The present main edition of Visual Studio Tools for the Office System (aka VSTO) can either be run as a standalone tool or as a ‘plug-in’ to the VS.NET 2005. This is important to know that it per se does not require that VS.NET 2005 to be installed, only the .NET Framework. If wanted, Office developer can develop .NET based solutions only with VSTO although it limits the target versions of the Office suite.

With VSTO 2005 (internal version 2.0) we can create document-level solutions (i e templates, files for specific tasks, smart documents and smarttags). It’s also possible to create application-level managed COM Add-ins with it but only for Outlook 2003 and later.  

In order to create any VSTO solutions we need to have the Professional Edition of Office 2003 or later installed on both the development machine and on the targeting machines. It also requires that the target machines have both the .NET Framework and the VSTO runtime installed. In my opinion VSTO is a development platform typical for enterprise solutions and where the requirements can be met via central controled IT-structures (especially the security aspects).  

In the end of the summer 2006 MSFT released the beta of VSTO 2005 SE (Second Edition) which I have explored together with Excel 2007. Today MSFT announced that the final release of it is now available for download.  It’s very important to understand that it does not replace the present version of VSTO 2005. It adds new functionality to VSTO 2005 to create application-level solutions (managed COM Add-ins) for all applications in the Office 2003 and 2007 suite with VSTO.

Before I downloaded the final release I read the following from the download page:

“Do not install VSTO 2005 SE on a computer that has more than one version of Microsoft Office installed.”

From a strictly enterprise point of view this is not remarkable in any way. After all, large worldwide enterprises usually run one version all over the world (at least in my experience). But for us small developers who are forced to work with several versions of Office (as well as different language versions too) it gets more difficult with VSTO with this kind of requirements.

If You already use VSTO and want to read more and download VSTO 2005 SE: VSTO 2005 SE

The above gives me the opportunity to put together a rather primitive but workable guideline when it comes to developing COM Add-ins for Excel:

# Use classic VB:  

• If the add-in target several versions in the range of 2000 – 2007 or all of them. 
• If You can’t afford to buy VS.NET 2005 or VSTO 2005 / 2005 SE and
• If the customers don’t explicit demand a managed COM Add-in solution.

# Use VS.NET 2005 or use VSTO 2005 + VSTO 2005 SE

• If the customers explicit demand a managed COM Add-in and
• If the add-in target one version of 2003 and 2007 or both.

As You can see I don’t make any differences between VS.NET 2005 and VSTO 2005/VSTO 2005 SE. Developing managed COM add-ins with VS.NET 2005 require that we ‘shim’ the solutions. VSTO already use a loader that doesn’t requires any ‘shimming’ but on the other hand it requires the VSTO runtime to exist on the target machines.

If we explicit target the areas of smart documents, smart tags and data bounded workbooks then VSTO is probably the best way to go.

The above guideline should not prevent us from learning and exploring both VS.NET and VSTO (which explain why this blog exist).

Finally, a comment on MSFT’s  “new” terminology. MSFT use the following terms:

• “Unmanaged COM Add-ins” and “COM Add-ins” are OK for me as they imply that they are developed with an older technology then .NET.
• “Managed COM Add-ins” is also OK for me as it implies that they are developed with a tool that requires .NET Framework.
• “Managed Add-ins” is used for VSTO 2005 SE solutions.  I totally disagree with the phrase because it imply that COM is not part of the communication between the host application(s) and the add-in(s). I prefer “Managed COM Add-ins”.
• “Add-ins” should only be used for add-ins created with the host applications.
   

Kind regards,
Dennis

Ps. I promise that in my next blogpost will take the ‘transition case’ further by showing the solution as a COM add-in developed with classic VB (that is VB 6.0).

.NET Books

Internet is for many individuals their number #1 source when it comes to information of all kind. It’s usually an inexpensive and a fast way to get wanted information and if not found then there exist a great number of Q&A forums.

Despite the easy access via Internet I still prefer to read printed books, especially when it’s about new areas of interest. For me the main advantages for using printed books are:

• They usually give more input on ‘why and when’ then just ‘how’ which is important to get a better understanding from a broader perspective.
• They have been reviewed both from a technical point of view as well as from a general view. Most publishers have an errata page for each book which is regular updated  and the code used in the book is available for download.
• I can bring the books with me where ever I want as they  are available without the demand of a computer.
• They allow me to make marks and comments whenever I want to do it.
• They have become less expensive for the last years (at least in Sweden).
• They can be sold in second hand.

When it comes to publishers within the field of programming I prefer the following:

• Addison Wesley 
• APress
• Wiley 
• Wrox

For me these publishers stands for high quality and offer many times books that are above the average level and also avoid ‘gracefully’ to try to cover ‘every aspects’ on the subjects.

Visual Basic.NET

Professional VB 2005 
Authors: Bill Ejven et al 
Publisher: Wrox
Pages: 1015
ISBN: 0-7645-7536-8
Target audience: Experienced classic VB/VBA developers

The book introduces the readers to .NET Framework, VB.NET, Security and Error handling, ADO.NET and ASP.NET. This book is good although it cover, in my opinion, to wide number of areas. I prefer books where the author(s) focus on few areas in order to get a deep on the subjects.

Pro VB 2005 and the .NET 2.0 Platform
Author: Andrew Troelsen
Publisher: Apress
Pages: 990
ISBN: 1-59059-578-5

Ever since I read Andrew Troelsen’s book ‘COM and .NET Interoperability’ I like his writing style and this one does not get me disappointed. Like the above book from Wrox it tends to cover too many areas but not as much as the one above.

If You consider which book to choose between these two books then I strongly recommend to select this book. Especially as it cover in one chapter (in an understandable way) COM and .NET Interoperability.

Pro .NET 2.0 Windows Forms and Custom Controls in VB 2005
Author: Matthew MacDonald
Publisher: Apress
Pages: 961
ISBN 1-59059-694-3
Target audience: Intermediate – Advanced

In many ways this is an excellent book on the subject and offer also two very good appendix about ‘Creating Usable Interface’ and ‘ClickOnce’.  It’s well written and very clear on many things and for me it’s the book that encourages me to really explore things with Windows Forms.

So if You believe You’re part of the main target audience then this book should be considered as a ‘must’.

Object-Oriented Programmning (OOP)

During all the years I’ve been around it has always been ‘hype’ around OOP and to the fact that classic VB/VBA don’t support Inheritance and Polymorphism. Despite the lack of it I can only conclude that many solutions created with classic VB/VBA works excellent and continue will do it.  The point here is that You don’t need to feel that it’s a must to learn everything about OOP in order to create workable solutions with VB.NET.

If You really want to learn OOP then I strongly suggest that You check out the following two books on the subject:

Visual Basic.NET Class Design Handbook (Coding effective Classes)
Authors: Damon Allison, Andy Olsen and James Speer
Publisher: Apress
Pages: 352
ISBN: 1-59059-275-1 
Target audience: Intermediate – Advanced

In my opinion this is The Book on the subject. They manage to ‘isolate’ the book to only deal with OOP which I highly appreciate, i e one book cover a specific area only. If You’re concerned on the topic then this book is highly recommended.

An Introduction to Object-Oriented Programming with Visual Basic.NET
Author: Dan Clark
Publisher: Apress
Pages: 396
ISBN: 1-59059-015-5
Target audience: Beginner – Intermediate

Clark’s book introduces the readers to the Unified Modeling Language (UML). In my opinion this can be an advantage as it offer a platform to learn OOP in a more structural and in a logic way.

What may be an annoying aspect to consider when it comes to buying book is that MSFT has a extremely high pace of new releases of .NET Framework and VB.NET. However, books can always be useful although they are not ‘state of the art’. 

What’s Your opinion about books and do You have any good books to recommend for the .NET – world?

In an upcoming blogpost I will present some books that explicit target Visual Studio Tools for the Office System (VSTO) and Add-ins. 

Kind regards,
Dennis