Relating Worksheet(I).Name with VBComponent(J).Name

  • Thread starter Thread starter MWE
  • Start date Start date
M

MWE

In the VBE, each Microsoft Excel Object has a VBComponent name and a
Worksheet name. Programatically, I can interrogate either the
Worksheets or the corresponding VBComponents. How do I tie the two
together?

Given a VBComponent (that is associated with a worksheet), how do I
determine its associated worksheet name or index?

Given a worksheet, how do I determine its associated VBComponent name
or index?

Thanks
 
MWE,

Given a VBComponent, you have to loop through the worksheets to
find the worksheet whose code name is the same as the VBComponent
name. E.g,

Function VBCompToWSName(VBComp As VBIDE.VBComponent) As String
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.CodeName = VBComp.Name Then
VBCompToWSName = WS.Name
Exit Function
End If
Next WS
End Function

Given a Worksheet, you can get its CodeName property to find the
associated VBComponent. For example,

Function WSToVBComp(WS As Worksheet) As VBIDE.VBComponent
Set WSToVBComp =
WS.Parent.VBProject.VBComponents(WS.CodeName)
End Function


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