List all modules in workbook X?

[KR]

This is probably an easy one, I just don't know the commands to use- I'm
looping through a series of workbooks, and need to identify the module names
in each.

If possible, it would be really, really helpful to know when each module was
last updated- in some cases there are modules with the same name, some of
which were imported replacements for older (broken) code, so if I can figure
out which workbooks have that module /and/ which ones are the old ones that
still need to be replaced, that would be very, very cool.

Thanks for any help or advice,
Keith

 

[Chip Pearson]

The following code will list all the modules in the
ActiveWorkbook. There is no way to determine when a module was
last modifed.

 

[Chip Pearson]

The following code will list all the modules in the
ActiveWorkbook. There is no way to determine when a module was
last modified.

Dim VBComp As VBIDE.VBComponent
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Then
Debug.Print VBComp.Name
End If
Next VBComp

You'll need a reference to the Extensibility library. In VBA, go
to the Tools menu, choose References, and scroll down to
"Microsoft Visual Basic For Application Extensibility Library"
and put a check next to it.

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

 

[KR]

Chip-

Thanks for the snippet, I'll give it a try.

Just curious, is there a way to pull in a specific line of code? Since I
know which module was modified, if I could pull in, for example, the 15th
line in that module, then I could compare it against the 15th line in the
modified (new) module to see if it matches up. It wouldn't require a date
but would still allow me to detect outdated modules. Any suggestions on how
I might go about this?

Many (many many) thanks,
Keith

 

[Chip Pearson]

Keith

Dim CodeLine As String
Dim VBComp As VBIDE.VBComponent
For Each VBComp In ActiveWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Then
CodeLine = VBComp.CodeModule.Lines(15, 1)
Debug.Print CodeLine
End If
Next VBComp

See www.cpearson.com/excel/vbe.htm for details about working with
the Extensibility libary.


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

 

[KR]

Chip (or others)-

I thought I had this code working, but now I'm getting a weird error- the
relevant code is below. The first workbook opens, then I get a pop-up error:
Run time error '-2147024890 (80070006)': System Error
&H80070006(-2147024890). The handle is invalid.

When I look in the code (debug) and mouseover to get the VBComp.Type the
mouseover box shows: VBComp.Type = <Method 'type' of object '_vbComponent'
failed> and it actually errored out on the "if VBComp.Name..." statement.
I'm way out of my league here... I have added the iserror(VBComp.type) to
try to just bypass this and keep the workbook going, but it still bugs
out...

Any help would be greatly appreciated!
Thanks,
Keith

<snipped code that gets all workbook names in directory and adds them to
MyFiles()>
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)

'lets me verify how many workbooks have been processed
Application.StatusBar = "Processing " & Fnum & " of " &
UBound(MyFiles) & " -> " & MyFiles(Fnum)

'open as readonly, called "mybook"
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0, False)
Application.EnableEvents = True

For Each VBComp In mybook.VBProject.VBComponents
If IsError(VBComp.Type) Then
'do nothing
Else
If VBComp.Type = vbext_ct_StdModule Then
'MsgBox VBComp.Name

If VBComp.Name = "ValidateAndPasteData1" Then
mybook.VBProject.VBComponents.Remove VBComp

If VBComp.Name = "ValidateAndPasteData" Then
(etc.)