Error description - VBA code

[davidtrickett]

Does anyone know how I can identify/refer to an error description that isn't in the trappable errors list? For example if I try to open a non-existent workbook it returns error number 1004. At runtime the Err.description is "'thisworkbook.xls'" could not be found" etc., but the description attached to err no 1004 is "Application-defined or object-defined error".

This is for an application on a large network - I need to create an error log so that I can identify problems - obviously something more helpful than "Application-defined..." would be handy. BTW I don't have enough room in the log file to record a lengthy description - only some sort of code number.

Thankyou

 

[TommyGun]

Error 1004 is one of the most frequent errors in VBA. Would it be possible to store info in your log file based upon what was happening when your error occured, or are you just trapping errors in general?

 

[Mudface]

Error 1004 is just a generic catch-all code for when a specific error code is not available. It's not entirely clear what you're doing, but code similar to the following might help. You could assign the Err.Description to a string and use say, the first 50 characters in your logbook maybe?

Public Sub Test()

On Error GoTo ErrTrap

Workbooks.Open "C:\magpie.xls"

Exit Sub

ErrTrap:
MsgBox Err.Description

End Sub

 

[davidtrickett]

TommyGun & Mudface

Thanks for your response.

The problem is that the code is running on a cranky NT network, which from time to time decides, for example, that a workbook the code is trying to open ain't there - although it is! I could store the error description returned at runtime, but the length of the description, e.g.

"www.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted."

is too long for the log file (there are other constraints on this).

The trappable errors are fine - e.g. err 52=Bad file name or number, but our network is very unpredictable and can return 1004 quite happily!

 

[Mudface]

Well like I said you could store the description in a string and just truncate it. How many characters can you store in the logfile?

 

[davidtrickett]

18!

 

[Mudface]

Well, about the best you could do would be something like this-

Public Sub Test()
Dim strErr As String

On Error GoTo ErrTrap

Workbooks.Open "C:\magpie.xls"

Exit Sub

ErrTrap:
strErr = Left(Err.Description, 18)
MsgBox strErr

End Sub

The only other way I can think of, other than increasing your log file size, would be to write some sort of parsing code and set up your own code numbers which would be a pretty long-winded affair given the possible number of errors that can be given the 1004 number.

 

[davidtrickett]

Yes - I know! I was just hoping to find something more helpful.

The idea of creating my own err 1004 list is a thought - but I would need to find all the possible messages for this error. Do you know if they are stored anywhere accessible?

 

[Ivan F Moala]

18!

Hi Dave 18?!
Why can you only store 18 characters ?
If you explain more we may be able to get something better ?

 

[davidtrickett]

Hi Ivan - thanks for responding (you're up late?)

The log file format is created by a "Type" declaration, which is in use for a number of other purposes. I know I could declare another Type - but this thing is already so infernally complex that I really want to avoid this! But if there is no other way round I guess I'll have to!