Line Numbers in VBA Code?

I have a rather large VBA subroutine I wrote.  I am trying to split it up, but until then, there are a few areas that might encounter an error.  I'd like to know whereabouts in the module the error occurred.  

Mind you, this is distributed as an MDE, so the user cannot get to the code, even during an error.  Right now, I have it put up a screen, with the module name, and Access/Jet Error number. Is there a way to tell *where* in the module it happened?  (Of course, I can go back and devise where it occurred, based on ErrNo, but I'm lazy that way)....

Thanks in advance.
LVL 1
dougp23Asked:
Who is Participating?
 
n fConnect With a Mentor Commented:
As far as I know, you can't get the line number to be returned, but you can get around this by creating a local variable at the beginning of your sub and then changing it at specific points in your code.  Change the variable to tell you what's going on in your code at that point.

For example:
Sub MySub()
On Error GoTo errTrap

Dim errHere as string

errHere = "top"
start your code...
....
errHere = "updating tblOne"
more code...
....
etc.

Then in the error trap,
errTrap:
   Msgbox "Error number " & err.number & vbCr & _
       "(" & Err.Description & ") occured at " & _
       errHere
   Exit Sub


The more times you change the variable, the more specific you can be in where the error is occuring.
         

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Nothing outside of what was already metnioned as your using a MDE, which has no source code that can be read.  

Whatever labeling you do, you'll need to do it on your own.

Jim.
0
 
sraudaCommented:
Yep.  Use it all the time and it saves big time and headaches when debugging.

Try the following:

sub Test()

     dim x integer, y integer

05   on error goto Test_Err
10   x = 10
20   y = 0
30   msgbox x/y
40   exit sub

Test_Err:
    msgbox "Error: " & err.number & vbcrlf & "Description: " & err.description & vbcrlf & "Line: " & erl

end sub

Erl is an undocumented function in vb and vba.  If the function or sub has no line numbers, Erl returns 0.



0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sraudaCommented:
For vb, there is a line number generator, but I have yet to find one for Access.  Was thinking of writing one for myself.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
srauda,

<<For vb, there is a line number generator, but I have yet to find one for Access.  Was thinking of writing
one for myself. >>

  FMS, Inc. includes one as part of their Total Access Detective.

Jim.
0
 
dougp23Author Commented:
Thanks!  Even though I started out asking forline #s, I think this solution is cleaner.
As far as line # generator, the Access Developers Book (by Litwin, et. al.) has a nice line # generator (requires you to export the module to text first, then re-import it after it adds #s).

Like I said, though, I like nfeldman's idea.
Thank you all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.