get module line from erl and procedure

[RB Smissaert]

Is there a way to get the VBE module and module line, given the procedure
and erl where an error occured?
The idea is to move straight to the line in the VBE where the error occured
after handling the error.
I have numbered the lines per procedure with MZ Tools.

I can move to the line when I have the module and the module line:

With ThisWorkbook.VBProject.VBComponents(strModule).CodeModule.CodePane
.SetSelection lStartLine, 1, lStartLine, 1
.Show
End With

I could make my error handler retain the module as well as the procedure,
but even then I am not sure I could get
to the module line.


RBS

 

[Chip Pearson]

I don't believe you can get the module name in which the error
occurred.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[RB Smissaert]

OK, how about knowing the module name and the erl?

RBS

 

[Chip Pearson]

As I said, you can't get the module name. Erl will return the
line number of the error, assuming that line has a label,
otherwise it will return the most recent line label.

Sub AAA()
10: On Error Resume Next
20: Debug.Print 1 / 0
30: Debug.Print Err.Source, Err.Number, Err.Description, Erl
End Sub

displays


VBAProject 11 Division by zero 20

in the Immediate window.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[RB Smissaert]

Yes, but I can add the module name to the error handler.

Now if I have:

- Module name
- Procedure name
- erl

would I then be able to get the linenumber of the module?

RBS

 

[Chip Pearson]

No, you can't get the text line number in code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[RB Smissaert]

I suppose I could get the .ProcStartLine and do a find within that block of
code for the erl, knowing they always
start in column one.
There is a small chance though it would find a non-erl number that is the
same.

RBS

 

[RB Smissaert]

This Sub will do the job:
It is combined with some other code, but you can see.


Sub GoToVBELine(Optional strModule As String, _
Optional strProcedure, _
Optional bFunction As Boolean = False, _
Optional lErl As Long = -1)

Dim strCell As String
Dim lBracketPos As Long
Dim lSpacePos As Long
Dim lStartLine As Long
Dim lProcedureLine As Long
Dim strSelection As String
Dim i As Long

On Error GoTo ERROROUT

If Len(strModule) = 0 Then
strModule = Cells(ActiveCell.Row, 1).Value
End If

If lErl = -1 Then
'get there from values in the sheet
'----------------------------------
strCell = ActiveCell.Value
lBracketPos = InStr(1, strCell, "(", vbBinaryCompare)
lSpacePos = InStr(lBracketPos, strCell, Chr(32), vbBinaryCompare)
lStartLine = Val(Mid$(strCell, lBracketPos + 1, lSpacePos - (lBracketPos
+ 1)))
With ThisWorkbook.VBProject.VBComponents(strModule).CodeModule.CodePane
.SetSelection lStartLine, 1, lStartLine, 1
.Show
End With
Else
'get there from values from an error handler
'-------------------------------------------
With ThisWorkbook.VBProject.VBComponents(strModule).CodeModule
lProcedureLine = .ProcStartLine(strProcedure, vbext_pk_Proc)
Do While .Find(CStr(lErl), _
lProcedureLine + i, _
1, _
lProcedureLine + i, _
Len(CStr(Erl)) + 1, _
True, _
False) = False
i = i + 1
Loop
With .CodePane
.SetSelection lProcedureLine + i, 1, lProcedureLine + i, 1
.Show
End With
End With
End If

Exit Sub
ERROROUT:

On Error GoTo 0

End Sub


RBS