View Poll Results: Who uses line numbers in their VBA code

Voters
29. This poll is closed
  • Yes, I use line numbers.

    3 10.34%
  • No, I don't use line numbers.

    26 89.66%
+ Reply to Thread
Results 1 to 62 of 62

Who uses line numbers in their code?

  1. #1
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Who uses line numbers in their code?

    Just curious as to if anyone uses them? I am doing a really big project right now and I need to create error logs and they are pretty much useless unless I get what line the error occured on. And in VBA the only way to get this with native tools is to number your lines.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    Not for 35 years or more. Maybe you could try "modular" programming.

    Hey Dave,

    This works:

    Please Login or Register  to view this content.
    Last edited by xladept; 01-17-2016 at 10:06 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Who uses line numbers in their code?

    Are you talking about code for editing modules?
    I have been experimenting with this of late,
    Please Login or Register  to view this content.
    But ,there is nothing that allows you to know what line you are on, UNLESS you have them number.

    Also, I think I left my brain at home, as I don't know what you are trying to show me with that code???

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    Well, Dave, I just put numbers on the lines and it looks like the VBA will still take line numbers. But, for modular, I meant you could write short routines or functions and just call them in order, to whit:

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Who uses line numbers in their code?

    I don't for vba, but if you use the mztools addin it's pretty trivial - one click to add them and one click to remove

  6. #6
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Who uses line numbers in their code?

    Quote Originally Posted by Kyle123 View Post
    I don't for vba, but if you use the mztools addin it's pretty trivial - one click to add them and one click to remove
    Hey Kyle,
    It is not the line numbers, I made a short piece of code to do that. It is the error trapping that I am concerned with. I know MZ-Tools has something tells you what line the error was on. It is the fact that VBA lacks the tools to do thorough error trapping.

  7. #7
    Forum Contributor
    Join Date
    09-03-2015
    Location
    IE
    MS-Off Ver
    2003 - 2016
    Posts
    258

    Re: Who uses line numbers in their code?

    And in VBA the only way to get this with native tools is to number your lines
    Not sure if you mean the undocumented ERL() function which returns the last line number passed in the code...

    Please Login or Register  to view this content.
    Line numbers can be on a line by themselves, part of the executable line, do not have to the contiguous or even in order. The only restriction is they must be at the start of the line before any executable statement and be numeric (Both reasonably obvious). You can have 'sparse' line numbers where only the 'critical' lines are numbered. VBA just remembers the last line number passed. If no line numbers exist in the procedure, ERL returns 0.

    Sample error log (The tabs don't show):

    Date Time User Machine Line No Procedure Name Module Name Error Error Description
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    20150314 15:43:42 Me MINE 000000 PROPERTY:FILENA FRMSPLASH 000091 Object variable or With block variable not set
    20150730 13:49:41 Me MINE 011110 STATUSPOPUP FRMX 000005 Invalid procedure call or argument
    20150730 13:49:41 Me MINE 011310 STATUSPOPUP FRMX 000091 Object variable or With block variable not set


    I'll answer your poll with a Yes. I don't always use them and don't use them on every line, but they are used extensively. Even if the code has been 'normalised' and broken down into short procedures there can be 2 or 3 critical lines in a procedure where you would like to know which one errored...
    Last edited by grimes0332; 01-18-2016 at 06:54 AM. Reason: Mispellings <- Hah! Proves the point

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Who uses line numbers in their code?

    Not a bad idea:

    Please Login or Register  to view this content.



  9. #9
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Who uses line numbers in their code?

    Quote Originally Posted by grimes0332 View Post
    Not sure if you mean the undocumented ERL() function which returns the last line number passed in the code...

    Please Login or Register  to view this content.
    Line numbers can be on a line by themselves, part of the executable line, do not have to the contiguous or even in order. The only restriction is they must be at the start of the line before any executable statement and be numeric (Both reasonably obvious). You can have 'sparse' line numbers where only the 'critical' lines are numbered. VBA just remembers the last line number passed. If no line numbers exist in the procedure, ERL returns 0.

    Sample error log (The tabs don't show):

    Date Time User Machine Line No Procedure Name Module Name Error Error Description
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    20150314 15:43:42 Me MINE 000000 PROPERTY:FILENA FRMSPLASH 000091 Object variable or With block variable not set
    20150730 13:49:41 Me MINE 011110 STATUSPOPUP FRMX 000005 Invalid procedure call or argument
    20150730 13:49:41 Me MINE 011310 STATUSPOPUP FRMX 000091 Object variable or With block variable not set


    I'll answer your poll with a Yes. I don't always use them and don't use them on every line, but they are used extensively. Even if the code has been 'normalised' and broken down into short procedures there can be 2 or 3 critical lines in a procedure where you would like to know which one errored...
    It was in my first post, I know how to trap errors and the line number via "erl". I think there is a big misunderstanding here ,my point I was making was that the ONLY way to get line numbers was to have them in the code, hence my question "Who puts line numbers in their code?"

  10. #10
    Forum Contributor
    Join Date
    09-03-2015
    Location
    IE
    MS-Off Ver
    2003 - 2016
    Posts
    258

    Re: Who uses line numbers in their code?

    It was in my first post...
    You never mentioned specifically, only alluded to it. Bringing it out into the open might be helpful for others, though.

  11. #11
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,171

    Re: Who uses line numbers in their code?

    Line numbers died out with the dinosaurs.

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    Another thing that's gone the way of the Dinosaurs is Flowcharting - but, if you have an hundred or more page program, as sometimes happened in the days of yore, you might want to consider Flowcharting.

    BTW: Procedure Names in code seem to also be atrophying.
    Last edited by xladept; 01-18-2016 at 12:15 PM.

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Who uses line numbers in their code?

    Quote Originally Posted by xladept View Post
    BTW: Procedure Names in code seem to also be atrophying.
    In what way?

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    Hi Kyle,

    I just posted this code correction - do you see a procedure name? I guess recursion is obviated by the looping features. Anyway, it's been on the back of my mind, so I just threw it out there

    Please Login or Register  to view this content.
    Last edited by xladept; 01-18-2016 at 12:39 PM.

  15. #15
    Forum Contributor
    Join Date
    09-03-2015
    Location
    IE
    MS-Off Ver
    2003 - 2016
    Posts
    258

    Re: Who uses line numbers in their code?

    Line numbers died out with the dinosaurs
    True, as far as they are needed as the target of a GoTo (also not extinct are GoSub, and Return) - but do you have any other way to easily identify where an error occurred in a procedure?

    Don't dismiss something just because it's 'considered obsolete'. After all the Goto statement is still in regular use. Try using that anywhere except setting up an error handler and see the reaction.
    Last edited by grimes0332; 01-24-2016 at 09:05 AM. Reason: Corrected 'target of a Gosub' - should have been 'target of a Goto'.

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    Hi Grimes,

    I still use GoSub.....Return I posted code with about 7 (all to the same procedure) of them just yesterday

  17. #17
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Who uses line numbers in their code?

    Sorry, you've lost me. A re you referring to the fact that the procedure name is completely useless in establishing what the sub actually does?

  18. #18
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Who uses line numbers in their code?

    Hi Dave.
    "And in VBA the only way to get this with native tools is to number your lines."

    Is that necessarily the case?
    Why not use the O n Error and O n Error Goto 0 statements sprinkled through your code and pass some appropriate text comment each time to an independent error logging procedure.

    I realise one should never say never but I was always encouraged to avoid line numbers for program control since it can lead to spaghetti code. I know you are not wanting to use line numbers for program flow in this case - or at least I think that's what you're saying but passing some comment to a procedure when an error occurs seems a more natural way to deal with this.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  19. #19
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    I don't mean anything like that - I'm just saying that most coding that I've seen lately is designed to let the process just "fall through" thus not necessitating Procedure Names. It's good design. But, then


    BTW: WTF is "spaghetti code" and where did the term originate - did some Pundit invent it for personal notoriety?
    Last edited by xladept; 01-18-2016 at 12:54 PM.

  20. #20
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Who uses line numbers in their code?

    Spaghetti code is when you use line numbers for things like loops rather than a loop or writing just one procedure with things like if X goto 4

  21. #21
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    Thanks Kyle, a couple of years ago, someone accused me of spaghetti code because of my fondness for GoSub....Return. I've been wondering what they were talking about. This must have all happened after my involvement with Programming. (IBM School 1965).

  22. #22
    Forum Contributor
    Join Date
    09-03-2015
    Location
    IE
    MS-Off Ver
    2003 - 2016
    Posts
    258

    Re: Who uses line numbers in their code?

    My last word...
    but I was always encouraged to avoid line numbers for program control since it can lead to spaghetti code
    Agree totally.

    The line numbers are not used to control program flow; just as an easy, efficient way to 'record the progress' of the code through the procedure, where needed.

    I see no one else has confessed to using line numbers in the poil - I stand proudly alone (Not an unusual ocurrance, mind).

  23. #23
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: Who uses line numbers in their code?

    I answered "no" and I never, ever, use line numbers. If you do want/need to use GoTo, you can use a label ... but, as has been alluded to, use of GoTo is frowned upon (in polite circles ). Better to use structured programming ( ). And, when I first learned to code, and use structured programming AND flowchart, the guideline was that the flowchart for a module should fit on an A4 sheet of paper (and not be so tiny you couldn't read it). The module should have a specific, limited function with a single entry point, a single exit point, one or more defined inputs and one or more defined outputs.

    So, the structure would start with a Main, or Master, module which called sub-modules. Each sub-module would call other sub-modules until, eventually, you called sub-modules that actually did something. Module names should be descriptive, such as, InputData, AnalyseData, PrintData, etc.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  24. #24
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: Who uses line numbers in their code?

    Using Line Numbers is a PiTA. You need to start with huge gaps in the sequence in order to allow the insertion of additional lines of code. If you don't spread the numbers out, you end up having to re-number them ... and change all the GoTos

  25. #25
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    @ Trevor - wholeheartedly agree But, I like a GoTo once in a while (to paraphrase Oscar Wilde)

  26. #26
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Who uses line numbers in their code?

    Quote Originally Posted by xladept View Post

    BTW: WTF is "spaghetti code" and where did the term originate - did some Pundit invent it for personal notoriety?
    Not entirely sure of the etymology although I seem to remember first becoming aware it back in the heady days of the ZX81 (with 1Kb RAM!) back at the start of the 80s when line numbering was a BASIC requirement and the only way to direct process flow.

  27. #27
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Who uses line numbers in their code?

    On error Goto is all well and good, but when you want to get detailed logs of what is happening and where, I feel it is pretty important to be able to pin point where and why something has happened. No code is flawless and the user can and will do what you don't expect. Especially when working with languages other than English. We have double byte characters and they cause enough headaches.

    Other languages allow for native procedures to get line numbers etc.

    Eg, the below is an example of what I am talking about.

    Please Login or Register  to view this content.
    They look the same in Excel and cause grief to no end. And yes, I try to convert to single byte wherever I can using this,

    Please Login or Register  to view this content.

  28. #28
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    @ Richard - Thanks for the information

  29. #29
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Who uses line numbers in their code?

    What about debug.print module.name & macro/function.name in every macro/ function
    At least you know - consulting the immediate window - in which macro/function something went wrong.

    Please Login or Register  to view this content.

  30. #30
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Who uses line numbers in their code?

    Is easy to find error if you do not use error handler except when you expect error. For user testing is much faster than to read error log.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  31. #31
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Who uses line numbers in their code?

    Quote Originally Posted by snb View Post
    What about debug.print module.name & macro/function.name in every macro/ function
    At least you know - consulting the immediate window - in which macro/function something went wrong.

    Please Login or Register  to view this content.
    I agree that knowing those are important, but line specific is also very important I feel.

  32. #32
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Who uses line numbers in their code?

    Quote Originally Posted by Izandol View Post
    Is easy to find error if you do not use error handler except when you expect error. For user testing is much faster than to read error log.
    And you can expect all errors can you? You are better than me.

  33. #33
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Who uses line numbers in their code?

    Of course not - that is purpose of this method. Debugger shows error during testing and I fix it. Is easier because I have all variables available and user can tell exactly what he did.

  34. #34
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Who uses line numbers in their code?

    Re posts #32 & #33.

    I think this essentially boils down to for whom is the logger required and what Dave means by 'errors' for his error log?

    Is Dave wanting to trap system coding errors and report these in a neat log for himself as the developer, or is he wanting the system to report user input/handling errors FOR the user so that they may review the log and take any appropriate action in a controlled way.

    When developing and like Izandol I prefer to have the system fall over and stop with the yellow highlight so that I know exactly which line causes the problem. As a user though (and particularly if the user doesn't have any idea what macro code is) that's the last thing I want to see.

    I sort of agree with you both. It just depends from where you're approaching the question.

  35. #35
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    @ JapanDave
    Hi JapanDave

    I still like to use Line Numbers sometimes. I still think it is a good way to reference a “Line”..*** I find it useful for, as you have suggested, into specifically referencing a point where, for example, an error occurred, or in general I find it helpful when explaining a code to someone..
    http://www.excelforum.com/developmen...ml#post4198803
    Given my limited experience compared to the others replying to this Thread, I expect my conclusion would then still have to be they are to be avoided!! – If I am using them they are probably bad!!

    Just out of interest though…
    _1 ) I would always put them in after at a point when I was fairly sure the code was finished, and number 10, 20, 30 etc to allow for the possible additions later between lines. And be liberal with a few empty numbered lines, again to allow for inserting lines later.

    _ 2) How to put them In ?…
    _ …I use the technique of copying the whole code and pasting it into a spare Excel Spreadsheet column, then adding each row to an adjacent column of numbers, then copying the final value back to the Code Window…
    Row\Col
    E
    F
    G
    24 10 10
    25 20 Rem 2) Error handling 20 Rem 2) Error handling
    26 30 '2a) An Error handler is made availble and VBA instructed to use it on passing the next line. 30 '2a) An Error handler is made availble and VBA instructed to use it on passing the next line.

    Row\Col
    G
    24
    =E24 & " " &F24
    25
    =E25 & " " &F25
    26
    =E26 & " " &F26


    I guess the above is as outdated as my ideas about using Line Numbers….
    Quote Originally Posted by JapanDave View Post
    ..... the line numbers, I made a short piece of code to do that. ......
    … Could you share / explain that code?

    _ 3) I am not sure if VBA is clear on its concept of counting a code "Line" or Lines….****
    http://www.mrexcel.com/forum/lounge-...ml#post4387604
    For example is this “Line” _....
    Please Login or Register  to view this content.
    _...... one Line or two “Lines” . VBA will not allow me to give the second “Line” a number like this
    Please Login or Register  to view this content.
    _.....................................................


    Here an example of when I would find having Line numbers useful:--- how I would demo… the difference between Error HandlING Statements….._
    On Error Goto 0
    And
    On Error Goto -1
    These two can be thought of as sending the Error to some imaginary points…. Or Lines with numbers 0 and - 1

    _..... Using then line numbers to assist….
    _ Here first the code with Line Numbers:
    Please Login or Register  to view this content.
    _ Here the explanations:

    _a) Run the code preferably in Debug ( F8 ) Mode. You will see that the Error HandlER “made available” in line 40 and “switched on” by line 90 will only work once. This is because VBA is in what is called an “exceptional” state. In this case VBA is being instructed that an Error HandlER is ”in control". Further attempts to “instruct” VBA what to do in the case of an error are ignored. And so further errors are responded to normally.

    _b) Include line 180 ( On Error Goto -1 ) and run again. You will see that the Error HandlER from line 40 works over and over again. Note that in this further ( infinite ) looping, we return from the Error handling code section “ErrHandlERSection1” ( Code Lines 150 to 200 ) to line 60. We do not therefore go through line 40. However the original error handler is still “made available” and ready for use, that is to say, will be switched on by the next error. The Error HandlING Statement, On Error Goto -1, has “switched off” this Error HandlER from Line 60, and cleared the “exceptional VBA State”. But it does not “remove” the “availability” of , or instruction to VBA to use, that original Error HandlER

    _c) Include also now Line 190 ( On Error Goto 0 ) and run again. Now the Error handler will only work once. This is because the additional On Error Goto 0 has “removed” or “Stopped being available” any Error handler in place

    _d) If you further change line 200 to read
    200 GoTo 40
    And run the code again , then the Error HandlER will work over and over again as line 40 will continually “re - switch it on”

    _................................

    I personally find that all a lot easier to explain when I have line numbers.


    Alan


    P.s. Please Let me …..

    ..........................................................................
    References:
    http://www.excelforum.com/excel-new-...ml#post4017218
    http://excelmatters.com/2015/03/17/on-error-wtf/
    https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo
    http://www.mrexcel.com/forum/excel-q...ml#post4357653
    Last edited by Doc.AElstein; 01-19-2016 at 07:10 PM. Reason: last URL repaired
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  36. #36
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    @ grimes0332
    Hi grimes0332,
    Thanks for the Erl() function.. ....Another useful undocumented ( in VBA ) thing.

    Alan
    P.s. In my code from Post #35, Erl() gives me the line number just before the Line where the error occurred.
    _... So does snb’s, if he has a Line number before
    Please Login or Register  to view this content.
    P.P.s…. Try a line number > 65535 !!
    P.P.P.s.. Does Erl() have an optional argument … I mean what is the significance and use ( if any ) of the ()?
    EDIT Ah, OK I missed that Erl() gives by design the last line before the error line,.
    Last edited by Doc.AElstein; 01-22-2016 at 05:59 AM. Reason: poo

  37. #37
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    Well, if you guys are going to make mistakes

  38. #38
    Registered User
    Join Date
    08-06-2016
    Location
    osoyoos
    MS-Off Ver
    windows 10
    Posts
    0

    I love line numbers

    I put the line numbers that are in the code on the flowchart elements.

    pict01.jpg

    pict02.jpg

    This flowchart does the line wrap / hi-lite for my custom search / cms app.

    here is the source code... It's a few years old but this part hasn't changed

    Please please show me how you would code up this routine.....

    http://www.telusplanet.net/public/stonedan/source.txt

  39. #39
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: I love line numbers. So do I. But What the Fuk is this

    Quote Originally Posted by SpectateSwamp View Post
    I put the line numbers that are in the code on the flowchart elements.

    This flowchart does the line wrap / hi-lite for my custom search / cms app.

    here is the source code... It's a few years old but this part hasn't changed

    Please please show me how you would code up this routine.....

    http://www.telusplanet.net/public/stonedan/source.txt

    I am not sure what you want excactly.

    I put some code lines on.

    It is not perfect

    Alan

    ( I did not do it manually !!! )
    Attached Files Attached Files
    Last edited by Doc.AElstein; 08-07-2016 at 01:14 PM.

  40. #40
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Who uses line numbers in their code?

    If I anticipate an error in "normal" usage, I use (for example)
    Please Login or Register  to view this content.
    If I'm still developing / debugging, I'd rather catch the unhandled errors using the built in debugger.

    Line numbers?! No thanks!
    Last edited by Olly; 08-08-2016 at 06:43 PM.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  41. #41
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Who uses line numbers in their code?

    @Olly, that all well and good when it is you debuging the code. But, what happens when you send that file off to the users and all you have to go on is this '"ERROR", Err.Number, Err.Source, Err.Description'. You have no idea where it happened in a lot of cases. Eg, a 'Runtime error' , could happen in any number of places.

  42. #42
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Who uses line numbers in their code?

    I hope that once I've sold a routine that the bugs are caused by improper handling and that I have protected for "bad" data

  43. #43
    Registered User
    Join Date
    08-06-2016
    Location
    osoyoos
    MS-Off Ver
    windows 10
    Posts
    0

    I use line numbers all the time

    I use pseudo line numbers..

    Line_number = "line 1000"
    input data
    line_number = "line 1010"
    Check data.

    With everything handled by a common error section.
    Those errors that aren't trapped get put to a log file for sorting later.

    When one locally traps every error that can possibly occur... Code becomes hard to understand..

    Structured Coders can't jam-it / trial and error test like us noodlers.

    When you have problems with code do a quick FlowChart.

  44. #44
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    Hi SpectateSwamp
    Did you catch post #39
    http://www.excelforum.com/showthread...06#post4450133
    Alan

  45. #45
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Who uses line numbers in their code?

    Your post cannot be seen because of a modulo error in this forum's software.

  46. #46
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    Quote Originally Posted by snb View Post
    Your post cannot be seen because of a modulo error in this forum's software.
    Hi snb
    who are you talking to ?
    Alan

    P.s.
    if you aretalikng about this;
    http://www.excelforum.com/suggestion...-not-exis.html

    -..The point was that I did not actually post.
    Last edited by Doc.AElstein; 09-27-2016 at 09:43 AM.

  47. #47
    Registered User
    Join Date
    08-06-2016
    Location
    osoyoos
    MS-Off Ver
    windows 10
    Posts
    0

    Don't dare take away Our line numbers... Dumb Asses

    Quote Originally Posted by Doc.AElstein View Post
    Hi SpectateSwamp
    Did you catch post #39
    http://www.excelforum.com/showthread...06#post4450133
    Alan


    For those that are against line numbers... Do your thing.. But leave us alone. Or we will do code reviews on your pathetic offerings.

  48. #48
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    Quote Originally Posted by SpectateSwamp View Post
    For those that are against line numbers... Do your thing.. But leave us alone. Or we will do code reviews on your pathetic offerings.
    Lol.. Don’t be too aggressive though.

    _.. In the meantime I have found line numbers most use in helping to explain Code I pass on in this forum... as well as for the Erl() stuff mentioned all in this Thread.
    http://www.excelforum.com/the-water-...ml#post4293426

    But sometimes , like today, I get really naffed off with my own offerings with code lines when I want to modify or just take parts from a code..

    _..so _ I already have a code which puts in the code lines at a flash...... so I did a quick Antidote today and did a code to wipe them out and put the code back as it originally was:
    http://www.excelforum.com/showthread...t=#post4488539

    _ Now there is a positive contribution to our acceptance_.. if you can wipe the code lines out at a flash, then it is not too bad to have them given Line numbers in, may be


    Alan

    P.s. The latest of my codes for “putting them in” is here_..
    http://www.excelforum.com/showthread...t=#post4488352
    _....not perfect yet, but not too bad.

    Edit: 26th Sept 2016 I updated my codes a bit:
    http://www.excelforum.com/showthread...09#post4489109
    Last edited by Doc.AElstein; 09-26-2016 at 01:02 PM.

  49. #49
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Who uses line numbers in their code?

    I am not sure that what are you trying to achieve. But if it's about line number only then it can be done by this.
    Please Login or Register  to view this content.
    But actually I am not sure about your question.

  50. #50
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Who uses line numbers in their code?

    Also this one works for me :
    Please Login or Register  to view this content.

  51. #51
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    Hi sanran
    Quote Originally Posted by sanram View Post
    ...
    But actually I am not sure about your question.
    In this Thread we are discussing generally the use of Line Numbers in code.

    Line Numbers

    Your second code uses Line Numbers.
    Please Login or Register  to view this content.
    We have been discussing the use of these. We are interested in any opinions. The Thread is asking if people use line numbers. I for example use them freqeuntly,
    http://www.excelforum.com/showthread...t=#post4488539

    See also in the Thread we are now in ( see in side 2 of the Thread we are now in:
    http://www.excelforum.com/the-water-...ml#post4293426 )
    In this code I am using Line numbers and two labels, ErrHandlERSection1: and CreateError:
    Please Login or Register  to view this content.
    _.........................
    _._____________________________________________________________________

    Labels

    Quote Originally Posted by sanram View Post
    I am not sure that what are you trying to achieve. But if it's about line number only then it can be done by this.
    Please Login or Register  to view this content.
    ....
    I think you are incorrect.

    Your first Code is using Labels.
    https://www.youtube.com/watch?v=cE1NOfyz04Y

    Your first code only has Labels. it has no Line Numbers
    Please Login or Register  to view this content.

    This is how my code would look like with labels instead of line numbers

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    _..................................


    Do you see the difference?

    I can use either 150: or ErrHandlERSection1: as a label. They are both labels. I cannot use them both on the same line.

    But I can use a Line Number and a Label on the same line
    150 ErrHandlERSection1:


    Alan
    Last edited by Doc.AElstein; 09-26-2016 at 03:09 AM.

  52. #52
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Who uses line numbers in their code?

    Okay. I got it now.

  53. #53
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    Quote Originally Posted by sanram View Post
    Okay. I got it now.
    Glad to help with your understanding....
    Alan

  54. #54
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Some Further Codes to add or take off Line numbers. Errorousing ; GoTo; Labuls

    Duplicate Post
    Last edited by Doc.AElstein; 09-26-2016 at 01:12 PM.

  55. #55
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Further Codes to add or take off Line numbers. Errorousing ; GoTo; Labuls L Bollox

    While I am here_..... that last bit of label Line stuff made me think::: my codes did not catch GoTo Line Numbers and the such .. and nor did a lot others i googled...

    Actuallyy, I Googled quite a bit on looking for code alternatives to mine for putting in and taking off line Numbers. There are a few but i could not get them too work. Possibly I will be enlightened in the future. In the meantime I thought no harm in “perfecting” mine a bit further..

    So, A couple of problems I noticed.

    _1 ) if you have a code line like_...
    _______ GoTo 234
    _.. where 234 is an existing Line number in a code where you want to add line numbers to the complete code, then you would need to adjust those numbers manually after running a code to add Line numbers. ----- But not any more with my latest code.

    _2 ) On my code to remove Line numbers, then you can get a problem with any Labels. ( Like _....
    Label_X:
    _......etc )

    This is because if my code that puts the label in, pasts out like this ( like what it does ) _...

    20 ___LabulX:

    _...then the VB Editor “eats” the extra spaces and changes it to this:

    20 LabulX:

    My code for taking off Code lines was then was getting a bit “out of wack” , which resulted in some labels getting a bit truncated, like

    bulX

    ( Bollox ! ) _....
    _....But never mind I fixed that as well

    _.......

    Here is a good example
    Say I start with this code, which is messy, but works:

    Please Login or Register  to view this content.
    I copy that to the Clipboard ( Highlight Code and Hit Ctrl+C )
    I run my code
    Sub InsertLineNo_A8()
    That puts the modified code with Line Numbers in the Clipboard
    I paste it out ( Ctrl+V ) and get this:

    Please Login or Register  to view this content.
    I copy that to the Clipboard
    I run my code
    Sub CodLnsWeg_A8()
    That puts the modified code without Line Numbers in the Clipboard
    I paste it out and get this:

    Please Login or Register  to view this content.
    I copy that to the Clipboard
    I run my code
    Sub InsertLineNo_A8()
    That puts the modified code with Line Numbers in the Clipboard
    I paste it out and get this:

    Please Login or Register  to view this content.
    I copy that to the Clipboard
    I run my ....... etc... etc.....

    _................................................

    The codes are in their ‘Explicit Pedantry Form to help further the understanding of mankind..
    If I get around to sanitising them and maybe catch anything else non perfekt in them then I will maybe do a post in Tips and Tutorials Sub Forum. For now someone might find them useful..

    Alan
    _.....................

    Code:
    Sub InsertLineNo_A8()
    ( All one code: I had to split it due to Post size limits. Each code section should be copied directly under the previous in the same Code Module )
    http://www.excelforum.com/showthread...t=#post4489052
    http://www.excelforum.com/showthread...t=#post4489054


    and likewise for
    Sub CodLnsWeg_A8()
    http://www.excelforum.com/showthread...t=#post4489061
    http://www.excelforum.com/showthread...t=#post4489065
    Last edited by Doc.AElstein; 09-27-2016 at 10:01 AM.

  56. #56
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Who uses line numbers in their code?

    @DocE

    Put this code in de codemodule of sheet1.
    Run the macro: M_snb

    Please Login or Register  to view this content.

  57. #57
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    Hi snb, Thanks for that.
    _....

    I have all the following codes in my Worksheets Code Module with Code name "Tabelle1"

    Then,

    This

    Please Login or Register  to view this content.
    -.. after running this

    Please Login or Register  to view this content.
    _.. gives this

    Please Login or Register  to view this content.
    _.............................................._______________________________-

    And this

    Please Login or Register  to view this content.
    _...after running this

    Please Login or Register  to view this content.
    _... gives this

    Please Login or Register  to view this content.
    _.........................
    __________
    and this:

    Please Login or Register  to view this content.
    _.. after running this:

    Please Login or Register  to view this content.
    _.. gives this:

    _....... a mess !

    _ But for adding lines it is a nice alternative.
    _ It keeps a constant indent ( for all but labels ) when the number of digits change, like

    90__'''
    100_'''

    _ I note that the second code must go directly after the first.



    _ otherwise, as usual.... I have not got a clue how it works

    _ Eventually I will suss it may be


    Alan
    Last edited by Doc.AElstein; 09-26-2016 at 06:27 PM.

  58. #58
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Who uses line numbers in their code?

    It was only meant for adding line numbers (not adding lines....). But apparently you found out yourself.

    So if you 'misuse' the code: mess in, mess out.

    Please Login or Register  to view this content.
    Last edited by snb; 09-27-2016 at 03:10 AM.

  59. #59
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    Quote Originally Posted by snb View Post
    It was only meant for adding line numbers...
    _.......
    Sub M_snb_numberout()
    _.......
    _......
    _............
    Thanks snb.
    It’s a very nice compact code for adding Line numbers. Works great
    http://www.excelforum.com/showthread...t=#post4489718
    http://www.excelforum.com/showthread...=3#post4489721

    I’d use it more if I understood it

    Thanks for the Number out code_................
    Sub M_snb_numberout()
    _.............well.
    I had to fight a little bit with that..........

    _1 ) It errors if a line has no code and just a line number.
    _ I did a quick correction for that:
    http://www.excelforum.com/showthread...t=#post4489734

    then

    _2 ) I noticed it leaves a small offset at the early lines.
    http://www.excelforum.com/showthread...t=#post4489734
    Possibly that does not always occur depending on tab settings? . I see you are using a Tab in your code, but I do not quite understand what you are doing there.


    Anyway, I played around a bit, ( and messed it up a bit ) -...-- and came up with a messy version that knocks out those extra indents.
    http://www.excelforum.com/showthread...t=#post4489792


    Alan
    Last edited by Doc.AElstein; 09-27-2016 at 09:48 AM.

  60. #60
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Who uses line numbers in their code?

    a new post to show the others.

  61. #61
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Who uses line numbers in their code?

    Hi snb
    FWIW. I keep my latest version of a code to put in or take off code lines in the Code module CodeLines in this File which I keep up to date.
    https://app.box.com/s/zhz7awdag4nl1zs6564s9zzcwp50e4w9
    I am at A8 I think now
    Sub InsertLineNo_A8() ‘ To Put in
    Sub CodLnsWeg_A8() ‘ To take out

    The codes are messy with all my usual unnecessary stuff and ‘Comments etc.
    But I find them useful.
    They also now deal with GoTo ‘s, so if you have existing GoTo ‘s with numbers then it catches them and corrects for that. They are almost certainly not perfect. As time goes by I notice Bugs or things not quite working so I update the code and put the next version in that File. ( A9 would be the next etc )

    The way the codes work is that you highlight a code, copy it to the clipboard, then run the Put in or take out Code Lines Code. The modified code is then in the Clipboard so if you paste back out over your selection then you should get the modified version of your code. ( or paste it out somewhere else ). The code that puts Code lines in also adds a temporary Worksheet in the current Active Workbook with the code in which you can chose to delete or not through the normal Default Display warning that pops up when the code attempts to delete it. )

    Here I just posted the current codes again :
    Version A8

    Sub InsertLineNo_A8() ‘ To Put in

    ( All one code I just had to split it in three parts due to post size restrictions )

    http://www.excelforum.com/showthread...t=#post4525293
    http://www.excelforum.com/showthread...t=#post4525301
    http://www.excelforum.com/showthread...t=#post4525305


    _......

    Sub CodLnsWeg_A8()

    ( All one code I just had to split it in two parts due to post size restrictions )

    http://www.excelforum.com/showthread...t=#post4525308
    http://www.excelforum.com/showthread...t=#post4525316


    Alan

    BTW. Post # 17
    http://www.excelforum.com/showthread...t=#post4291625
    mention some M Z tool
    I guess that Tool is probably a much better alternative to mine.

    I think it costs though. That tends to put me off, not that I mind paying necessarily for stuff, but I find then you get in a trap of having to keep updating things

  62. #62
    Registered User
    Join Date
    08-06-2016
    Location
    osoyoos
    MS-Off Ver
    windows 10
    Posts
    0

    I put line numbers back in

    I assign a line number to a string throughout the program.

    My massive Search Engine / Multimedia Player has 10,000 plus lines
    and many many goto's

    When an error occurs I check the value of the string and GoTo the error handler.

    How easy it is to find source if the lines are in sequence.

    Serendipity kicks in every now and again. That just doesn't happen with structured code. Nope
    Last edited by AliGW; 02-23-2017 at 08:15 AM. Reason: Video link removed by moderator - seems to bear no relevance at all to the thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. F8 does not execute code line by line in new windows 8.1
    By lubbamkt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2014, 11:22 AM
  2. Replies: 1
    Last Post: 11-13-2013, 10:28 AM
  3. [SOLVED] SQL Code does not fit in one line in Excel VBA, automatically moves down one line
    By trizzo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-11-2013, 10:56 AM
  4. [SOLVED] Save Data code not saving to the next line, keeps overwriting to same line.
    By Justin25150 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2012, 06:40 AM
  5. Line numbers some numbers are blue some are black
    By nkt122866 in forum Excel General
    Replies: 2
    Last Post: 09-29-2005, 05:05 PM
  6. Continue line of VBA code on the next line
    By peacelittleone in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-24-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1