Mac VBA compile error - Help!

[sknowles]

Hopefully, someone has seen this & knows a work-around - it's making me
crazy. I don't have a lot of experience writing VBA code for this
platform.

I'm developing a set of macros (on Win/XP) and testing them on Macs.
When I open the workbook, I try to execute (from the
ThisWorkbook.Private Sub Workbook_Open() routine) a series of
house-keeping subroutines in our code module. Works fine on (several)
Windows platforms.

However, on (several) Macs, I get the following error:
"Compile error:
Automation error"

which opens the VBA IDE, with the header of the following routine
highlighted - even though this routine is not called from the
Workbook_Open routine. (It is called when the user executes a
particular menu command.)

Sub MA_Repair_Links(Sheet_Name As String, _
Replacement_String As String, _
wBook As Workbook, MA_Return_Code As Integer)

Dim wSheet As Worksheet

Set wSheet = MA_Get_Sheet(Sheet_Name, MA_Return_Code, wBook)
wSheet.Select
wSheet.Unprotect Password:=MA_Pwd
wSheet.Cells.Replace What:="=#REF!", _
Replacement:=Replacement_String, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

wSheet.Protect Password:=MA_Pwd, UserInterfaceOnly:=True

End Sub

The offending subroutine is not executed from the Workbook_Open event.
Even more interesting (to me), I can later run the routine that calls
this module (after quitting the IDE and manually setting my global
variables), and it works fine!

I'm doing most of my testing on Microsoft Excel X for Mac® Service
Release 1 (2001), but am getting the same behaviors on other Mac
versions.

Thanks for any insights you can provide...
- Steve Knowles

 

[JE McGimpsey]

The offending subroutine is not executed from the Workbook Open event.
Even more interesting (to me), I can later run the routine that calls
this module (after quitting the IDE and manually setting my global
variables), and it works fine!

I'm doing most of my testing on Microsoft Excel X for Mac® Service
Release 1 (2001), but am getting the same behaviors on other Mac
versions.

Since it runs fine when you've manually set your globals, I can't think
why this would occur.

The first thing I would try is Rob Bovey's Code Cleaner:

http://www.appspro.com/Utilities/CodeCleaner.htm

Code modules tend to get corrupted over time with editing. It's possible
that there's some corruption that WinVBA ignores but Mac VBA chokes on.