none
Line numbering in VBA RRS feed

  • Question

  • Good day .............

     

    Somewhile back I got into the habit of manually line numbering my routines (in access) - yes just like it was in the 80's ... :)

    Only because it's seriously helpful in error trapping where reference to specific line numbers pops up in my trapping routine. (as a comment I am surprised by the number of apparent learned folk who ask the question why one numbers lines !!)

    I am now Fed up with doing it manually and would appreciated some answers to these questions :-

    1/ What verison of vba is used in access 2007 - is there a version?

    2/ Will the auto number utility MZTools VB6 IDE work with that version of vba - specifically in Access 2007

    3/ Or can anyone recommend any other efficient numbering utility?

    4/ Is there any downside to the auto numbering within routines ?

     

    Many thanks

     

    Regards

     

    db


    • Edited by dbdesign Sunday, September 25, 2011 11:09 AM
    Sunday, September 25, 2011 11:08 AM

Answers

  • What a pity it doesnt operate in access 2007 win 7 64bit - as that is where I operate from

    It should work on 32-bit Windows or 64-bit Windows - it is only 64-bit Office that it doesn't work with.
     

    Enjoy,
    Tony
    www.WordArticles.com
    • Marked as answer by dbdesign Wednesday, November 16, 2011 9:11 AM
    Monday, September 26, 2011 7:49 AM
  • Hi dbdesign,

    1. Access 2007 works with VBA6.

    2. Yes it will work in Office 2000-2010 32-bit, except Office 64-bit (as stated at their site).

    3. No, I don't know any other utility. MZTools is great and it is Freeware.

    4. I think its a personal choice, I dont see any downside to do it.

    The advantage of using this is for me, having the option to trap an Error at Line Number using the ERL () Function as described here:

    http://msdn.microsoft.com/en-us/library/ee358847%28v=office.12%29.aspx

     

    See below example from that article:

    Sub SampleErrorWithLineNumbers()
          Dim dblNum As Double
    10    On Error GoTo PROC_ERR
    
          ' Crashes if table doesn't exist
    20    Select Case Rnd()
            Case Is < 0.2
    30        dblNum = 5 / 0
    40      Case Is < 0.4
    50        dblNum = 5 / 0
    60      Case Is < 0.6
    70        dblNum = 5 / 0
    80      Case Is < 0.8
    90        dblNum = 5 / 0
    100     Case Else
          End Select
    110   Exit Sub
    
    PROC_ERR:
    120   MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _"Error: (" & _
                 Err.Number & ") " & Err.Description, vbCritical
    End Sub
    

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by dbdesign Sunday, September 25, 2011 4:40 PM
    Sunday, September 25, 2011 3:43 PM
    Moderator

All replies

  • Hi dbdesign,

    1. Access 2007 works with VBA6.

    2. Yes it will work in Office 2000-2010 32-bit, except Office 64-bit (as stated at their site).

    3. No, I don't know any other utility. MZTools is great and it is Freeware.

    4. I think its a personal choice, I dont see any downside to do it.

    The advantage of using this is for me, having the option to trap an Error at Line Number using the ERL () Function as described here:

    http://msdn.microsoft.com/en-us/library/ee358847%28v=office.12%29.aspx

     

    See below example from that article:

    Sub SampleErrorWithLineNumbers()
          Dim dblNum As Double
    10    On Error GoTo PROC_ERR
    
          ' Crashes if table doesn't exist
    20    Select Case Rnd()
            Case Is < 0.2
    30        dblNum = 5 / 0
    40      Case Is < 0.4
    50        dblNum = 5 / 0
    60      Case Is < 0.6
    70        dblNum = 5 / 0
    80      Case Is < 0.8
    90        dblNum = 5 / 0
    100     Case Else
          End Select
    110   Exit Sub
    
    PROC_ERR:
    120   MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _"Error: (" & _
                 Err.Number & ") " & Err.Description, vbCritical
    End Sub
    

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by dbdesign Sunday, September 25, 2011 4:40 PM
    Sunday, September 25, 2011 3:43 PM
    Moderator
  • Just to add, there are separate versions of MZTools for VB6 and VBA. To be clear the VB6 version will not work in Office VB editors, so get the VBA version.

    Peter Thornton

    Sunday, September 25, 2011 4:30 PM
    Moderator
  • Hi Daniel

     

    I thank you for your specific and  detailed non judgemental answer - how refreshing :)

    "Yes it will work in Office 2000-2010 32-bit, except Office 64-bit (as stated at their site)."

    What a pity it doesnt operate in access 2007 win 7 64bit - as that is where I operate from -

     

    I guess I'll just have to keep the old manual numbering routine going - development happens so fast it leaves not only me in the vapour trail but lots of others as well - tedious really - cest la vie :)

     

    I have my trap routines working well  - but thanks for your example -

     

    Thanks again

     

    db


    • Edited by dbdesign Sunday, September 25, 2011 4:37 PM
    Sunday, September 25, 2011 4:35 PM
  • Thank you Peter - I'll check it out

     

    db


    • Edited by dbdesign Sunday, September 25, 2011 4:45 PM
    Sunday, September 25, 2011 4:42 PM
  • What a pity it doesnt operate in access 2007 win 7 64bit - as that is where I operate from

    It should work on 32-bit Windows or 64-bit Windows - it is only 64-bit Office that it doesn't work with.
     

    Enjoy,
    Tony
    www.WordArticles.com
    • Marked as answer by dbdesign Wednesday, November 16, 2011 9:11 AM
    Monday, September 26, 2011 7:49 AM
  • Thought I would just update this -

     

    MZTools works like a dream - could not be more happy with it - one click and phroom 1800 line numbers - granted in 20 line gaps

     

    gerrit! you'll be sooooo glad you did!! :)

     

    thanks to all those who have helped with this and others

     

    db


    • Edited by dbdesign Wednesday, November 16, 2011 9:14 AM
    Wednesday, November 16, 2011 9:14 AM