vba/excel

[David Eaton]

I just finished migrating a vba application to Excel on my Mac (Excel
X for Mac Service Release 1). It's a financial application that I've
been using for quite a while both at work and home on a PC. While
rather large, with 4 modules, the whole thing was built from scratch
without any calls to exterior applications. After carefully reviewing
every line of code, I tested it and everything seemed ok. But the
next time I opened the file, and every time since, Excel completely
crashes.

When I open the file and disable macros, I get the following message:
This workbook contains a type of macro ( Microsoft Excel version 4.0
macro) that cannot be disabled. There may be viruses in these macros.

Then when the file opens, there's a button in the middle of the sheet
indicating design mode, and when I go to the Visual Basic Editor excel
crashes. I can't figure it out. I wrote every line of code myself,
and there's just nothing there except for really basic stuff. I also
tried downloading and installing all the updates, to no avail.

Should I just throw in the towel on excel, and spend a lot of time and
effort to learn OpenOffice, or does anyone out there have any clues on
how I can fix this problem?

Cheers,
David

 

[Bernard REY]

David Eaton wrote :

I just finished migrating a vba application to Excel on my Mac (Excel
X for Mac Service Release 1). It's a financial application that I've
been using for quite a while both at work and home on a PC. While
rather large, with 4 modules, the whole thing was built from scratch
without any calls to exterior applications. After carefully reviewing
every line of code, I tested it and everything seemed ok. But the
next time I opened the file, and every time since, Excel completely
crashes.

Do you mean you built it from scratch on the Mac? Or did you build it on the
PC before migrating? If yes, did you use any "ActiveX" controls. This could
be the cause of the problems encountered. But it's difficult to say more
without having a more precise idea of the contents... You may have a look in
the VBA help file to the items "Differences between Excel VBA for Windows
and Excel VBA for the Macintosh" and "Strategies for Developing
Cross-Platform Solutions".

Does the workbook still open on the PC?

When I open the file and disable macros, I get the following message:
This workbook contains a type of macro (Microsoft Excel version 4.0
macro) that cannot be disabled. There may be viruses in these macros.

This doesn't necessairly mean there is a XL4Macro. This comes up in certain
other circumstances. It surely indicates there is a problem, but not much
more.

 

[Neil]

There may be nothing wrong with your code, but the file might be corrupt. I
have found that frequent changes to VBA Excel files can lead to
unexplainable performance problems.

As a result, I keep backups of my VBA code in text files. When I suspect
corruption to a Excel file, I copy the text into the code modules of a new
Excel workbook. The Excel Windows community has a CodeCleaner program,
courtesy of Mr Rob Bovey, that automates something analogous. I'm not aware
of a Mac equivalent (sigh).

Neil

 

[J.E. McGimpsey]

I just finished migrating a vba application to Excel on my Mac (Excel
X for Mac Service Release 1). It's a financial application that I've
been using for quite a while both at work and home on a PC. While
rather large, with 4 modules, the whole thing was built from scratch
without any calls to exterior applications. After carefully reviewing
every line of code, I tested it and everything seemed ok. But the
next time I opened the file, and every time since, Excel completely
crashes.

When I open the file and disable macros, I get the following message:
This workbook contains a type of macro ( Microsoft Excel version 4.0
macro) that cannot be disabled. There may be viruses in these macros.

Then when the file opens, there's a button in the middle of the sheet
indicating design mode, and when I go to the Visual Basic Editor excel
crashes. I can't figure it out. I wrote every line of code myself,
and there's just nothing there except for really basic stuff. I also
tried downloading and installing all the updates, to no avail.

Should I just throw in the towel on excel, and spend a lot of time and
effort to learn OpenOffice, or does anyone out there have any clues on
how I can fix this problem?

I'd try two things first.

Sometimes, especially after a large number of edits, projects get
corrupted. If you have access to the PC, run Rob Bovey's Code
Cleaner:

http://www.appspro.com/utilities/Cleaner.asp

if not, create a new workbook and copy the text from each module to
the new workbook.

Second, with all Office apps closed, rename the Microsoft folder in
the ~:Librariesreferences: folder (where ~ is your home
directory). Start XL - a new folder and prefs will be created. If
the Workbook opens, you can try adding back the other apps'
preferences.

 

[David Eaton]

I deleted the Microsoft folder at
$HOME/Library/Preferences/Microsoft/
I presume that's the one you meant - unfortunately it didn't help. As
far re-doing the code, that's what I did before the problem. I pasted
the text into each module and carefully checked each line for
typos/errors. Then I set a worksheet to call all the functions that I
had just finished writing in VBA. It worked fine, until I closed and
then re-opened the file. I'm throwing in the towel on this, life's
too short to get so frustrated trying to deal with crappy Microsoft
products.