Quartz,

VBA code is stored in the CodeModule of a VBComponent. A CodePane
is the visible representation of the CodeModule. A CodePane is
one of many types of a Window. Other Windows include the main VBE
window itself, the project explorer, the immediate window, etc.


2. If I have code stored in a sheet module, how can I get a
reference to the code pane that that sheet module is in?

To access the CodePane of a worksheet's code, use something like
the following:

Dim VBComp As VBIDE.VBComponent
Dim VBCodeMod As VBIDE.CodeModule
Dim VBCodePane As VBIDE.CodePane

Set VBComp = ThisWorkbook.VBProject.VBComponents( _
Worksheets("Sheet1").CodeName)
Set VBCodeMod = VBComp.CodeModule
Set VBCodePane = VBCodeMod.CodePane
Debug.Print VBCodePane.CountOfVisibleLines

3. How can you determine or equate the code pane object to any
other object in the VBE using VBA?

I'm not entirely sure what you are asking here, but you can get
to the VBComponent of a CodePane with code like the following:

Set VBComp = VBCodePane.CodeModule.Parent

To get to the VBProject containing a given CodePane, use

Dim VBProj As VBIDE.VBProject
Set VBProj = VBCodePane.CodeModule.Parent.Collection.Parent

For more information, see www.cpearson.com/excel/vbe.htm and
www.cpearson.com/excel/codemods.htm



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


"quartz" wrote in message
...
Hello. I'm hoping an MVP or someone who knows can help me here.

1. Can someone please explain to me what a "CodePane" is and
how it differs from a "CodeModule" or, for that
matter, a window?

2. If I have code stored in a sheet module, how can I get a
reference to the code pane that that sheet module is in?

3. How can you determine or equate the code pane object to any
other object in the VBE using VBA?

4. Your example code regarding 2 & 3 above would be most
appreciated.

Thanks in advance for your assistance.

Unless you program (hard code) the information you want, such information is
not available in relation to an error to the best of my knowledge. (If you
hard code a significant key part of the chain of information you want, then
you can get the rest as Chip has shown).

Use of the components of the VBE object model do not require a reference to
the VBE extensibility. They will be late bound. However, if you declare
them as

Dim vbcomp as VbComponent

then you would need the reference since this is early binding.

--
Regards,
Tom Ogilvy

"Mark Worthington" wrote in message
om...
Chip et al,

I have devoured the great info on your site and elsewhere, but cannot
work out how to "easily" determine a standard module name in VBA. It
would appear NOT to be simply available via the VBComponents
collection etc, but I did come across your method, ref
Application.VBE.ActiveCodePane.CodeModule.Parent.N ame. This seems not
to require a reference in VBA to the VBA Extensibility library ....

With regard to the original question no. 3 :

3. How can you determine or equate the code pane object to any other
object in the VBE using VBA?

I would like to be able to determine the procedure, module & project
names so that my standard error handler can say "Error 1004 .... in
Procedure x, Module y, Project z." So I would like to ask if anyone
can enlighten me on determining the above names.

Many thanks,

Mark

"Chip Pearson" wrote in message
...
Quartz,

VBA code is stored in the CodeModule of a VBComponent. A CodePane
is the visible representation of the CodeModule. A CodePane is
one of many types of a Window. Other Windows include the main VBE
window itself, the project explorer, the immediate window, etc.


2. If I have code stored in a sheet module, how can I get a
reference to the code pane that that sheet module is in?

To access the CodePane of a worksheet's code, use something like
the following:

Dim VBComp As VBIDE.VBComponent
Dim VBCodeMod As VBIDE.CodeModule
Dim VBCodePane As VBIDE.CodePane

Set VBComp = ThisWorkbook.VBProject.VBComponents( _
Worksheets("Sheet1").CodeName)
Set VBCodeMod = VBComp.CodeModule
Set VBCodePane = VBCodeMod.CodePane
Debug.Print VBCodePane.CountOfVisibleLines

3. How can you determine or equate the code pane object to any
other object in the VBE using VBA?

I'm not entirely sure what you are asking here, but you can get
to the VBComponent of a CodePane with code like the following:

Set VBComp = VBCodePane.CodeModule.Parent

To get to the VBProject containing a given CodePane, use

