Compile and save VBA project bug?

Topic

New Reply

I encountering a weird problem in an Excel 2007 VBA project I’m doing now.

Each time before I close the file, I compile and save. The next time I open up the .xlsm file again, I expect to see the “Compile VBAProject” command grayed out, but it’s not! Why does Excel still think the VBA code is un-compiled?

Has anyone here experienced this problem before?

Thanks.

Reply | Quote

Replies

Well, I discovered the cause of the problem — ActiveX controls (option buttons, command buttons, etc.) which I put in the Excel sheets. I looked at an earlier version of my project without the ActiveX controls and it didn’t have this problem. And if I delete the ActiveX controls in my current version, re-compile and save, the project does remain compiled after re-opening.

Still, I would like to know how I can keep my project in a compiled state even with the ActiveX controls. This has to be an Excel 2007 bug. I definitely don’t recall this happening with the earlier Excel versions.

Reply | Quote

I have NEVER worried about the compiled state of my workbooks. I think you can safely do the same. In fact, I even prefer an uncompiled version of my workbooks because these are smaller in file size.

Try this on a file with some significant amount of VBA:
– Open file, enable macros, compile VBA project, save. Note filesize. (my example: 9852 kb)
– Open same file, DISABLE macros and save. Note filesize has decreased (My example: 8023 kb).

Reply | Quote