Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 29

Thread: Limitations on Excel for Mac VBA

  1. #1
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location

    Limitations on Excel for Mac VBA

    I thought it might be helpful if we listed some of the limitations of Excel on the Mac (regarding VBA), so that programmers can account for that if they are working in a cross-platform environment. And it will give us incentive to write to the MacBU at MS to request full-scale support.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  2. #2
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location

    Search features

    From another post, Brad wrote:

    I got so aggravated at the "crippling" of VBA that I write code in Windows Office using Virtual PC emulation on my Mac. This also gives me full access to Microsoft's on-line support, which has also been crippled for Mac users (can't use all of the search features). It might be slow, but at least everything works.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  3. #3
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location

    Lack of support for hover

    Brad also wrote:
    Two other clumsy workarounds for the lack of support for hover:
    ? myVariable in the Immediate window returns the current value
    MsgBox myVariable displays the value on the screen

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  4. #4
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location

    Save as PDF in VBA

    On another board a poster noticed that while in the Print dialog box in Excel, there is an option to "Save as PDF". However, there does not seem to be any way to access that option via VBA on the Mac.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  5. #5
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location

    Thumbs down No Microsoft Scripting Runtime

    This is another area of MS severely limiting the "cross-platform" work. Just discovered to tady when trying to run the Dictionary Object Model macros that Mac does not have Microsoft Scripting Runtime.


    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  6. #6
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location

    A Mac Office site

    Jim McGimpsey is a MS MVP and a Mac specialist. I just found his web page today. He lists some of the bugs/limitations related to XL 2004 and with discussions about each:

    McGimpsey

    McGimpsey's list:

    General and Setup
    • Pasting from XL04 to another application fails (MSKB)
    • Can't open XL2003 files (MSKB)
    • "Permission for this document is currently restricted" when opening XL03 files (MSKB)
    • "Smart formatting" doesn't format a range of date cells


    Worksheet functions
    • COUNTIF() only counts used range


    Charts
    • Chart labels may overlap (MSKB)


    Printing
    • Text in Outline format prints as Bold (MSKB)
    • Borders may not print (MSKB)
    • Double Underlines print as thick Underline (MSKB)


    Userforms
    • LoadPicture method doesn't work
    • Range.Copy method doesn't Work from Userform
    • Focus problems with Userforms


    VBA
    • Event macros are not deleted
    • Deleted or renamed procedures corrupt module
    • Range.Copy method doesn't work from VBE


    ---------

    Okay, somewhere MS has claimed "cross-platform compatibility". Looks like there is a ways to go!

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  7. #7
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by shades
    On another board a poster noticed that while in the Print dialog box in Excel, there is an option to "Save as PDF". However, there does not seem to be any way to access that option via VBA on the Mac.
    "Save As PDF" is not part of Windows VBA, it is installed by Acrobat, or maybe some other 3rd party PDF writer.

  8. #8
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    The bottom line is that VBA in Office Mac is based on a subset of VBA 5, at least prior to Office 2004, but Office 2000, and up, on Windows are using VBA 6.

    I don't know about Office 2004.

    In an off moment, I might accept that MSFT does not support certain object model difference due to the increased cost of implementing for the Mac, but I NEVER will understand why simple string functions such as Split, Join, etc. were not addded to Mac VBA. Rather trivial to do.

  9. #9
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Yeah, to create a PDF just try to print to the acrobat printer and it should work.

  10. #10
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    OK, I'll make it 4 of us using VBA on Mac! Looks like we need some activity around here, so I have a few to add to the list when I remember them. Right now I'm converting Excel VBA code from Office 2000 to Office X on my new machine and hitting plenty of problems. (Yeah, I'm too cheap to get Office 2004 yet).

    The LoadPicture() method above hits me hard because I use it to update images in UserForms. Haven't found any useful workaround yet although I've read that it's been fixed in Office 2004 SP1. Right now I have to use conditional complilation to avoid compiler errors:

    [VBA]#If Not Mac Then
    UserForm.Picture = LoadPicture(picturename)
    #End If[/VBA]

    Next up... the InStrRev() function is not recognized and gives a compiler error. For this one I just created a new function to handle it under both Mac and Win:

    [vba]Function RevSearch(ByVal string1 As String, ByVal string2 As String) As Integer

    RevSearch = 0

    Do While InStr(string1, string2)
    RevSearch = RevSearch + InStr(string1, string2)
    string1 = Right(string1, Len(string1) - InStr(string1, string2))
    Loop
    End Function
    [/vba]

    There are probably more elegant solutions, but I have a lot of ground to cover...

    Another one: Application.GetOpenFileName() always seems to fail for selecting multiple files. Right now I'm using a very cludgy workaround that goes like this:

    i) identify a directory using Application.GetOpenFileName() in single file mode.
    ii) use this directory in a Application.FileFind search. This has to be done with conditional compilation as FileFind will cause a compilation error on Windows.
    iii) list the FoundFiles in a ListBox and allow the user to select files from this list.

    (BTW, I'm using this because I already have some code in there to allow the user to match filenames with comments stored in the spreadsheet before opening the files, so this was just reusing that code.)

    Pasting arrays into worksheets. This one's a little random.

    [vba]Dim MyData() as Variant, n as Integer

    ReDim MyData(1 to lengthoffile, 1 to 1)

    ...

    Do While blah-blah
    Input #1, MyData(n, 1)
    n = n + 1
    Loop

    Sheets("My Sheet").Cells(1, 1).Resize(UBound(MyData, 1), 1) = MyData[/vba]

    ...often fails with a Type Mismatch where file #1 just contains numeric data. It seems that Mac VBA is not as smart as WIN VBA in determining types for Variant. The fix here was to change the Input to:

    [vba]Input #1, TempData
    MyData(n, 1) = Val(TempData)[/vba]

    This forces VBA to recognize the Variant array as numeric.

  11. #11
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    Another limitation is on dates calculus. Excel on Windows has a range between 1/1/1900 and 31/12/9999 while at Mac its from 1/1/1904 through 31/12/9999. Not that this may impact too much but its always usefull to know.
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org

  12. #12
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by Paleo
    Another limitation is on dates calculus. Excel on Windows has a range between 1/1/1900 and 31/12/9999 while at Mac its from 1/1/1904 through 31/12/9999. Not that this may impact too much but its always usefull to know.
    the date difference is not a vba issue, rather it is a difference in the way apple and msft handle dates.

    likewise, there are differences in the coded representation of certain characters, e.g., as i recall, breaking space.

    also, the separator used in paths.

    one can conditionalize vba code to asccount for some differences, such as those i listed supra. for some others, i guess one is outta luck.

  13. #13
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    Oh ok, thanks.
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org

  14. #14
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Quote Originally Posted by Howard Kaikow
    "Save As PDF" is not part of Windows VBA, it is installed by Acrobat, or maybe some other 3rd party PDF writer.
    Actually, "Save as PDF" is part of Mac OS X, available in all OS X applications. In order to access that capability would require AppleScript, which can also work with Excel.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  15. #15
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Another one - Chart.Export seems to be broken as well. It's possible that it's actually a permissions problem, but since I'm asking it to export to the Documents folder, it really shouldn't be.

    Of course, it is not much of an issue for me right now, as I was using Chart.Export to create a .gif file, and put it back into a UserForm with .LoadPicture().

  16. #16
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    The problem with Application.GetOpenFileName() for multiple file selections can be worked around.

    The key piece of code is:
    [VBA] FileList = MacScript("return choose file with multiple selections allowed")
    [/VBA]
    which is a call to AppleScript. Maybe I'll write a quick KB article once I have the details worked out.

    Interestingly, if you open the Excel Dictionary in Script Editor, GetOpenFileName() can be accessed from AppleScript. The dictionary shows no way of selecting multiple files.

    BTW, there are three reasons for going after this workaround. i) The workaround above is too cludgy. ii) Application.FileFind takes too long to do its job. iii) Application.FileFind appears to fail in the trial copy of Excel 2004 that I have.

  17. #17
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Dim MyArray() as Variant

    and then using
    [vba]MyArray = Sheets("xyz").Cells(r,c).Resize(dr,dc)[/vba]

    will cause a 'Can't assign to Array' error.

    The fix? Just remove the parantheses from the Dim statement. In Windows, VBA works with either form.

  18. #18
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Userforms. If two controls set each other's values, you can get into problems. For example:
    [vba]Private Sub Control1_Click
    Control2.Value = x
    ...

    end sub

    Private sub Control2_Click
    Control1.Value = y
    ...

    end sub[/vba]

    The problem is that, in setting a control's value, you automatically call that control's _Click code. Windows VBA is pretty smart about this, but Mac VBA seems to get stuck in loops, usually resulting in Excel unexpectedly quitting. So be very careful how you set up this kind of code.

    Call SubInModuleX. Unlike Win VBA, Mac VBA does not always seem to like calling a public Sub in another module unless you use
    [vba]Call ModuleX.SubInModuleX[/vba]
    Seems very random. Several possible error codes including 'Sub Not Defined' and 'Illegal Use of Property'.

    Hey, but the good news is that I now have probably about 70% of my Win VBA code running in Office X now. So I'm getting closer.....

  19. #19
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Line Input and Input into arrays

    Boy, does Excel X hate this one (which works fine on Excel 2000):
    [vba]Dim TempData as variant, n as integer

    ReDim Tempdata(1 to whatever)

    open newfile for input as #1

    Line input #1, TempData(1)

    for n = 2 to whatever
    input #1, TempData(n)
    next n[/vba]

    The Line Input and Input statements give a 'Compile Error: Variable required - can't assign to this expression' or a 'Can't assign to array' error, or just cause Excel to quit.

    The solution? Input or Line Input into a non-array variable, and then assign the array element to the first variable.

  20. #20
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Quote Originally Posted by BlueCactus
    Hey, but the good news is that I now have probably about 70% of my Win VBA code running in Office X now. So I'm getting closer.....
    That's great. I wonder whether Xl2004 would be better than Excel v.X, fixing some of the limitations?

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

Posting Permissions

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