Excel 2003 Programming: A Developer's Notebook

Respond to Errors and Events from .NET

The .NET code in the preceding section included a couple lines that may be unfamiliar to you:

   If Len(sep) <> 1 Then _
       Throw New Exception("Separator must be one character long")

And:

   If IsArray(arg) Then
       If arg.Rank <> 1 Then Throw New Exception("Array must have one dimension")
   Else
       Throw New Exception("First argument must be an array")
   End If

These lines demonstrate VB.NET’s new error-handling constructs: Throw raises an error (.NET calls these exceptions ), the error is created as a New Exception object, and would be handled by a Try...Catch structure (not shown) if the method was called from .NET.

Note

VB.NET and VBA use different error-handling constructs and terminology. You need to understand these differences to translate between the two.

How to do it

Since this code is called from Excel, however, you handle it using the VBA On Error statement. For example:

   ' Excel code.
   Sub TestNetError(  )
       Dim ar(1, 1) As String
       Dim NetStr As New NetForExcel.NetString
       ar(0, 0) = "causes": ar(0, 1) = "an": ar(1, 0) = "error"
       On Error Resume Next
       ' Cause error.
       Debug.Print NetStr.Join(ar)
       ' Catch and report error
       If Err Then
           Debug.Print "Error:", Err.Description
           Err.Clear
       End If
       On Error GoTo 0
   End Sub

If you run the preceding code, the Join method causes an error (in .NET you’d say it throws an exception ) which can be handled in Excel the same way as any other error. In this case, a message “Error: Array must have one dimension” is displayed ...