Deleting Worksheet Code in a Macro

Summary: If you are writing some complex macros, you may want those macros to actually delete VBA procedures associated with different worksheets in your workbook. You can do the deletion by following the advice provided in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Jean-Louis would like to write a VBA procedure that deletes the code attached to a specific worksheet. He knows how to delete procedures stored in modules, but not how to do it when they are stored in the sheet.

The good news is that if you know how to delete macros within a module, you can apply the same technique to delete it within a sheet. The difference is that you would use the sheet name rather than the module name when referring to the component you want to delete.

For instance, if you are referring to code in a module in a workbook, you normally do it by referencing the containing module in this manner:

ActiveWorkbook.VBProject.VBComponents("Module1")

To refer to code contained within a worksheet, you would use this syntax, instead:

ActiveWorkbook.VBProject.VBComponents("Sheet1")

For other ideas about how to reference VBA code in various ways from within other macros, refer to the following page at Chip Pearson's site:

http://www.cpearson.com/excel/vbe.aspx

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3274) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

A Picture is Worth Thousands! Your worksheets are not limited to holding numbers and text. You can also add graphics or easily create charts based on your data. Excel Graphics and Charts, available in two versions, helps you make your graphics and charts their absolute best.

Check out Excel Graphics and Charts today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Deleting Worksheet Code in a Macro