Section 5.4.Respond to Errors and Events from .NET | Excel 2003 Programming: A Developers Notebook (Developers Notebook)

5.4. 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.

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

Handling events from .NET components in 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 the object, and respond to the event in an event-hander procedure. For example, the following code defines and raises an event in the .NET NetString class:

    ' .NET code    Public Class NetString        ' Declare event.        Public Event Sorted As EventHandler        Private m_value As String        ' Other code omitted.        Public Function Sort(ByVal arg As String, _          Optional ByVal ascending As Boolean = True) As String            ' Declare an array.            Dim ar(  ) As String, res As String            ' Break the string up and put it in the array.            ar = arg.Split(" "c)            ' Sort the array.            ar.Sort(ar)            ' Reverse the order if requested.            If Not ascending Then ar.Reverse(ar)            ' Convert the array back to a string and set value property            m_value = String.Join(" ", ar)            ' Raise event.            OnSorted(  )            ' Return result            Return m_value        End Function        ' By convention, events are raised from OnXxx procedures in .NET        Friend Sub OnSorted(  )            RaiseEvent Sorted(Me, System.EventArgs.Empty)        End Sub       ' Property that returns Sort result (added to illustrate event).       Public ReadOnly Property Value(  ) As String            Get                Return m_value            End Get        End Property    End Class

The preceding event occurs any time the Sort method completes a sort. This actually occurs very quickly, so this isn't the greatest use for an event but it's clearer to build on this previous example than to start a completely new one. To handle this event in Excel, add the following code to the class for a worksheet:

    ' Excel code in a worksheet class.    Dim WithEvents NetStr As NetForExcel.NetString        Private Sub Worksheet_Change(ByVal Target As Range)        If Target.Address = "$A$2" Then            ' Create object if it hasn't been initialized.            If TypeName(NetStr) = "Nothing" Then _              Set NetStr = New NetForExcel.NetString            ' Sort text in range A2.            NetStr.Sort [a2].Text        End If    End Sub    Private Sub NetStr_Sorted(ByVal sender As Variant, _      ByVal e As mscorlib.EventArgs)        ' When sort is complete, display result in range B2.        [b2].Value = NetStr.Value    End Sub

5.4.2. How it works

Now, you can change the text in cell A2 and the Sorted event displays the result in cell B2 once the sort is complete. There are a couple of points to note here:

VBA can only respond to events from within classesthat includes workbook and worksheet classes, as well as custom classes (.NET calls these instance classes ). You can't use events from modules (.NET calls these static classes or code modules ).
Once you declare a .NET object WithEvents, that component's events appear in the listbox at the top of the VBA editor code window.
You can't combine New and WithEvents, so you must initialize the object somewhere in a procedure (as shown previously in the Worksheet_Change procedure).