Run time Error 13 Type Mismatch Excel 97

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Monk - 25 May 2008 04:02 GMT

Hello

The following code deletes VBA in the file for users however I get a runtime
error 13 on the following line for those that are still using Excel 97. Is
there code that will work for Excel 97 and later versions or is this a
security setting on 97 which I cannot locate.

Error Line
Set VBComps = ActiveWorkbook.VBProject.VBComponents

Full Code
Dim oSht As Object
Application.DisplayAlerts = False 'suppress delete warning
For Each oSht In Sheets
If Not oSht.Visible Then oSht.Delete
Next
Application.DisplayAlerts = True
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

Reply to this Message

Peter T - 25 May 2008 10:48 GMT

Sounds like your project has a reference set to the wrong Extensibility
library. Remove it form tools/refs. You could add the correct reference,
Vbeext1.olb. However for your procedure I wouldn't bother and adapt to Late
Binding

Change the two declarations starting As VBIDE. to As Object and change the
first Case line to
Case 1 To 3

If interested I have a little routine that I keep in my Personal that will
ensure the correct reference is applied in respect to version.

Regards,
Peter T

> Hello
>
[quoted text clipped - 27 lines]
> Next VBComp
> End Sub

Reply to this Message

Monk - 26 May 2008 06:48 GMT

Thanks Peter. Your suggestions work perfectly.

> Sounds like your project has a reference set to the wrong Extensibility
> library. Remove it form tools/refs. You could add the correct reference,
[quoted text clipped - 44 lines]
> > Next VBComp
> > End Sub

Reply to this Message