VBIDE coding - User defined type not defined error

[staticbob]

Guys,

I'm using the code below from Chip Pearsons site to removed all code on a worksheet that I'm copying. I'm getting this user defined type not defined error on the first line . . any ideas ?

Thanks
Bob

Sub FUBARVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

 

[Mudface]

You need to add a reference to the VBA extensibility library. Click on Tools-References in the VBE, and scroll down and tick the entry for Microsoft Visual Basic for Applications Extensibility 5.3.

 

[staticbob]

Thanks Mud,

But now I get a type mismatch on . . .

Set VBComps = ActiveWorkbook.VBProject.VBComponents


?

Cheers,
Bob

 

[Mudface]

I'm not sure why you'd get a type mismatch, it works OK on my PC. You may run into problems if you're running Excel 2002 or above, you need to click on Tools-Macros-Security and the Trusted Sources tab, then check the Trust Access to Visual Basic Project box.

 

[staticbob]

nah, Im running XL2000 Mud so the trusted sources doesn't apply I presume ?

Cheers
Bob