Displaying the line of code that caused the error

[David9746]

I have some sub procedures that I have error trapping code in and would like
to know how I can display the line of code where the error ocuured in the
error message rather than having the code break and highlighting the line
that caused the error.

 

[Allen Browne]

VBA does not give you the line of code that caused the error.

It is possible to add labels to the code, and get the most recent label. For
example:
10 On Error Goto Err_Handler
20 Dim a as Integer
30 Debug.Print a / a 'Divide by zero error
40 End

You can then test ERL in your error handler to get the most recently
encountered label for the error (30 in the example above.)

However, that is a serious performance hazard. A far better routine is to
identify any line where an error is likely to occur, and break it out into a
separate procedure. There you can handle the specific problem part of the
code in a very specific way, and you don't have the nightmares about the
multiple points in the main procedure where an error is likely.

For example, if your procedure is uncertain whether there is a table named
"Table1" in the database. If you just refer to the table it will error.
Therefore you write a separate little routine to test if the table exists,
and call that instead of doing it in the main routine. Sample of the little
routine that could error:
http://allenbrowne.com/MacroInFormReport.html#TableExists

Similarly if you want to set a property of an object using DAO, many
properties don't exist if they are not set, so you get error 3270 ("Propert
not found".) It therefore makes sense to have a separate function to test if
the property exists, and create it if necessary, like this SetPropertyDAO()
function:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO

 

[James A. Fortune]

For example, if your procedure is uncertain whether there is a table
named "Table1" in the database. If you just refer to the table it will
error. Therefore you write a separate little routine to test if the
table exists, and call that instead of doing it in the main routine.
Sample of the little routine that could error:
http://allenbrowne.com/MacroInFormReport.html#TableExists

Similarly if you want to set a property of an object using DAO, many
properties don't exist if they are not set, so you get error 3270
("Propert not found".) It therefore makes sense to have a separate
function to test if the property exists, and create it if necessary,
like this SetPropertyDAO() function:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO

I think that checking to see if the table exists and creating the
property if it doesn't exist are wise ideas.

How about this alternative for your HasProperty() function (untested):

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim boolFound As Boolean
Dim prp As Property

boolFound = False
For Each prp In obj.Properties
If prp.Name = strPropName Then
boolFound = True
Exit For
End If
Next prp
HasProperty = boolFound
End Function

James A. Fortune
(e-mail address removed)

 

[Allen Browne]

Not sure I understood that suggestion.

Are you saying that looping through all the properties of an object to see
if you can find one that matches the name would be more efficient than just
trying the name to see if it works?

 

[James A. Fortune]

Not sure I understood that suggestion.

Are you saying that looping through all the properties of an object to
see if you can find one that matches the name would be more efficient
than just trying the name to see if it works?

I'm not necessarily trying for ultimate efficiency here, but even trying
to match a name as you suggest would cause Access to do the same thing
anyway, would it not? Looping through the names provides a nice clean
way to look for the property without raising an error unnecessarily.
I'm not sure how much extra time raising the error would contribute to
the total execution time in cases where the property does not exist. I
simply try to avoid raising errors when I don't have to, even if it
takes a little more code or takes a slightly longer -- which I am not
sure about in this case. I understand that many programmers do not
share my viewpoint. In my experience, this has caused me to create code
that is usually cleaner, more robust and more proactive than the
"negative" method of raising an error when a call fails.

James A. Fortune
(e-mail address removed)