PC to Mac VBA Cross-platform compatibility

[Geoff Martin]

I created what I call a practice sheet generator designed to randomize a
list of items from one worksheet then populate another worksheet. Great for
teaching kids sight words and other applications. However, I created it in
Excel 2000 on my PC and have tried to run it on a Mac using Excel 98 or
higher. It doesn't work at all. On the worksheet that accepts the list,
there are ActiveX controls like CommandButtons that start the code, but
those don't seem to work either.

I don't have regular access to a Mac, but I don't even know how or where to
begin troubleshooting the cross-platform compatiblity issues. Are there
resources out there? Please let me know if I need to provide more details or
code...

Thanks much,
Geoff

 

[JE McGimpsey]

ActiveX is a windows-only technology, so ActiveX controls won't work in
MacXL - use controls from the Forms toolbar instead.

All MacXL versions use VBA5, equivalent to WinXL97. Don't use VBA
methods introduced in VBA6 (such as join, replace, split, etc.). You can
find VBA5 workarounds - look in the archives:

http://groups.google.com/advanced_group_search?q=group:*excel*

There are a very few other gotchas - for instance, the FileFilter
argument to GetOpenFileName works differently, but that should be in XL
Help. You might also look up Conditional Complilation, i.e.:

'Do cross-platform stuff
#IF Mac
'Do Mac Stuff
#Else
'Do Win Stuff
#End if
'Do cross-platform stuff

 

[Geoff Martin]

Thanks JE,

Some follow-up questions. I don't have regular access to Macs with Excel.
Aside from the ActiveX issues, and the general types of exceptions you
mention below:

1) If I can get the program to run on Excel 97, is it fairly likely to work
on a Mac?

2) Because of the slowness of changing header/footer settings, I use
Excel4Macros to set those. Will there be a problem on Mac?

3) What is the code to check the OS and what would it return for Mac or
Windows?

4) I looked briefly, but isn't there a setting one can activate somewhere
that makes it to where you can only use VB5 compatible codes?

Thanks again,
Geoff

 

[JE McGimpsey]

In-line.

Some follow-up questions. I don't have regular access to Macs with Excel.
Aside from the ActiveX issues, and the general types of exceptions you
mention below:

1) If I can get the program to run on Excel 97, is it fairly likely to work
on a Mac?
Yes.


2) Because of the slowness of changing header/footer settings, I use
Excel4Macros to set those. Will there be a problem on Mac?
No.

3) What is the code to check the OS and what would it return for Mac or
Windows?

I use conditional compilation, as I wrote before. You could also use the
Application.OperatingSystem property:

Dim bMacOS As Boolean
bMacOS = (Left(Application.OperatingSystem, 3) = "Mac")
'cross-platform stuff
If bMacOS
'mac specific stuff
Else
'windows specific stuff
End if
'cross platform stuff

4) I looked briefly, but isn't there a setting one can activate somewhere
that makes it to where you can only use VB5 compatible codes?

No. You have to use conditional compilation if you want to include VB6
commands.

I do this all the time in my business. One technique I use is to define
the VBA5 equivalents of VBA6 functions in a separate module, then use
cross-platform code in the remainder. For instance, if I use the VBA6
replace function:

s = Replace(s, "$$", nID)

In a separate module I put

#IF Mac
Public Function Replace(ByRef Text As String, _
ByRef sOld As String, ByRef sNew As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal Count As Long = -1, _
Optional ByVal Compare As Long = vbBinaryCompare _
) As String
'my VBA5 replace function
End Function
#End If

You can find a lot of canned VBA5 equivalents at

http://support.microsoft.com/default.aspx?scid=kb;en-us;188007

They're not particularly optimized, but they work.

(One tip: MacVBA doesn't define the VbCompareMethod method constant used
in the functions at the above link, which is why I changed it to Long in
the Replace function above).

 

[Peter T]

Could I but in and ask what about API's? gather some at least may cause
problems or not work in Mac. Is there a list of those that are safe to use,
if any.

TIA, Peter

 

[JE McGimpsey]

APIs reference windows OS functions and will not work cross-platform.

 

[Peter T]

That's definitive!

Thanks, Peter

APIs reference windows OS functions and will not work cross-platform.

 

[Geoff Martin]

Been away the last few days...Thank you so very much for your help!