• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1892
  • Last Modified:

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.
0
dougp23
Asked:
dougp23
1 Solution
 
n fCommented:
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)PresidentCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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)PresidentCommented:
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now