Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question line numbers in VBA code

    I am trying to debug my excel macro but i am having a hard time because the "Erl" feature doesn't work unless code line numbers have been added. Is there a quick way to add code line numbers (aside from typing them in manually)? Thanks for any responses!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mike,

    Welcome to the Lounge as a new poster.

    Take a look at this macro.

    I would recommend using this on a COPY of your workbook so you can maintain the original un-numbered code.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Oct 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Mike,
    By default, I don't think there's any quick way to add line numbers. There are code-editing items out there that can do that sort of thing if you google it, one is called codeliner or something like that. Other than that, you could try writing a macro that will do it by using the ReplaceLine method of the CodeModule object. I hope this helps!

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Mike,

    Vince is correct. The following code will add the line numbers directly into the code without copy/pasting. Replace the module or form and procedure names (in blue) with those in your project. It will take into consideration where the procedure is located and any declarations in the declarations section of the module. It will exclude any lines that cannot have a line number and add only one line number to a code line than spans multiple lines.

    HTH,
    Maud

    Sample code prior to running "AddLineNums":
    Mike1.png

    Sample code after running "AddLineNums":
    Mike2.png

    Code:
    Public Sub AddLineNums()
    '--------------------------------------
    'DECLARE AND SET VARIABLES
        Dim Procedure As CodeModule, Code As String, Module As String, Proc As String
        Dim StartLine As Long, EndLine As Long, LineNum As Integer, I As Long
        Module = "Module1" 'CHANGE TO MODULE NAME
        Proc = "UpdateStats"  'CHANGE TO PROCEDURE NAME
        Set Procedure = ThisWorkbook.VBProject.VBComponents(Module).CodeModule
        StartLine = Procedure.ProcBodyLine(Proc, vbext_pk_Proc)
        EndLine = Procedure.ProcCountLines(Proc, vbext_pk_Proc) - StartLine
        LineNum = 10
    '--------------------------------------
    'CYCLE THROUGH LINES OF CODE
        For I = StartLine To EndLine
    '--------------------------------------
    'EXCLUSIONS
            If InStr(1, Procedure.Lines(I, 1), "Sub", vbTextCompare) > 0 Then GoTo NextLine
            If InStr(1, Procedure.Lines(I, 1), "Function", vbTextCompare) > 0 Then GoTo NextLine
            If I > 1 Then
                If Right(Procedure.Lines(I - 1, 1), 1) = "_" Then GoTo NextLine
            End If
    '--------------------------------------
    'ADD LINE NUMBER
            Procedure.ReplaceLine I, LineNum & Chr(9) & Procedure.Lines(I, 1)
            LineNum = LineNum + 10
    NextLine:
        Next I
    '--------------------------------------
    'CLEANUP
    Set Procedure = Nothing
    End Sub
    Last edited by Maudibe; 2015-11-22 at 11:15.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Mike,

    I forgot to mention that you have to reference the Microsoft Visual Basic for Applications Extensibility 5.3 library.

    Alt-F11 to open VB editor > Tools > Reference... > scroll down to Microsoft Visual Basic for Applications Extensibility 5.3 library > Tick the checkbox next to it > OK.

    Maud

  6. #6
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts
    I've used MZ-Tools 3 (free) for several years and have just purchased MZ-Tools 8 for vb/vba. It may be more than you want -- it does a lot more than adding and removing line numbers, and works within the vbe.

  7. #7
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    I will second the MZ-Tools (Click Here) recommendation. Many VBA developers I know say it is a MUST HAVE for anyone working in VBA (Word Macros).

    The great thing about MZ-Tools is that is a VBE add-in which allows it to work in ALL Office applications that use the VBE editor for VBA code. It even works across multiple version of Office on the same PC.
    Last edited by HiTechCoach; 2015-11-23 at 15:58.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Tags for this Thread

Posting Permissions

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