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.

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.

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.

Answer 3

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.