VBCompononts.Name...

[brzak]

Is it possible to obtain the name of a VBComponent

i.e. the procedure at the bottom of this page, which lists all
Procedure names in a VBComponent, relies on being supplied the name of
the module / form. However would it be possible to do something along
the lines of:

--------------
For each VbComponent in VBComponents

'do things here
'address the component by something like VbComponent.name (which
itself doesn't exist)
'that is, now we have the name, we can address the right part of the
form.

Next VBComponent
----------------

there doesn't seem to be any simple way of finding the Proc names
either, the only ways of I've seen have been by examining code line
by line.

The List Procedures (http://www.cpearson.com/excel/vbe.aspx)
subroutine I mentioned above is:

-----------------------
Sub ListProcedures()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim NumLines As Long
Dim WS As Worksheet
Dim Rng As Range
Dim ProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule

Set WS = ActiveWorkbook.Worksheets("Sheet1")
Set Rng = WS.Range("A1")

With CodeMod
LineNum = .CountOfDeclarationLines + 1
ProcName = .ProcOfLine(LineNum, ProcKind)
Do Until LineNum >= .CountOfLines
Rng(1, 1).Value = ProcName
Rng(1, 2).Value = ProcKindString(ProcKind)

Set Rng = Rng(2, 1)
LineNum = LineNum + .ProcCountLines(ProcName,
ProcKind) + 1

ProcName = .ProcOfLine(LineNum, ProcKind)
Loop
End With
End Sub

Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As
String
Select Case ProcKind
Case vbext_pk_Get
ProcKindString = "Property Get"
Case vbext_pk_Let
ProcKindString = "Property Let"
Case vbext_pk_Set
ProcKindString = "Property Set"
Case vbext_pk_Proc
ProcKindString = "Sub Or Function"
Case Else
ProcKindString = "Unknown Type: " & CStr(ProcKind)
End Select
End Function

 

[Chip Pearson]

The VBComponent object has a Name property that you can retrieve or use. For
example,

Dim VBComp As VBIDE.VBComponent
For Each VBComp In ThisWorkbook.VBProject.VBComponents
Debug.Print VBComp.Name
Next VBComp

Debug.Print ThisWorkbook.VBProject. _
VBComponents("ThisWorkbook").CodeModule.CountOfLines


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[brzak]

Thanks Chip,

looking at it now it seems silly,

it's like me asking why this sub isn't working:

Sub HowNotToDoIt()
For Each Worksheet in ThsiWorkBook.Worksheets
Debug.Print Worksheet.Name
Next Worksheet
End Sub

oh well, i'll have to maek sure not to make the same mistake again!

great website by the way!

 

[Chip Pearson]

For Each Worksheet in ThsiWorkBook.Worksheets

As a general rule, you should avoid using variable names that are the same
as VBA or Excel reserved words. Excel defines an object named Worksheet and
it can cause confusion if you have a variable by that same name.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[brzak]

thanks, though I intedned to use the VBA / Excel name in that
HowNotToDoIt sub, jsut furtehr illustrating the point I had missed,
i.e. that a variable is required, and that it isn't possible to use
the VBA / Excel name, it just doesn't make sense. Soperhaps if someone
else sees this they will not only see how to do it, but also how not
to do it and when you're not aware of the problem, it can be quite
difficult to see, but once you do then it becomes trivial almost.