Help raising an error from an Excel Object (e.g. Worksheet)
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 |
Help raising an error from an Excel Object (e.g. Worksheet)
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 |
Help raising an error from an Excel Object (e.g. Worksheet)
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" wrote in message oups.com... 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 |
Help raising an error from an Excel Object (e.g. Worksheet)
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 |
All times are GMT +1. The time now is 10:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com