Visual Basic Macro Incompatibility

[AnaeromyxoMan]

Hi,

Recently, I had a colleague help write a macro in Visual Basic in Excel
on a Windows PC. The Macro runs fine in Excel running on a PC, however
it crashes in Excel on my Mac and gives the error: "Complile Error:
Sub or Function not defined". I am running the latest version of Excel
and have OS 10.3.9 on my G4 computer. It is a bit frustrating since it
would really be nice to have this macro run on my Mac. Any suggestions
would be greatly appreciated.

AnaeromyxoMan

 

[JE McGimpsey]

Recently, I had a colleague help write a macro in Visual Basic in Excel
on a Windows PC. The Macro runs fine in Excel running on a PC, however
it crashes in Excel on my Mac and gives the error: "Complile Error:
Sub or Function not defined". I am running the latest version of Excel
and have OS 10.3.9 on my G4 computer. It is a bit frustrating since it
would really be nice to have this macro run on my Mac. Any suggestions
would be greatly appreciated.

The most common cause is that MacVBA is version 5.00 (the same as
WinXL97), so any version 6.x commands will give a compile error.

I develop cross-platform all the time. What I do is develop my own
version of the needed command and use conditional compilation to allow
WinXL to run native code and MacXL to run the substitute command.

For instance, if the code uses the VBA6 Replace command, you could do
something like this (put it in a regular code module):

#If Mac Then
Public Function Replace(text As String, old_text As String, _
newText As String)
Replace = Application.Substitute(text, _
old_text, new_text)
End Function
#End If

Then when the compiler hits the line

sMyString = Replace(sMyString, "a","b")

the VBA6 function will be called in WinXL, but MacXL will call the code
above.

Note that the Substitute() function is not a direct equivalent for VBA's
Replace. If you need a direct equivalent, you'll have to roll your own,
or get one from one of several web sites that have that kind of info.

Similar functions written in VBA5 are available for VBA6 functions. A
Google search should find them.