folajinadu
04-25-2006, 06:57 AM
I have the following macros that clears all code and remove all modules from my application and it runs fine when using Excel 2002. Recently, I have users that run Excel 2003 and Excel crashes when it tries to remove or delete the modules. See the code below:
Note: Excel crashes on the 2nd subroutine.
Sub DeleteAllVBA()
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 vbext_ct_Document
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
Case Else
End Select
Next VBComp
End Sub
Sub remove_modules_user_form()
'declare a component in the project
Dim myModulesAndUserForm As VBComponent
Dim wb As Workbook
Set wb = ActiveWorkbook
Application.StatusBar = "Removing modules from spec book output"
For Each myModulesAndUserForm In wb.VBProject.VBComponents
'if vbcomponent is a standard module or user-form
If myModulesAndUserForm.Type = vbext_ct_StdModule Then
'Remove all modules and user-forms from this workbook
wb.VBProject.VBComponents.Remove myModulesAndUserForm
Else
End If
Next myModulesAndUserForm
Set myModulesAndUserForm = Nothing
End Sub
Note: Excel crashes on the 2nd subroutine.
Sub DeleteAllVBA()
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 vbext_ct_Document
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
Case Else
End Select
Next VBComp
End Sub
Sub remove_modules_user_form()
'declare a component in the project
Dim myModulesAndUserForm As VBComponent
Dim wb As Workbook
Set wb = ActiveWorkbook
Application.StatusBar = "Removing modules from spec book output"
For Each myModulesAndUserForm In wb.VBProject.VBComponents
'if vbcomponent is a standard module or user-form
If myModulesAndUserForm.Type = vbext_ct_StdModule Then
'Remove all modules and user-forms from this workbook
wb.VBProject.VBComponents.Remove myModulesAndUserForm
Else
End If
Next myModulesAndUserForm
Set myModulesAndUserForm = Nothing
End Sub