Mac VBA compile error

Mac VBA compile error - Help!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sknowles - 27 Jan 2006 17:32 GMT

Hopefully, someone has seen this & knows a work-around - it's making me
crazy.

I'm developing a set of macros (on Win/XP) and testing them on iMacs.
When I open the workbook, I execute (from the ThisWorkbook.Private Sub
Workbook_Open() routine) a series of 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:

Sub MA_Repair_Links(Sheet_Name As String, Replacement_String As String,
_
wBook As Workbook, MA_Return_Code As Integer)
'
' Marketing Budget Toolkit - Repair Links after sheet import
' (c) 2006 Marketing Acumen, LLC
' Macro written Jan 2006 by Steve Knowles
'
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

Note: this 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

Reply to this Message

Dave Peterson - 27 Jan 2006 20:53 GMT

If you don't get an answer in this newsgroup, maybe you'll get one here:
news://msnews.microsoft.com/microsoft.public.mac.office.excel

I'm confused about what you write--at the top, you say you call it from
workbook_open. At the bottom, you say you don't.

But if you do call it in the workbook_open event, I've seen posts that describe
timing issues (for a lack of a better term) that can occur.

Some people suggested something like:

Private Sub Workbook_Open()
Application.OnTime Now, "Continue_Open"
End Sub

Then in a general module:

sub Continue_Open()
'your real code here
end sub

(But I have no real idea--wintel only here <bg>.)

> Hopefully, someone has seen this & knows a work-around - it's making me
> crazy.
[quoted text clipped - 43 lines]
> Thanks for any insights you can provide...
> - Steve Knowles

Signature

Dave Peterson

Reply to this Message

sknowles - 28 Jan 2006 06:09 GMT

Dave -
Thanks for the pointers - I will try both the newsgroup, and the timing
trick.

To try to clarify my confusing statement: the compile error occurs when
I open the workbook, after I click the "Enable Macros" warning, but
before any of the routines in my Workbook_Open() are executed. But I do
not actually call the MA_Repair_Links sub from that event, anyway -
it's executed only when the user selects an action from the menu, after
the workbook is open.

Thanks again for the tips; I'll let you know how it works out.
- Steve

Reply to this Message

Dave Peterson - 28 Jan 2006 12:09 GMT

After reading your explanation, I don't think that the timing issue is the
problem.

But good luck finding, er, solving the problem.

> Dave -
> Thanks for the pointers - I will try both the newsgroup, and the timing
[quoted text clipped - 9 lines]
> Thanks again for the tips; I'll let you know how it works out.
> - Steve

Signature

Dave Peterson

Reply to this Message