How to jump to line number in VBA editor?

vba excel office-2010
sigil picture sigil · Sep 3, 2015 · Viewed 11.3k times · Source

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

Blackhawk picture Blackhawk · Sep 3, 2015

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.

Related questions

Calling web service using VBA code in Excel 2010

I am trying to write some VBA code in Excel 2010 that would consume a web service. I am unable to find any related resources on the internet. Can someone please tell me how to do this.

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More