Help raising an error from an Excel Object (e.g. Worksheet)

[keeena]

One of my workbooks features automation within Excel Objects; e.g. I
have added my own properties and methods to some Worksheet objects.

I'm noticing that raising errors from Excel Objects does not seem to
work as I intended. I'm using Excel 2003 and VBA. I searched the NGs
and couldn't find any information on raising errors w/in Excel object
modules.

' *Sample Code*
' Put this code w/in Sheet1 object

Private mRow As Long

Public Sub SetRow(ByVal Value As String)
On Error GoTo ErrProc
mRow = Application.WorksheetFunction.Match(Value, Me.Columns("A"),
0)
Exit Sub
ErrProc:
Err.Raise vbObjectError + 1001, ,"Release value could not be
matched in the table"
End Sub
' End Sheet1 code

' Put this code in Module1
Sub RunMe()
On Error GoTo ErrHandler
Sheet1.SetRow "ThisStringDoesNotExistInSheet1ColA"
MsgBox "Doing other stuff..."

ExitProc:
Exit Sub

ErrHandler:
MsgBox Err.Number & Err.Description

If Err.Number = vbObjectError + 1001 Then
Resume Next
Else
Resume ExitProc
End If

End Sub
' End Module1 code

' *End Sample Code*

I noticed that Err.Description changes based on the error number I
choose to use, which leads me to believe that there are additional
Error #'s defined which are unique to the MS Excel Objects (? or
something along those lines). I did try using larger error #'s as
well as removing vbObjectError...this didn't help.

I'd just like to know more details about why this is occurring and if
there is a way I could use err.raise to return my own errors..

Thanks,
-K

 

[merjet]

Years ago I tried using Err.Raise and never did fully understand it or
get it to work as expected or desired. I gave up and declared Public
variables mbErrorFlag (Boolean) and maErrorMsg (String) and worked
with those.

Hth,
Merjet

 

[NickHK]

It seems that it is because your calling routine is in a module. If you move
it to say Worksheet(2) with:

Private Sub CommandButton1_Click()

On Error GoTo Handler
Worksheets(1).SetRow "sometext"

Exit Sub
Handler:

MsgBox Err.Number & vbNewLine & Err.Source & vbNewLine & Err.Description

End Sub

It works as expected. Seems that you can only return an error to a class
module.
Never occurred to me before but I imagine it's related to the fact that you
cannot Dim a variable WithEvents in a non-object module.

NickHK

 

[keeena]

NickHK,
Thanks for sheding some light on the problem - I didn't realize there
would be a difference based on where the calling code was. Thats a
big help - I should be able to work around it.
-K