MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBComponets & VBIDE Extensibilty Object Model ?

  Asked By: Kristopher    Date: Oct 21    Category: MS Office    Views: 2742

With this single line of code shown below in Excel( w/VBA ), it returns the
Module Name at the instance when a particular subroutine is entered.
modName = Trim(Replace(ThisWorkbook.VBProject.VBE.ActiveCodePane.Window.Caption,
"(Code)", "", , , vbTextCompare))

With this single line line shown below, it returns the total number of lines in
the current compone/procedure that this line of code is in.
Lines =
veCodePane.Window.Caption, "(Code)", "", , ,
vbTextCompare))).CodeModule.ProcCountLines("Subroutine1", vbext_pk_Proc)

What I would like to be able to solve, is at the instance when the subroutine(
component/procedure ) is entered.
Is to know the componet/procedure's name, without having to "hardcode" the
component/procedure's name into a localized string variable.
Does anybody know how I can retrieve it, at the instance when the
component/procedure is entered ?



3 Answers Found

Answer #1    Answered By: Davi Costa     Answered On: Oct 21

Function ProcOfLine(ByVal Line as Long, ByRef ProcKind as vbext_ProcKind) as

Check out my article on TechTrax:
"Using the VBA Extensibility Library"

What are you working on? Sounds interesting...

Answer #2    Answered By: Sydney Thompson     Answered On: Oct 21

I am working on one thing for sure and possibly a second thing.
The first thing, is to possibly utilize the ProgressBar Function call that Greg
Chapman talked about.
The reason to know all the information about the component/procedure at the
instance of its entry.
Especially knowing the total  number of lines  in the specific component/procedure
and the module name.

When having the total number  of lines in the component/procedure, I would then
start the call to the
ProgressBar function at 0% and knowing what the limit# is, in order to get to
Get the picture, as each component/procedure is entered.
It will cause the ProgressBar looking like a dynamically progress bar moving
back& forth all the time
until all the components/procedures are executed from start to finish in the
flow path it was executed.

Another good thing in knowing the component/procedure information at the
instance of its entry,
is also to measured the elasped time spent in each component/procedure for
performance tuning.
And, in conjunction to dynamically measure the trace flow of the VBA code  while
it is being execute.
I did this for another company, in order to fine-tune the OS and its software.
We or I did it the hard-way, by hard-coding trace-hooks data in each
component/procedure...( sigh )
From this, we could produce statistical data and reports to fine-tune the
software for optimum performance.

Last of all, the component/procedure name  is also passed to the error handler in
each component/procedure.
Sound even more interesting.

Answer #3    Answered By: Kim Cruz     Answered On: Oct 21

Not exactly what you want I think.... but anyway...

Function fncGetCurrentProcName() As String
' Return the Procedure name  under the cursor.

Dim lnglSLine As Long
Dim lnglELine As Long
Dim lnglSCol As Long
Dim lnglECol As Long
Dim objlCodePane As VBIDE.CodePane
Dim objlCodeModule As VBIDE.CodeModule
Dim strlProc As String

Set objlCodePane = VBE.ActiveCodePane
Set objlCodeModule = objlCodePane.CodeModule

objlCodePane.GetSelection lnglSLine, lnglSCol, lnglELine, lnglECol
strlProc = objlCodeModule.ProcOfLine(lnglSLine, vbext_pk_Proc)

Set objlCodePane = Nothing
Set objlCodeModule = Nothing

fncGetCurrentProcName = strlProc
' ***********************************************************************
End Function

Didn't find what you were looking for? Find more on VBComponets & VBIDE Extensibilty Object Model ? Or get search suggestion and latest updates.