12

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.

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
sigil
  • 9,370
  • 40
  • 119
  • 199
  • you can have a look here and see if it's a suitable solution: http://www.mrexcel.com/forum/excel-questions/576449-code-line-numbers-visual-basic-applications.html also here: http://www.mztools.com/v3/mztools3.aspx – sous2817 Sep 03 '15 at 17:34

6 Answers6

11

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.

Blackhawk
  • 5,984
  • 4
  • 27
  • 56
  • 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 http://www.cpearson.com/excel/vbemenus.aspx for hints – Blackhawk Sep 03 '15 at 17:57
  • All this does, for me at least, is spit out the line number I use for X. – Johnny Bones Sep 03 '15 at 18:08
  • @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`. – Blackhawk Sep 03 '15 at 18:20
  • I think this is my best option, seems to work pretty well when I use it from the Immediate command line. – sigil Sep 03 '15 at 18:40
6

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.

enter image description here

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.

MatthewD
  • 6,719
  • 5
  • 22
  • 41
  • This answer is especially useful for people, who can't change the TrustCenter-settings, which were disabled by an admin – bluelDe May 30 '16 at 11:41
  • How have I never noticed that feature? This is surely better than trying to remember line numbers or scroll bar position. Thanks. – dra_red Jul 29 '19 at 23:56
2

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 sProc 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
        sProc = vbaModule.ProcOfLine(lActiveLine, vbext_pk_Proc)

        With vbaModule
            .CodePane.SetSelection .ProcStartLine(sProc, ProcType) + lLine, 1, .ProcStartLine(sProc, ProcType) + lLine + 1, 1
        End With
    End If

End Sub
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
1

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.

Paulo Avelar
  • 2,140
  • 1
  • 17
  • 31
  • 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). – sigil Sep 03 '15 at 18:19
  • I see, that makes sense. In that case, the `JumpToLine` function seems more fitting. – Paulo Avelar Sep 03 '15 at 18:24
0

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

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • VBA technically does internally keep track of line numbers - see [SetSelection](https://msdn.microsoft.com/en-us/library/Aa443967(v=VS.60).aspx) – Blackhawk Sep 03 '15 at 18:02
0

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 sProc 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
    sProc = thisModule.ProcOfLine(lActiveLine, vbext_pk_Proc)
    Dim newline As String
    newline = LCase(InputBox("Enter the desired line." _
                & vbLf & "    20 means line 20 in " & sProc _
                & 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(sProc, ProcType) + lLine, 1, .ProcStartLine(sProc, 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://stackoverflow.com/questions/32381879/how-to-jump-to-line-number-in-vba-editor
End With
 exit_function: End Sub

`

UncleBob
  • 41
  • 1
  • 7