Empty a Module within Excel 2016 …

[AreJay]

Using VBA in Excel 2016, how can I empty a standard module with appropriate VBA code without deleting it? Although I am able to delete a module, create a module, rename a module … I simply need to open an existing module and remove all code from within it so that it is completely empty. Thank you.

[YasserKhalil]

Hello
Try this code


'Tools >> References >> Microsoft Visual Basic for Application Extensibility 5.3


Sub Test()
With ThisWorkbook.VBProject.VBComponents("Module2").CodeModule
Debug.Print "Number Of Lines In Module2: " & .CountOfLines
.DeleteLines 1, .CountOfLines
End With
End Sub

[AreJay]

Your solution works except that AFTER the module becomes empty, after the fact, a prompt box pops up stating: “Can’t enter break mode at this time”. It presents 4 options: “Continue”, “End”, “Debug” (grayed out), “Help”.

If you press “Continue”, all VBA code continues to work afterwards, but ignores any and all Breaks or Stops in the code that follows. Do you know what causes this prompt box, and how to avoid it? A nice solution you gave that evaded me, except for the prompt box, which may be related, or not (perhaps some other setting?). Thanks.

[SamT]

I would look at using Application.DisplayAlerts

[AreJay]

Unfortunately, setting the Application.DisplayAlerts = False does not help, as the prompt box still comes up.

[YasserKhalil]

It seems that you are trying to debug the code by pressing F8 .. and this may cause the error
The code is to be executed or run in one shot by pressing F5
Hope that helps

[AreJay]

It happens regardless of pressing F5 or F8. Good catch there about F5 vs F8 though. However, according to MSDN, this seems to be the cause:

A change was made programmatically to the project (https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/vbe-glossary) using the extensibility (add-in) object model. This prevents the program from having execution suspended. You can continue running, or end execution, but can't suspend execution. Referenced is MS Visual Basic for Applications Extensibility 5.3, which is required to access the VBIDE.VBProject code.

I delete a module, add a new one, rename it, add new code to it. This happens error prompt pops up immediately after the new code is added correctly. This occurs each time.

Also, if I empty the module, and adds new code to the existing module, it comes up there occasionally, but not each time. Interesting.

[YasserKhalil]

From Tools >> References >> Check "Microsoft Visual Basic for Applications Extensibility 5.3"