Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 29 of 29

Thread: Limitations on Excel for Mac VBA

  1. #21
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Chart.CopyAsPicture has some weird flaky text issues. The method always works, but text in the picture can be screwed up in the following ways:
    1. Last word of chart title dropped.
    2. Axis title text corrupted.
    3. Last word of axis title dropped.
    4. Last character of data labels dropped.

    It might be an issue that can be worked around, but it seems very random in whether it occurs, and how the text is malformed. BTW, this is not a simple updating issue as with the Chart.Export method in Win VBA (where a non-updated chart gets exported if you include the .Export method and updates to the chart properties within a single 'With Sheet(x).ChartObjects(y).Chart .... End With block' .)

    VBE. If the debugger kicks in with two or more Userforms active, chances are that Excel X will quit when you stop the debugger. Always fun.

  2. #22
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Quote Originally Posted by BlueCactus
    Chart.CopyAsPicture
    I think this might really be an OS bug (I'm on 10.3.8). If you copy a chart in Excel (with data labels), and paste back into Excel, there's a good chance the text will be corrupt. If you paste into GraphicConverter, same thing. But if you paste into Canvas or Preview, the labels come out fine.

  3. #23
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    A little correction to the LoadPicture() issue:

    In my earlier post, I mentioned that not only will LoadPicture() not work in Excel X, but that any instance of LoadPicture() in cross-platform code need to be enclosed in conditional compilation to avoid compiler errors. E.g.,
    [vba]#If Not Mac Then
    Userform.Picture = LoadPicture(filename)
    #End If[/vba]
    Actually, it's slightly more complex. Although I could have sworn that this avoided the compiler errors before, it now doesn't work for me. I now recommend moving LoadPicture() to a different subroutine. That is:
    [vba]...
    #If Not Mac Then
    Call PictureLoad
    #End If
    ...
    End Sub

    Sub PictureLoad()
    Userform.Picture = LoadPicture(filename)
    End Sub[/vba]

    For some reason, the top example will still cause compiler error under Excel X, even though the compiler should never process the conditional code.

    As I mentioned before, LoadPicture() is supposedly fixed under Office 2004 SP1, but I do not have the means to verify this.

    I use LoadPicture() to copy charts into dialog boxes. Right now, my code does that for Win VBA, but repositions the userform next to an on-sheet copy of the chart under Mac VBA. (I actually include both options for Win VBA since the code is already there.) This is not a trivial matter. When I get some time, I'll be posting a cross-platform version of this code in the KB, which could be easily modified to load pictures off disk.

  4. #24
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by BlueCactus
    A little correction to the LoadPicture() issue:


    Actually, it's slightly more complex. Although I could have sworn that this avoided the compiler errors before, it now doesn't work for me. I now recommend moving LoadPicture() to a different subroutine. That is:

    For some reason, the top example will still cause compiler error under Excel X, even though the compiler should never process the conditional code.
    Must be in a different module, not just in a different sub.

    The compiler checks all the code to assure syntactic correctness.
    Funny things can happen if code is inadvertently included in a compiler #IF.

  5. #25
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Quote Originally Posted by Howard Kaikow
    Must be in a different module, not just in a different sub.

    The compiler checks all the code to assure syntactic correctness.
    Funny things can happen if code is inadvertently included in a compiler #IF.
    This is a strange thing that I'm struggling to get a grip with. Right now I have one particular Excel file where the code:
    [vba]Sub Test()
    #If Mac Then
    MsgBox "Mac"
    #Else
    MsgBox "Win"
    #End If
    End Sub[/vba]
    gives "Win" on my Mac. My other files correctly respond "Mac"

    As far as Chart.Export and LoadPicture() in userform code go, I can make them fly under Mac VBA's radar by
    1. placing them in a separate Sub within the userform.
    2. calling that Sub using
    [vba]if Instr(Application.OperatingSystem, "Win") then call WinCode[/vba]

    But I agree that it is probably good practice to place them in a separate module. Just haven't tested that approach among all the other havoc I'm experiencing.

  6. #26
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by BlueCactus
    This is a strange thing that I'm struggling to get a grip with. Right now I have one particular Excel file where the code:
    [vba]Sub Test()
    #If Mac Then
    MsgBox "Mac"
    #Else
    MsgBox "Win"
    #End If
    End Sub[/vba]
    gives "Win" on my Mac. My other files correctly respond "Mac"
    go into the Object Broswer on the Mac and see if the Mac constant is defined for that verion of Office.

    My recollection is that the Mac constant was not in all versions of Office on windoze. Do not know about the Mac, I prefer to eat Oranges.

    As far as Chart.Export and LoadPicture() in userform code go, I can make them fly under Mac VBA's radar by
    1. placing them in a separate Sub within the userform.
    2. calling that Sub using
    [vba]if Instr(Application.OperatingSystem, "Win") then call WinCode[/vba]

    But I agree that it is probably good practice to place them in a separate module. Just haven't tested that approach among all the other havoc I'm experiencing.
    Using a separate module is the proper way to do this. My recollection is that I encountered cases where using a separate sub was insufficient protection.

  7. #27
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Another one. Some Userform events seem to get handled a little differently. For example, I have a rather complex form that acts as a mini-database to store information about various data files.

    One of the ComboBox_Change() routines includes the lines:
    [vba]BoxPrefix.Text = ""
    BoxSuffix.Text = ""[/vba]
    where BoxPrefix and BoxSuffix are textboxes.

    I don't really understand the difference, but Excel 2000 VBA does not trigger BoxPrefix_Change() and BoxSuffix_Change() by executing this code. Mac (Excel X) VBA does. (I should add that both BoxPrefix and BoxSuffix are typically empty when ComboBox_Change() is called.)

  8. #28
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by BlueCactus
    Another one. Some Userform events seem to get handled a little differently. For example, I have a rather complex form that acts as a mini-database to store information about various data files.

    One of the ComboBox_Change() routines includes the lines:
    [vba]BoxPrefix.Text = ""
    BoxSuffix.Text = ""[/vba]
    where BoxPrefix and BoxSuffix are textboxes.

    I don't really understand the difference, but Excel 2000 VBA does not trigger BoxPrefix_Change() and BoxSuffix_Change() by executing this code. Mac (Excel X) VBA does. (I should add that both BoxPrefix and BoxSuffix are typically empty when ComboBox_Change() is called.)
    most differences are likely due to Windows Office 2000, and later, using VBA 6.*, and likely a different Forms engine.

    If you compare with Office 97, the differences are likely less.

  9. #29
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Quote Originally Posted by Howard Kaikow
    most differences are likely due to Windows Office 2000, and later, using VBA 6.*, and likely a different Forms engine.

    If you compare with Office 97, the differences are likely less.
    Yeah, that would probably explain it. It also explains why I've had a lot of updating issues in my cross-platform userforms.

Posting Permissions

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