Dim VBProj As VBIDE.VBProject
Set VBProj = VBCodePane.CodeModule.Parent.Collection.Parent

For more information, see www.cpearson.com/excel/vbe.htm and
www.cpearson.com/excel/codemods.htm



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


"quartz" wrote in message
...
Hello. I'm hoping an MVP or someone who knows can help me here.

1. Can someone please explain to me what a "CodePane" is and
how it differs from a "CodeModule" or, for that
matter, a window?

2. If I have code stored in a sheet module, how can I get a
reference to the code pane that that sheet module is in?

3. How can you determine or equate the code pane object to any
other object in the VBE using VBA?

4. Your example code regarding 2 & 3 above would be most
appreciated.

Thanks in advance for your assistance.

Mark,

There is no way to determine, at run time, the procedure name or
the module name in which an error occurred. You would have to
hard code the name of the procedure as a constant in each
procedure, and the name of the module as a Private constant in
each module. Some add-ins, such as MZ Tools (www.mztools.com)
can automate this editing procedure. The only thing you can get
automatically at run time is the project name, using Err.Source.
If you use line number labels in a procedure, the Erl function
will return last line number executed before the error occurred.

So, you could write a module with code like

Private Const ModuleName = "Module1"
Sub AAA()
Const ProcedureName = "AAA"
On Error GoTo ErrH:
10:
' more code
20:
' more code
Exit Sub
ErrH:
MsgBox "Error: Line: " & Erl & " Procedu " & ProcedureName
& _
" Module: " & ModuleName & " Project: " & Err.Source

End Sub


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


"Mark Worthington" wrote in message
om...
Chip et al,

I have devoured the great info on your site and elsewhere, but
cannot
work out how to "easily" determine a standard module name in
VBA. It
would appear NOT to be simply available via the VBComponents
collection etc, but I did come across your method, ref
Application.VBE.ActiveCodePane.CodeModule.Parent.N ame. This
seems not
to require a reference in VBA to the VBA Extensibility library
.....

With regard to the original question no. 3 :

3. How can you determine or equate the code pane object to any
other
object in the VBE using VBA?

I would like to be able to determine the procedure, module &
project
names so that my standard error handler can say "Error 1004
..... in
Procedure x, Module y, Project z." So I would like to ask if
anyone
can enlighten me on determining the above names.

Many thanks,

Mark

"Chip Pearson" wrote in message ...
Mark,

There is no way to determine, at run time, the procedure name or
the module name in which an error occurred. You would have to
hard code the name of the procedure as a constant in each
procedure, and the name of the module as a Private constant in
each module. Some add-ins, such as MZ Tools (www.mztools.com)
can automate this editing procedure. The only thing you can get
automatically at run time is the project name, using Err.Source.
If you use line number labels in a procedure, the Erl function
will return last line number executed before the error occurred.

Chip & Tom,

Many thanks! I must be learning something when I beat you two to the
same answer … but then I'm using your own previous postings! I was a
busy boy last night on the web…

Tom : ta for the VBE extensibility note. I can just about get my head
around late binding.

To all out there, check out Chip's Tools page,
http://www.cpearson.com/excel/xltools.htm. There I found "Google
Search Utility by Ron de Bruin" which has enabled me to discover many
answers to my many questions. I also came across MZ Tools which gives
a neat automatic method. (more later). I really recommend Stephen
Bullen's Smart Indenter as a wonderful VBA utility, it deserves a
place on the Tools page…..

Chip : I wanted to avoid the requirement for a VBE extensibility
reference, and in my search came across your postings about the
ActiveCodePane. I accept that I can't programmatically determine the
procedure name (much like not knowing the bus you're on once you step
on, a simple analogy I used as explanation to the wife!) but are you
sure that you can't determine the module and project names at
run-time?? Sorry to quote you as a witness (!) :

Project : Application.VBE.ActiveVBProject.Name

Module : It depends on what you actually need. If you need the
window, use

Application.VBE.ActiveWindow

If you need the code module object, use

Application.VBE.ActiveCodePane.CodeModule.Parent.N ame

Also, Err.Source doesn't always give the project, it sometimes returns
Excel.Application.

I use MZ-Tools to automate the typing, but I prefer to not hard-code
anything (except the procedure name, I accept I have no choice,
strange though it seems), so my standard Error Handler is :

