How to access components of loaded Add-In Projects?

[Greg Maxey]

Hi,

As part of a larger personal project, I am trying to build a list of
each VB project loaded in a current document and a list of the
individual modules and procedures in those projects.

Using the code shown below, I have been able to get the names of all
loaded projects and the individual modules/procedures of the active
template and open documents.

I am having a problem with the Add-Ins. I am beginning to believe
that there may not be a VBA solution when I discovered the protection
property of the project is "Read" only. Still I thought I would ask
here.

A secondary question has to do with my code in general. While I don't
have as much exasperation these days working with arrays, I am still
not very confident or efficient. While the process I have written is
working as I expected I don't know if it is sound or correct. I would
appreaciate some feedback. What I have done is simple monkey see,
monkey do. This monkey would really like to finally understand this
concept of variants, arrays, an array of variants, etc.

I remember either Jonathan West, JGM or Tony Jolans tried to explain
it to me one time, but I can't locate that exchange. If someone would
take this code and try to educate me again I will appreciate it.
Thanks.

Option Explicit
Sub ScratchMacro()
Dim i As Long
Dim j As Long
Dim pProjName As String
Dim vProjects() As String
Dim oComp As VBComponent
Dim ListArray As Variant
For i = 0 To Application.VBE.VBProjects.Count - 1
ReDim vProjects(Application.VBE.VBProjects.Count)
On Error GoTo Err_Handler1
pProjName = Application.VBE.VBProjects.Item(i + 1).FileName
pProjName = Right(pProjName, Len(pProjName) - InStrRev(pProjName,
"\"))
vProjects(i) = pProjName
For Each oComp In Application.VBE.VBProjects.Item(i +
1).VBComponents
If oComp.Type = vbext_ct_StdModule Or oComp.Type =
vbext_ct_Document Then
ListArray = ListMacros(oComp)
For j = 0 To UBound(ListArray) - 1
MsgBox oComp.Name & ": " & ListArray(j)
Next j
End If
Next oComp
ReEntry:
Next
Exit Sub
Err_Handler1:
If Err.Number = 50289 Then
'Any way to access the project components that are in loaded Addins?
'Application.VBE.VBProjects.Item(i + 1).Protection = False
Resume
End If
Resume ReEntry
End Sub
Public Function ListMacros(oModule As VBComponent) As Variant
'Set reference to the VBA Extensibility library
Dim pString As String
Dim lngLineCount As Long
Dim lngModLevelLines As Long
Dim pName As String
pString = ""
lngModLevelLines = oModule.CodeModule.CountOfDeclarationLines
If lngModLevelLines > 0 Then
lngLineCount = lngModLevelLines + 1
Else
lngLineCount = 1
End If
Do While lngLineCount <= oModule.CodeModule.CountOfLines
pName = oModule.CodeModule.ProcOfLine(lngLineCount, vbext_pk_Proc)
pString = pString & pName & ","
lngLineCount = lngLineCount +
oModule.CodeModule.ProcCountLines(pName, vbext_pk_Proc)
Loop
ListMacros = Split(pString, ",")
End Function

 

[Jean-Guy Marcil]

Greg Maxey was telling us:
Greg Maxey nous racontait que :

Hi,

As part of a larger personal project, I am trying to build a list of
each VB project loaded in a current document and a list of the
individual modules and procedures in those projects.

Using the code shown below, I have been able to get the names of all
loaded projects and the individual modules/procedures of the active
template and open documents.

I am having a problem with the Add-Ins. I am beginning to believe
that there may not be a VBA solution when I discovered the protection
property of the project is "Read" only. Still I thought I would ask
here.

A secondary question has to do with my code in general. While I don't
have as much exasperation these days working with arrays, I am still
not very confident or efficient. While the process I have written is
working as I expected I don't know if it is sound or correct. I would
appreaciate some feedback. What I have done is simple monkey see,
monkey do. This monkey would really like to finally understand this
concept of variants, arrays, an array of variants, etc.

I remember either Jonathan West, JGM or Tony Jolans tried to explain
it to me one time, but I can't locate that exchange. If someone would
take this code and try to educate me again I will appreciate it.
Thanks.

Hi Greg,

While I do not pretend to be whole lot more advanced than you are, I am also
of the Monkey See/Monkey Do category!, I can see a few things in your code:

For i = 0 To Application.VBE.VBProjects.Count - 1
ReDim vProjects(Application.VBE.VBProjects.Count)

The For statement seems OK as an array is usually index from 0 to 1-minus
the total count.
But, the ReDim statement has a few problems:
By declaring the array size with "Application.VBE.VBProjects.Count", you are
effectively creating an array with one too many item. If you have five
Projects, you will end up with an array bound from 0 to 5, or 6 items.
Also, you are redefining the Array at each pass in the For Next loop. I
would define the array size before launching the loop, or after.
And, as is, because you do not use the Preserve Key Word, the array is wiped
out every time the line is executed.

So, I would do something like:

ReDim vProjects(Application.VBE.VBProjects.Count - 1)
For i = 0 To Application.VBE.VBProjects.Count - 1
....

Or, sometimes we do this:

ReDim vProjects(1000) 'We use a number we know to be way too big so all
items will fit in the array
For i = 0 To Application.VBE.VBProjects.Count - 1
....
Next
ReDim Preserve vProjects(i - 2)

Since the array is 0 bound, if we had 5 project, by the time we get to the
ReDim statement, i = 6, because we need the array to be from 0 to 4, we use
i-2 and the Preserve key word so as to not wipe out the content.

Finally, I am no expert with VBProjects and VBComponents, but I see two
causes for the error 50289:
One, the project is protected by a password. As you know, we cannot use
VBA to open password protected VBA project.
The second cause is if the project is not password protected, but it is not
actually opened, in those case, on my machine, the line
For Each oComp In Application.VBE.VBProjects.Item(i + 1).VBComponents
throws the error.

So, you have to open the said add-in and get to its contents. But you have
to be careful because the exact same line will throw the exact same error
when the add-in is password protected... Here is another version of you code
that accounts for all that. I did not touch or really examine the ListMacros
function as it seemed to work perfectly well as is and I never encountered
errors when testing the main macro.


'_______________________________________
Sub ScratchMacro()
Dim i As Long
Dim j As Long
Dim pProjName As String
Dim vProjects() As String
Dim oComp As VBComponent
Dim ListArray As Variant
Dim docTemp As Document

ReDim vProjects(Application.VBE.VBProjects.Count - 1)

For i = 0 To Application.VBE.VBProjects.Count - 1
On Error GoTo Err_Handler1
pProjName = Application.VBE.VBProjects.Item(i + 1).FileName
vProjects(i) = Right(pProjName, Len(pProjName) - InStrRev(pProjName, "\"))

For Each oComp In Application.VBE.VBProjects.Item(i + 1).VBComponents
If oComp.Type = vbext_ct_StdModule Or oComp.Type = vbext_ct_Document
Then
ListArray = ListMacros(oComp)
For j = 0 To UBound(ListArray) - 1
MsgBox oComp.Name & ": " & ListArray(j)
Next j
End If
Next
ReEntry:
If Not docTemp Is Nothing Then
docTemp.Close wdDoNotSaveChanges
Set docTemp = Nothing
End If
Next
Exit Sub

Err_Handler1:
Select Case Err.Number
Case 50289
If Not docTemp Is Nothing Then
MsgBox vProjects(i) & " is password protected."
Resume ReEntry
Else
Set docTemp = Documents.Open(pProjName)
Resume
End If
Case Else
Resume ReEntry
End Select

End Sub
'_______________________________________

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Greg Maxey]

JGM,

All seems to make sense and I will have a crack at it tonight. I
would appreciate your (or anyone else's) comment on how I used the
declaration of ListArray (is variant the right one?), how I used the
Function declaration, and the use of split.

Thanks again.

 

[Jean-Guy Marcil]

Greg Maxey was telling us:
Greg Maxey nous racontait que :

JGM,

All seems to make sense and I will have a crack at it tonight. I
would appreciate your (or anyone else's) comment on how I used the
declaration of ListArray (is variant the right one?), how I used the
Function declaration, and the use of split.

Thanks again.

I guess you should use a String instead of a Variant since you are dealing
with strings, and thus declare the Function as a string:

For example:

'_______________________________________
Sub test()
Dim listArray() As String
Dim strTest As String

strTest = "123,456,789,012,345,678,901,234,567,890"
listArray = ListMacros(strTest)

End Sub
'_______________________________________

'_______________________________________
Function ListMacros(ByRef strTest As String) As String()

ListMacros = Split(strTest, ",")

End Function
'_______________________________________


As for the Split, I think it is appropriate because commas are not allowed
in module or macro names, so it is pretty safe to use that as a delimiter.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Greg Maxey]

Hi JGM,

Your suggestions worked well. The only thing I changed was:

Set docTemp = Documents.Open(pProjName, Visible:=False)

Thanks.