Userform/VBA Version Issues?? Excel for Mac

[Mane]

Hi,

I have a Userform created with VBA code in Excel 2003 (Windows. Sorry, I don't know the VB version). I've run this code through Excel 2010 (Windows) and it seems to work fine. When I try and run it in Excel for Mac 2004(VB 11.5), I get a "Sub or Function not defined" error, highlighting a call to the 'Replace' function.

This suggested to me it wasn't able to use all VB functions properly, so with a bit of Googling, I tried looking at Tools > References in the VB editor. There were some things in here called VB project and VB converter which were unchecked but when I try to select these and save the changes i get an error: "name conflicts with existing module, project or library" error.

Can anyone help with this?? Or at least help me better understand what the References options actually do.

Many thanks,

Pat

 

[Tinbendr]

Apparently the Replace function is not available in Mac 2004. See message #9 on a replacement.

 

[Mane]

Hi thanks for that.Unfortunately that replacement doesn't work for me

application.substitute()

gives a runtime error 424 object required.

Any ideas??

I just want to make the code as universal as I can, so it can run on as many different version of excel/vb as possible. Currently wrking on Excel Windows 2003 & 2010 but not Mac. Thanks

 

[BrianExcel]

Soemthing to consider is adding an IF THEN statement, containing identical code but written for both Windows & Mac.

In other words, write the same code twice (but with whatever small differences are needed to ensure it runs smoothly in both scenarios) and then attach it to an IF THEN statement that checks the application somehow.

So IF Application.Version = Windows Then
RUN Windows Code
End IF

IF Application.Version = MAC Then
RUN MAC Code
End IF

Something like that "might" work, but not sure how else to do it. MAC and Windows are obviously two very different O/S's, so I think you will continue to see problems if you try writing one block of code that you try running on multiple platforms.

Good luck.

 

[Mane]

Thanks Brian, That's a good idea. I know I'll never get something completely universally compatible.

Any ideas on why i can't use Replace() or Application.substitute() on my Mac though?? Any other replacements for Replace()??

Thanks again for all your help.

 

[BrianExcel]

Honestly no, no idea. I am new to Mac myself and just got Office11 installed, so I haven't had time to go through and check out what works VBA-wise, etc.