VB Extensibility library and "late binding"

D

Dennis

XL 2003

Would like to strip all VBA code especially Excel 4 macro sheets.

The code below is from Jim Rech.

The problem is that when I run the code it produces an error message (with
"ThisRef As Reference" - as the highlighted stop point):

Compile error
User-defined type not defined


In my Google research, I found some references to "late binding".

This is my first time with
1)VB Extensibility library or
2)"late binding" (I have no clue)

How do I modify the code below?

I am assuming that the reason there is no reference in the code is because
the VB Extensibility Library can be different for each XL installation.

Any help with as to the underlying theory would be a plus.

TIA

Dennis

*******************************************************

''Needs a reference to the VB Extensibility library set
'Removes from active workbook all:
''Regular modules
''Class modules
''Userforms
''Code in sheet and workbook modules
''Non built-in references
''Excel 4 macro sheets
''Dialog sheets
Sub RemoveAllCode_()
Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef As Reference, WS As Worksheet, DLG As DialogSheet
Set ThisProj = ActiveWorkbook.VBProject
Set AllComp = ThisProj.VBComponents
For Each VBComp In AllComp
With VBComp
Select Case .Type
Case vbext_ct_StdModule, vbext_ct_ClassModule, _
vbext_ct_MSForm
AllComp.Remove VBComp
Case vbext_ct_Document
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
Next
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
Next
Application.DisplayAlerts = False
For Each WS In Excel4MacroSheets
WS.Delete
Next
For Each DLG In DialogSheets
DLG.Delete
Next
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top