Programming Excel with VBA and .NET

Respond to Errors and Events from .NET Objects

The .NET code in the preceding section included a couple of 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 Visual Basic .NET’s new exception-handling constructs: Throw raises an exception, the error is created as a New Exception object, and it would be handled by a Try...Catch structure (not shown) if the method were called from .NET.

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 exception that 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 in the Immediate window.

Handling events from .NET components in Excel VBA is much the same as handling events from Excel objects: declare the object variable WithEvents at the module level of an Excel class, initialize ...