On Error GoTo ErrorHandler ' Enable error-handling routine

Const My_Procedure_Name As String = "Enter Procedure Name Here
.....!"

Mark,

I wouldn't trust the ActiveCodePane or ActiveVBProject to be the
same as the currently running procedure.


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


"Mark Worthington" wrote in message
om...
"Chip Pearson" wrote in message
...
Mark,

There is no way to determine, at run time, the procedure name
or
the module name in which an error occurred. You would have to
hard code the name of the procedure as a constant in each
procedure, and the name of the module as a Private constant
in
each module. Some add-ins, such as MZ Tools
(www.mztools.com)
can automate this editing procedure. The only thing you can
get
automatically at run time is the project name, using
Err.Source.
If you use line number labels in a procedure, the Erl
function
will return last line number executed before the error
occurred.

Chip & Tom,

Many thanks! I must be learning something when I beat you two
to the
same answer . but then I'm using your own previous postings! I
was a
busy boy last night on the web.

Tom : ta for the VBE extensibility note. I can just about get
my head
around late binding.

To all out there, check out Chip's Tools page,
http://www.cpearson.com/excel/xltools.htm. There I found
"Google
Search Utility by Ron de Bruin" which has enabled me to
discover many
answers to my many questions. I also came across MZ Tools which
gives
a neat automatic method. (more later). I really recommend
Stephen
Bullen's Smart Indenter as a wonderful VBA utility, it deserves
a
place on the Tools page...

Chip : I wanted to avoid the requirement for a VBE
extensibility
reference, and in my search came across your postings about the
ActiveCodePane. I accept that I can't programmatically
determine the
procedure name (much like not knowing the bus you're on once
you step
on, a simple analogy I used as explanation to the wife!) but
are you
sure that you can't determine the module and project names at
run-time?? Sorry to quote you as a witness (!) :

Project : Application.VBE.ActiveVBProject.Name

Module : It depends on what you actually need. If you need the
window, use

Application.VBE.ActiveWindow

If you need the code module object, use

Application.VBE.ActiveCodePane.CodeModule.Parent.N ame

Also, Err.Source doesn't always give the project, it sometimes
returns
Excel.Application.

I use MZ-Tools to automate the typing, but I prefer to not
hard-code
anything (except the procedure name, I accept I have no choice,
strange though it seems), so my standard Error Handler is :

On Error GoTo ErrorHandler ' Enable error-handling
routine

Const My_Procedure_Name As String = "Enter Procedure Name
Here
....!"
.
.
.
On Error GoTo 0
Exit Sub

ErrorHandler: ' Error-handling routine.

MsgBox "Error " & Err.Number & vbLf & vbLf & _
Err.Description & vbLf & vbLf & _
"in Procedure : " & My_Procedure_Name & vbLf & _
"in Module : " & _

Application.VBE.ActiveCodePane.CodeModule.Parent.N ame &
vbLf & _
"in Project : " & _
Application.VBE.ActiveVBProject.Name & vbLf & vbLf &
_
"Error Source : " & Err.Source & vbLf & _
"Error Helpfile : " & Err.HelpFile & vbLf & _
"Error Helpcontext : " & Err.HelpContext & vbLf & _
"Error line number : " & Erl

Erl comes into play if I use MZ-Tools to add line numbers.

Is there any way of using Application.Caller? I thought of
having the
procedure call something else, a dummy userform say, thereby
identifying the procedure . can you think of a way, coz I
can't.

Once again, many thanks for the guidance, I'd be interested in
your
comments. It's amazing what you can learn in 24 hours!

Mark

Mark,

I wouldn't trust the ActiveCodePane or ActiveVBProject to be the
same as the currently running procedure.

Chip,

I see what you mean when I test my error handler in VBE. As I step
through, the windows change as another module is accessed, and the
module name (given by
Application.VBE.ActiveCodePane.CodeModule.Parent.N ame) is incorrect.

Seems to work in the "real" world, but of cours my applications are
simple & local.

Ta for the help,

Mark

"Chip Pearson" wrote in message ...
Mark,

I wouldn't trust the ActiveCodePane or ActiveVBProject to be the
same as the currently running procedure.


My last post was premature. Further experimentation has shown a number
of problems with using ActiveCodePane.

I knew I should have trusted you!

Regards,

Mark