How to know the line number in a Error handler routine

Question

Hi all,

How to know the exact line number with error in my vba code ?

For instance:

Public Function EXISTE_TBL(TBLNOME As String, acnnt As ADODB.Connection) As Boolean
 Dim catAG As ADOX.Catalog
 Dim tblAG As ADOX.Table
 ''On Error GoTo ERROT
 Set catAG = New ADOX.Catalog
 catAG.ActiveConnection = acnnt
 EXISTE_TBL = False
 Sleep 100
 catAG.Tables.Refresh
 Sleep 100
 For Each tblAG In catAG.Tables
  If tblAG.Name = TBLNOME Then
   EXISTE_TBL = True
   Exit For
  End If
 Next
 Set catAG = Nothing
 Set tblAG = Nothing
 Exit Function
ERROT:
 Set catAG = Nothing
 Set tblAG = Nothing
 MsgBox str(Err.Number) & Err.Description 
End Function

What I wished is something like Err.Line...

Is there a way to get this ?

Thanks

João

Answer 1

You need to number your lines of code like this:

Public Function GetPrecisionScale(strTable As String, strColumn As String) As Variant
      'Purpose  : Returns an array containing column name, Precision and Scale for a given Decimal column.
      'DateTime : 1/7/2013 13:23
      'Author   : Bill Mosca
      'Requires : ADOX library
          Dim cnn As New ADODB.Connection
          Dim cat As New ADOX.Catalog
          Dim col As ADOX.Column
          Dim strInfo As String
          Dim aryReturn As Variant

10        Set cnn = CurrentProject.Connection
20        Set cat.ActiveConnection = cnn

30        Set col = cat.Tables(strTable).Columns(strColumn)
          
40        strInfo = col.Name & "|" & col.Precision & "|" & col.NumericScale
          
50        aryReturn = Split(strInfo, "|")
60        GetPrecisionScale = aryReturn

70        Set col = Nothing
80        Set cat = Nothing
90        Set cnn = Nothing

End Function

Now you are probably thinking, "No way am I going to edit all my code!!"

You don't have to. Go to mztools.com and download the freeware mztools for VBA. It's aCOM add-in that will install in the code window. One of the features is to add line numbering. put your cursor in a procedure and click the line numbering icon in the mztools toolbar. Done!

You can remove the numbering just as easily.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

You need to number your lines of code like this:

Bill

I apreciate the tip about mztools.

But I did not understand how to get the line number through the Err object.

Referring to my code...

 

.
.
.
 Exit Function
ERROT:
 Set catAG = Nothing
 Set tblAG = Nothing
 MsgBox str(Err.Number) & Err.Description 
 MsgBox "Line: " & Err.??????
End Function

 How to get the line number in the handler ???

João

Answer 3

.
.
.
 Exit Function
ERROT:
 Set catAG = Nothing
 Set tblAG = Nothing
 MsgBox str(Err.Number) & Err.Description 
 MsgBox "Line: " & Err.??????
End Function

 How to get the line number in the handler ???

The Erl() function will give you the line number, if the failing line is numbered. It's not well documented, so see this link:

    http://www.fmsinc.com/free/NewTips/VBA/ErrorHandling/LineNumber.html

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

If I still had hair I would ripped it all now ...

Thank you guys

João

Answer 5

I worked with Access for over 10 years and have never needed to know a line number. If your need is for debugging, I'd learn how to use the debugger and single-step thru your code. You can also remove the ON ERROR statements and when an error occurs CTRL-BREAK right into the location of the failure and use the immediate window to display the values of variables. Alternatively, you can sprinkle Msgbox statement around the area where your code is failing to see where it has reached.