Mac Office 2011 VBA

Hi all,

This is my first post. Hope I am in the right place.

I have written a lot of code on a PC for a MAC with Office 2011. I guess I had a dumb hope that all the coding would be compatible. Not.

Am in the process now of debugging a "Missing library" error message. No mention made of the library missing. The references in the MAC match those in my PC.

I don't have a Mac, so experimentation is limited.

I am wondering if anyone knows of PC to MAC (2011) VBA documentation on what works and what does not.

Based on reading older articles on the Internet, I have the following list of suspects:

msoFileDialogFolderPicker
InstrRev
ShellApp
(ex: Set ShellApp = CreateObject("Shell.Application":ShellApp.Open s )


MAC directory referencing using colons rather that backslash
Example: "HardDriveName:Documents:Test.doc"
(see Ken Pulls article: vbaexpress.com/kb/getarticle.php?kb_id=559

Any tips on known problems with the list above or direction to further reading is appreciated.


Thanks & regards,
mauryb

ShellApp sounds like a probable suspect.

Concerning file path separator there is a built in character
Application.PathSeperator

Also, Mac does not support ActiveX.
StrReverse??? I'm away from my Mac at the moment, so I'm not sure.

Excel VBA 2011 is a fairly big advance from 2004 VBA, so I'm not as confident about its features as I am about 2004. (If you are going cross platform, you might want to write for 2004 unless you are sure that all of your users have upgraded to 2011) (Excel 2008 did not support VBA, so....)

It might be best if you posted questions about specific issues as they arise.

The only other general advise I could give is to use conditional compiling
[VBA]#If Mac Then
Rem code for Mac
#Else
Rem code for Windows
#End[/VBA]

I hope this helped.

mikerickson,

Thanks for the reply.

I'll look into the shellapp right away.

My users all have 2011. No activex.

The reason I mentioned InstrRev (somewhat the REVerse of the string function "Instr", but counts instead from the right), is it was said to be a known problem in Mac 2004.

Was wondering if it still is.


What about the paths on a 2011 Mac machine? Are they separated by colons instead of the backslash?

If so, then my routine to get the program file folder is also a problem.

Take a look:

[VBA]Public Sub GetPaths(Optional LL As Long)
With ThisWorkbook
L = Len(.Name)
LL = Len(.FullName)
Base_Path = Left(.FullName, LL - L - 1) & "\"
ArchiveFiles_Path = Base_Path & "Archive Files" & "\"
End With
End Sub[/VBA]

I know your time is important,

Thanks again.

mauryb

This demonstrates Application.PathSeparator[VBA]Public Sub GetPaths(Optional LL As Long)

With ThisWorkbook
L = Len(.Name)
LL = Len(.FullName)

Base_Path = Left(.FullName, LL - L - 1) & Application.PathSeparator
ArchiveFiles_Path = Base_Path & "Archive Files" & Application.Separator

End With
End Sub[/VBA]
But you might want to use the .Path property of the workbook.[VBA]Sub GetPaths2()

Base_Path = ThisWorkbook.Path & Application.Separator
ArchiveFiles_Path = Base_Path & "Archive Files" & Application.Separator

End Sub
[/VBA]

Very nice.

That's one down and some to go.

Assuming strColon = Application.Separator, I'll do a manual work-around for the InStringReverse function, find away to open the program folder (without shell.open) and head straight back to the drawing board to see what else happens.


Mac 2011 vba is obviously un-charted territory--certainly for me.


Thank you, mikerickson.


mauryb

Here's some code that addresses the differences between Mac and PC path names:

[VBA]Sub GetNewFileName(File_name$, sName)
Select Case MacOrPC("")
Case "Mac"
sel = ":"
ff = "XSL8"
Case "PC"
sel = "\"
ff = "Excel (*.xls),*.xls"
End Select

File_name$ = Application.GetSaveAsFilename(Initialfilename:="New file", FileFilter:=ff)
kout = 1

sName = File_name$
Do 'Get file short name
kn = InStr(sName, sel)
If kn <> 0 Then
sName = Mid$(sName, kn + 1, Len(sName))
Else
Exit Do
End If
Loop
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function MacOrPC(Path) As String
If Path = "" Then Path = CurDir()
' ' debug.Print Path
MacOrPC = "Mac"
nx = Len(Path)
For i = 1 To nx - 1
If Mid$(Path, i, 2) = ":\" Then MacOrPC = "PC"
Next i
End Function[/VBA]

You can use this test instead of the MacOrPC function
[VBA]If Application.OperatingSystem Like "*Mac*" Then
MsgBox "I'm a Mac"
Else
MsgBox "This machine is a PC"
End if[/VBA]

i'm a newer mac user
i have bought 2011 for mac, thinking there was, full compatibility with office for Windows.
instead i have some problem with a couuple of button and some lines of code.
Now i don't have much time to spend to translate the file. someone can help me if i send the file, since it use for work i could pay for it.
thanks in advance:banghead:

felisida,

Welcome to the forum.
This sub-forum is not a good place for that kind of request. Not very many people visit the Mac forum.

A google search for Excel consultants could find you what you want.