How to jump to line number in VBA editor?

Question

I'm using VBA in Office 2010. On the top, there's a box with the line number and column number, e.g.:

Ln 1480, Col 17

Is there a way to jump directly to another line number in code editing (not in execution), the same way that I would use Ctrl+G in Notepad? This MSDN answer suggests that it's not possible, but I'm hoping that someone has found a way to do this kind of editor navigation.

I know that it's possible to just click on a procedure name in the dropdown, but unfortunately I'm working with some procedures that are several hundred lines long and until I get them refactored, it would be great to be able to include a line number in my bug tracker and jump to that line when I'm resolving the issue.

Respiratory

you can have a look here and see if it's a suitable solution: mrexcel.com/forum/excel-questions/… also here: mztools.com/v3/mztools3.aspx – Hennery

Answer 1

Make your own JumpToLine procedure for the VBA IDE

Create a new module called mdlJumpToLine and add the following method:

Public Sub JumpToLine(line As Long)
    Application.VBE.ActiveCodePane.SetSelection line, 1, line, 1
End Sub

As an example, if you want to jump to a line 1,234 in the code module or class you have open in the current code pane, type JumpToLine 1234 in the immediate window and hit enter. If that line is already in view, this does nothing, but if it's off the screen, it will automatically be scrolled to the center of the screen

Trust access to the VBA project object model

If you get this error, "Method 'VBE' of object '_Application' failed", you will have to make programmatic access to the VBE trusted. You can do this by (in Excel 2007) going to the main excel window (not the VBA IDE) and clicking "File" ---> "Options" ---> "Trust Center" ---> "Trust Center Settings" ---> "Macro Settings" and selecting the checkbox for "Trust access to the VBA project object model". From then on, the JumpToLine method should work.

Moncada

I'm willing to bet it wouldn't be terribly difficult to add a button and window for it to a VBA IDE toolbar, and then package it as an Addin... see cpearson.com/excel/vbemenus.aspx for hints – Moncada

All this does, for me at least, is spit out the line number I use for X. – Portaltoportal

@JohnnyBones Can you debug to find out how it is printing to the immediate window? That seems an odd behavior given no explicit use of debug.print. – Moncada

I think this is my best option, seems to work pretty well when I use it from the Immediate command line. – Respiratory

Answer 2

Not that know of. You can use bookmarks from the edit toolbar. If your edit toolbar is not displayed, Go to the View pulldown menu and select "Toolbars" and select "Edit".

The bookmark tools are on the right of the menu.

This will allow you to put bookmarks wherever you want in your code. You can then travel between them by hitting forward or backward bookmark arrows.

Galton

This answer is especially useful for people, who can't change the TrustCenter-settings, which were disabled by an admin – Ingrained

How have I never noticed that feature? This is surely better than trying to remember line numbers or scroll bar position. Thanks. – Ftc

Answer 3

This procedure will prompt you for a line number and then (kind of) take you to that line for whatever procedure you're already in. Two things: There's no error checking in it, so it needs some work there; If you put in a number that's larger than the total lines, it just takes you to the next procedure. But if you enter, say 30, it will take you to the 30th line of the current procedure rather than just the 30th line of the Module.

Public Sub GotoLine()
    
    Dim lLine As Long, lActiveLine As Long
    Dim storedProcedure As String
    Dim ProcType As Long
    Dim vbaModule As CodeModule
    Dim vbaPane As CodePane
    
    lLine = Application.InputBox("Enter Line", "Go to Line", , , , , , 1)
    Set vbaPane = Application.VBE.ActiveCodePane
    Set vbaModule = vbaPane.CodeModule
    
    If lLine > 0 Then
        vbaPane.GetSelection lActiveLine, 0, 0, 0
        storedProcedure = vbaModule.ProcOfLine(lActiveLine, vbext_pk_Proc)
        
        With vbaModule
            .CodePane.SetSelection .ProcStartLine(storedProcedure, ProcType) + lLine, 1, .ProcStartLine(storedProcedure, ProcType) + lLine + 1, 1
        End With
    End If
    
End Sub

Ogre

Answer 4

If you need that for bug-tracking assistance, why don't you use GoTo labels?

I bet your bug-tracking tool gets you an ID for the bug or something similar. Simply find the part where the bug is and add a line to it:

Bug1234: 'you may even add comments on the issue/bug

This line is ignored in execution and you can find it using Ctrl+F and searching the label name.

The upside is that if you refactor or change anything in your code, the reference will remain valid, whilst if you simply use the line number, any modification will invalidate the reference.

Roundy

Yes, that's more stable than line numbers, and I've been using that, but it isn't a solution that will work for someone who has read-only access to the workbook and wants to report a bug in a version that's already been released (and thus the line number won't be affected by changes to the development version). – Respiratory

I see, that makes sense. In that case, the JumpToLine function seems more fitting. – Roundy

Answer 5

The only way to do this would be to physically label your lines in your code. It's somewhat of a pain, but you can do this by using this code to add them:

Sub AddLineNumbers(wbName As String, vbCompName As String)
    Dim i As Long, j As Long, lineN As Long
    Dim procName As String
    Dim startOfProceedure As Long
    Dim lengthOfProceedure As Long
    Dim newLine As String

    With Workbooks(wbName).VBProject.VBComponents(vbCompName).CodeModule
        .CodePane.Window.Visible = False

        For i = 1 To .CountOfLines
            procName = .ProcOfLine(i, vbext_pk_Proc)

            If procName <> vbNullString Then
                startOfProceedure = .ProcStartLine(procName, vbext_pk_Proc)
                lengthOfProceedure = .ProcCountLines(procName, vbext_pk_Proc)

                If startOfProceedure + 1 < i And i < startOfProceedure + lengthOfProceedure - 1 Then
                    newLine = RemoveOneLineNumber(.Lines(i, 1))
                    If Not HasLabel(newLine) And Not (.Lines(i - 1, 1) Like "* _") Then
                        .ReplaceLine i, CStr(i) & ":" & newLine
                    End If
                End If
            End If

        Next i
        .CodePane.Window.Visible = True
    End With
End Sub

Then you would have to add a function to jump to whatever line you needed to get to:

Function JumpToLine(LnNum as String)
  GoTo LnNum
End Function

Source: http://www.mrexcel.com/forum/excel-questions/576449-code-line-numbers-visual-basic-applications.html

Portaltoportal

VBA technically does internally keep track of line numbers - see SetSelection – Moncada

Answer 6

Dick's solution was excellent, but this version can be called from the immediate window.

Public Sub IDEGotoLine()

    Dim lLine As Long, lActiveLine As Long
    Dim storedProcedure As String
    Dim ProcType As Long
    Dim thisModule As CodeModule
    Dim thisPane As CodePane

    Set thisPane = Application.VBE.ActiveCodePane
    Set thisModule = thisPane.CodeModule
    Application.VBE.Windows(thisModule & " (Code)").SetFocus
    With thisModule
        thisPane.GetSelection lActiveLine, 0, 0, 0
        storedProcedure = thisModule.ProcOfLine(lActiveLine, vbext_pk_Proc)
        Dim newline As String
        newline = LCase(InputBox("Enter the desired line." _
                    & vbLf & "    20 means line 20 in " & storedProcedure _
                    & vbLf & " m20 means line 20 in " & thisModule _
                    & vbLf & "The current line is  m" & lActiveLine))
                    Select Case Left(newline, 1)
        Case "m"hh
            On Error GoTo exit_function
            lLine = Mid(newline, 2)
            Application.VBE.ActiveCodePane.SetSelection lLine, 1, lLine + 1, 1
        Case Else
            On Error GoTo exit_function
            lLine = newline
            .CodePane.SetSelection .ProcStartLine(storedProcedure, ProcType) + lLine, 1, .ProcStartLine(storedProcedure, ProcType) + lLine + 1, 1
        End Select
        If IsError(Application.Caller) Then SendKeys "{f7}" ' if called from immediate window protect highlight from being erased.
    
    ' see https://www.experts-exchange.com/dashboard/#/questions/my/29262416
    ' see https://mcmap.net/q/923420/-how-to-jump-to-line-number-in-vba-editor
    End With
     exit_function: 
End Sub

Disseisin