Excel 2004 writes unhealthy files containing VBA code

[Edwin Tam]

After doing at least 800 times of experiment, I come up with the following
conclusion:

If an Excel file containing VBA code is saved in Excel 2004, there is
possibility that
1) on execution of the macro, Excel 2004 will quit unexpectedly; or
2) on loading of the file via VBA, Excel reports a "Invalid data format"
error.

For (1), the possibility will increase dramatically if the file contains
UserForm(s).
Also, for (2), the chance will further increase if the file was saved as an
"Excel Add-In".

For (2), the file contains no data at all, and the VBA code is perfectly
correct with no compile error.

However,
- such problematic files won't crash Excel X.
- If the files are re-saved in Excel X (e.g. the Excel Add-In is re-created
in Excel X), there is high possibility that the problem will solved. (But
this is not guaranteed.)


(BTW, I do not used to post such type of messages here. I used to sent
direct to MS. But since "someone" from MS recently suggested me to post to
the newsgroup, I have no choice. Hope they'll read this...)


Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Bob Greenblatt]

After doing at least 800 times of experiment, I come up with the following
conclusion:

If an Excel file containing VBA code is saved in Excel 2004, there is
possibility that
1) on execution of the macro, Excel 2004 will quit unexpectedly; or
2) on loading of the file via VBA, Excel reports a "Invalid data format"
error.

For (1), the possibility will increase dramatically if the file contains
UserForm(s).
Also, for (2), the chance will further increase if the file was saved as an
"Excel Add-In".

For (2), the file contains no data at all, and the VBA code is perfectly
correct with no compile error.

However,
- such problematic files won't crash Excel X.
- If the files are re-saved in Excel X (e.g. the Excel Add-In is re-created
in Excel X), there is high possibility that the problem will solved. (But
this is not guaranteed.)


(BTW, I do not used to post such type of messages here. I used to sent
direct to MS. But since "someone" from MS recently suggested me to post to
the newsgroup, I have no choice. Hope they'll read this...)


Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

Are you finding this behavior consistent with "new" workbooks? That is,
newly created with newly added modules? Have you tried "cleaning" the
workbook - saving all the module code as text; deleting the modules.;
copying the worksheets to a new workbook; and then importing the saved code?
I have not seen the problems you are describing at anywhere near the
frequency you appear to be having them.

 

[Edwin Tam]

Cleaning the workbook won't solve the problem. Even if it does, it happens
again very soon. Sometimes, for the same file, it's guarantee to crash Excel
2004. Sometimes, you never know when it won't crash.

"Cleaning" workbook has been a common method in Excel for Windows, because
it can be automated. But it cannot be automated easily on the Mac. Therefore,
on the Mac, it must be done manually.

Also, it is not always practical. It is the "academic" solution. For myself,
my project has more than 25 add-in files, with nearly 1000 pages of VBA code.
My shareware "Excel Power Expander" was released half year ago. I've been
trying to produce a Mac version (to be released as a freeware, so,
non-commerical, can be mentioned here). I spent nearly 5 months just hunting
in the code for the numerous "non-error errors". I swear the code is 99.99%
healthy and error-free. I've also been trying my super best to adapt the code
to "Mac-friendly". Yet, there are still numerous places which causes Excel
2004 to crash mysteriously. (Not Excel X.)

I just feel extremely frustrated. I seemed to be debugging some error-free
code, testing my luck by trial-and-error, testing for tricky work-arounds.
It's just like loading a Playstation game and pressing the buttons in strange
combinations, hoping to find the secret code which restores the life of the
character.

Yes, to me, a Mac + Excel 2004 = Playstation 3 game console.

Regards,
Edwin Tam