code to remove all macros from a workbook

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
This had been working without issue previously but today when I run debug on it there is a choke at the point of the case statement indicating "variable not defined". I believe I'd originally gotten this code here though I could be wrong. Can anyone explain what the issue is on this?

Code:
Private Sub RemoveAllVBA()

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
 
you're WELCOME!!

When I think about this one: it's a good thing that you had
"Option Explicit"
on top of the module. (not displayed in your code, but that errormessage can only occur when you have it)

Perhaps
"variable not defined"
didn't sound helpful at first glance, but ...
vbext_ct_StdModule is highlighted in blue when you get the error. So this means that your VBA "thinks" that "vbext_ct_StdModule" is a variable: surely you didn't mean that the expressions vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule should be variables...
It's a pity that the errormessage can't be more specific sometimes :-( or even better: repair the problem :wink:

have a nice weekend!
Erik
 
Upvote 0

Forum statistics

Threads
1,226,772
Messages
6,192,928
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top