Remove Excel 2003 macros easily

Question

I am using Excel 2003 and I have some workbooks that I have to send several days a week to some recipients. These workbooks have macros in it and I don't want the people receiving the file to be able to view the macros.

I know that the macros can be password protected but I also know that this password protection is weak and that several tools exist in order to gain access to the macros without knowing the password.

I am thinking that the best way for people to never get access to my macro code is simply to delete it altogether.

I am aware that I can copy paste the sheets to a new excel file and save it as a new file, which will not contain any macro, or that I can make a copy of the file and delete the every module and class manually. I have a lot of classes so it can be time consuming. However, I find this option time consuming and error-prone.

Placing my macros to an XLA file is not an option either as even on a fully-patched Excel 2003, Excel tends to crash when using XLA files, while the same code works perfectly fine inside the XLS file itself.

I'm thinking that the best option would be to have a command-line program that could remove the macros easily, but after googling a bit, it doesn't seem such a program exist.

Do you see any other options?

Thanks.

Answer 1

I discovered this unanswered question while wandering around the site. You have probably solved the problem by now but just in case.

My solution to this problem is to keep all my macros in workbooks I do not distribute to clients.

It is easy to open another workbook or operate on another. If you have With OtherWorkBook ... End With on the outside, the code in the middle is identical to what it would be if it were in target workbook.

I have never had a failure with this approach.

Answer 2

I run Excel 2003 on Windows XP on a five year old Dell Latitude and I don't have any problems with the twenty (20) xlas that load every day.

The correct answer is to separate your code from your UI and from your data, and not just for this application, but for every one that you write. And that separation is best done with add-ins. But add-ins are only regular Excel files with one bit flipped. So if you don't like xla, just put all your code in an xls, your UI in another xls, and your data somewhere else.

Code in xls files can manipulate other xls files just like an add-in can. But really, just use xla because whatever crashing you've experienced isn't because it was an xla.