PDA

View Full Version : Solved: Multiple 'On Error' commands in function doesn't work?



Cosmo
07-16-2008, 07:49 AM
I have a function where I am using different 'On Error' commands to handle different errors. The first error handles properly, it goes to the line specified, but an error in that section (line 9) puts up a runtime error instead of going to the line specified for that error.
Public Function getDocProperty(ByRef docFile As Excel.Workbook, ByRef propertyName As String) As String
On Error GoTo checkBuiltInProperties
getDocProperty = docFile.CustomDocumentProperties(propertyName)
Exit Function
checkBuiltInProperties:
Err.Clear ' Clear Err object properties. (doesn't help??)
On Error GoTo 0 ' Reset Error. (doesn't help??)
On Error GoTo errorcode
getDocProperty = docFile.BuiltinDocumentProperties(propertyName)
Exit Function
errorcode:
getDocProperty = "Property '" & propertyName & "' missing"
End Function

Function testProperty()
Debug.Print getDocProperty(Application.ActiveWorkbook, "MyProperty")
End Function

If I comment out the 3rd and 4th lines, the error that was causing the problem works properly. I thought that clearing the Error, or resetting it (lines 6 & 7) would help, but they don't seem to do what I expected.

I know I can resolve the problem by setting a separate function for the second error trap, but I was just wondering if there was something I was doing wrong, or if there is a way to trap multiple errors in the same function.

Bob Phillips
07-16-2008, 09:47 AM
Public Function getDocProperty(ByRef docFile As Excel.Workbook, _
ByRef propertyName)
Dim propValue As Variant

On Error Resume Next
propValue = docFile.BuiltinDocumentProperties(propertyName)

If IsEmpty(propValue) Then

propValue = docFile.CustomDocumentProperties(propertyName)
If IsEmpty(propValue) Then

getDocProperty = "Property '" & propertyName & "' missing"
Exit Function
End If
End If

getDocProperty = propValue
End Function

Cosmo
07-16-2008, 10:18 AM
Excellent, thanks.

So, for future reference, is it not possible to use multiple error checks?