Consulting

Results 1 to 9 of 9

Thread: Mac Office 2011 VBA

  1. #1

    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: "HardDriveNameocuments: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

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.

  3. #3
    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

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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]

  5. #5

    Mac Office 2011 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

  6. #6

    Related code

    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]

  7. #7
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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]

  8. #8
    VBAX Newbie
    Joined
    Apr 2012
    Posts
    2
    Location

    Migration vba excel from win to mac office 2011

    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

  9. #9
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •