get module line from erl and procedure

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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

RBS

"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> OK, how about knowing the module name and the erl?
>
> RBS
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in
>> message news:[email protected]...
>>> 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
>>
>>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> OK, how about knowing the module name and the erl?
>>
>> RBS
>>
>> "Chip Pearson" <[email protected]> wrote in message
>> news:[email protected]...
>>>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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>>
>>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in
>> message news:[email protected]...
>>> OK, how about knowing the module name and the erl?
>>>
>>> RBS
>>>
>>> "Chip Pearson" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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" <[email protected]> wrote in
>>>> message news:[email protected]...
>>>>> 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
>>>>
>>>>
>>>
>>
>>
>

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

"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> OK, how about knowing the module name and the erl?
>>>>
>>>> RBS
>>>>
>>>> "Chip Pearson" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>>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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>

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


"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> OK, how about knowing the module name and the erl?
>>>>
>>>> RBS
>>>>
>>>> "Chip Pearson" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>>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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>