Hello,
I am trying to use vba to remove the modules behind the active workbook. I modified code I found from an existing source http://www.cpearson.com. (The full code had additional parts I didn't need so I removed any parts that had nothing to do with stripping the VBA)
Sub SaveWithoutMacros()
'PLEASE NOTE : You must choose Tools, References and select the
'Microsoft Visual Basic for Applications Extensibility library
Dim wbActiveBook As Workbook
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
On Error GoTo CodeError
'Now strip all VBA, modules, userforms from the copy
'This code is from Chip Pearson's website http://www.cpearson.com
Set VBComps = wbActiveBook.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
wbActiveBook.Save
Exit Sub
CodeError:
MsgBox Err.Description, vbExclamation, "An Error Occurred"
End Sub
When stepping through the code, it reaches the following line and I get an error message that says "Object Variable or With block variable not set.":
Set VBComps = wbActiveBook.VBProject.VBComponents
Thank you in advance for your help.
Best,
Matt
Bookmarks