Consulting

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

Thread: Mac, Excel, and VBA

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

    Mac, Excel, and VBA

    As many of you know I am a Win2k user during the day and a Mac user at home. I just received Office 2004 last Thursday and have been looking at some of the improvements (I didn't have Office X, rather Office 2001).

    Good news on the Excel VBA side. Tonight on Excel 2004 I tried about 10 of my macros that I use daily at work. All of them worked exactly the same, except one which copies an Excel chart/range from Excel to PowerPoint (I picked up the code on one of the Excel sites). But the only change I had to make was in the reference to calling PowerPoint. So the line changed from:

    Set PPApp = GetObject(, "Powerpoint.Application.10") 'Excel 2002 format

    to:

    Set PPApp = GetObject(, "Powerpoint.Application") 'Excel 2004 format

    I thought it would be with the number changed, but it actually is the dropping of the number totally. A very minor alteration, so I am happy.

    ----------------

    Needless to say, I think this is encouraging for cross-platform development. If anyone is interested in those VBA codes that required no change, I can post the code as I discover more.

    Or

    if someone is interested in testing code on Excel 2004 I would be interested if it isn't too tough for me.

    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
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Shades,

    I'm defenitely interested in cross-platform code design. Although I do not have a Macintosh, I firmly believe this is something we here at VBAX should be cognizant of, and be able to adapt to/from.

    I think this should be along the same lines of error checking/trapping, coding for different versions of Excel and of course best practices.

    ...if someone is interested in testing code on Excel 2004 I would be interested if it isn't too tough for me.
    I may take you up on that too. Hope you didn't just bite off more than you can chew
    Nevertheless, thanks for the offer!

    Take care shades!!!

  3. #3
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Set PPApp = GetObject(, "Powerpoint.Application.10") 'Excel 2002 format

    to:

    Set PPApp = GetObject(, "Powerpoint.Application") 'Excel 2004 format

    I thought it would be with the number changed, but it actually is the dropping of the number totally. A very minor alteration, so I am happy.
    Set PPApp = GetObject(, "Powerpoint.Application") will return the latest version installed on the system.

    Set PPApp = GetObject(, "Powerpoint.Application.10") will return the particular version you specify. For 2004 I guess it would be:

    Set PPApp = GetObject(, "Powerpoint.Application.12")

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  4. #4
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Quote Originally Posted by mark007
    Set PPApp = GetObject(, "Powerpoint.Application") will return the latest version installed on the system.

    Set PPApp = GetObject(, "Powerpoint.Application.10") will return the particular version you specify. For 2004 I guess it would be:

    Set PPApp = GetObject(, "Powerpoint.Application.12")

    That's what I thought, but that didn't work; it only worked with the non-specified number in Mac. So, the question would be how does a coder handle that for both platforms? I'm out of my league here, but it is intriguing.

    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
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Being incompatible with a MAC I wouldn't have a clue. Does a MAC have something equivalent to the windows registry?
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  6. #6
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Since I just received the computer and am brand new to OS X (which has UNIX [FreeBSD] as its base), I am not sure about the structure. I will investigate in the next few days to find out.

    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 Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Here is a code snippet from a Microsoft KB article you can use to determine whether or not your code is running on a Mac.

    It's a start at developing macros or other code that will run in any environment!

    [vba]
    **********************************************************************
    '
    ' FUNCTION:
    ' RunningOnMac() As Boolean
    '
    ' PURPOSE:
    ' Detects if you are running on a Macintosh computer.
    '
    ' ARGUMENTS:
    ' None
    '
    ' RETURNS:
    ' True - You are running on a Macintosh computer.
    ' False - You are not running on a Macintosh computer.
    '
    ' **********************************************************************
    Function RunningOnMac() As Boolean

    ' Get the Operating System string.
    Dim strOs As String
    strOs = Application.OperatingSystem

    ' Check if the string Mac is part of the Os string.
    If InStr(1, strOs, "Mac", vbBinaryCompare) > 0 Then
    RunningOnMac = True
    Else
    RunningOnMac = False
    End If

    End Function

    [/vba]

    Use it like this:

    [vba]

    Sub main()

    ' Test if you are on a Macintosh computer.
    If RunningOnMac Then

    ' Enter Macintosh-specific code here.
    MsgBox "Running on a Macintosh"

    Else

    ' Enter Windows-specific code here.
    MsgBox "Running on Windows"
    End If

    End Sub


    [/vba]
    "All that's necessary for evil to triumph is for good men to do nothing."

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

    Serious bug in Excel VBA (Mac)

    I just discovered a serious bug in VBA on Excel 2004. When doing a simple command like:

    Range("A1").Copy

    an error message appears: "Copy method of range class failed"

    Same happened with the paste command.

    I tried recording a macro doing the copy/paste, and it recorded as it should with appropriate command lines. However, when stepping through, it errored on the above line (actually in the recorded example it was:

    Range("A1").Select
    Selection.Copy
    )

    But the effect was the same.

    I submitted an error report to MS. Let's see how quickly this gets resolved.

    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

  9. #9
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hey shades,

    Good to know. Nice job on the catch. Just wrought out of curiosity, how about

    [vba][A1].Copy[/vba]

    Or does it fail the same?

  10. #10
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Is there anything new in 2004 that's worth having?
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  11. #11
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    As far as Excel 2004, it seems like it received the least improvements. Entourage and Word are worth the upgrade. I haven't tried PowerPoint enough to know.

    As far as the error problem, after further testing, it seems that it occurs when trying to run from the VBE window, but not always within Excel itself. That is even more strange. I will try more tonight.

    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

  12. #12
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Quote Originally Posted by firefytr
    Hey shades,

    [vba][A1].Copy[/vba]

    Or does it fail the same?
    It also fails.

    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

  13. #13
    VBAX Regular
    Joined
    May 2004
    Location
    Springfield, MO
    Posts
    39
    I was a little disappointed to see that Excel 2004 still doesn't display the value of a variable when your mouse hovers over the variable name. This has been a handy feature for debugging on the Windows side since Excel 97.

  14. #14
    For debugging try using the watch windows, not sure if mac carries that feature.

    Jack

  15. #15
    VBAX Regular
    Joined
    May 2004
    Location
    Springfield, MO
    Posts
    39
    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.

    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

  16. #16
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Moved to the appropriate (new) forum. Hi Brad!!
    ~Anne Troy

  17. #17
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by shades
    As many of you know I am a Win2k user during the day and a Mac user at home. I just received Office 2004 last Thursday and have been looking at some of the improvements (I didn't have Office X, rather Office 2001).

    Good news on the Excel VBA side. Tonight on Excel 2004 I tried about 10 of my macros that I use daily at work. All of them worked exactly the same, except one which copies an Excel chart/range from Excel to PowerPoint (I picked up the code on one of the Excel sites). But the only change I had to make was in the reference to calling PowerPoint. So the line changed from:

    Set PPApp = GetObject(, "Powerpoint.Application.10") 'Excel 2002 format

    to:

    Set PPApp = GetObject(, "Powerpoint.Application") 'Excel 2004 format

    I thought it would be with the number changed, but it actually is the dropping of the number totally. A very minor alteration, so I am happy.

    ----------------

    Needless to say, I think this is encouraging for cross-platform development. If anyone is interested in those VBA codes that required no change, I can post the code as I discover more.

    Or

    if someone is interested in testing code on Excel 2004 I would be interested if it isn't too tough for me.
    I don't have a Mac, but you should be able to use the following:

    Set PPApp = GetObject(, "Powerpoint.Application")

  18. #18
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by shades
    That's what I thought, but that didn't work; it only worked with the non-specified number in Mac. So, the question would be how does a coder handle that for both platforms? I'm out of my league here, but it is intriguing.
    There's not really any reason to use the version number.

    If you happen to have more than one version of Excel installed on a windoze platform (this NOT an officially supported option), then you could use such a mechanism to create alternate objects, but why do so?

    I doubt that the installation of more than one version of Office concurrently is officially supported, by MSFT, on a Mac. At best, it is asking for trouble.

  19. #19
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Quote Originally Posted by Howard Kaikow
    If you happen to have more than one version of Excel installed on a windoze platform (this NOT an officially supported option), then you could use such a mechanism to create alternate objects, but why do so?
    While our corporation has settled on Office XP, some of our vendors have not. Thus, exchanging files with them requires that they work in several environments with different versions of XL. BTW, at work I do not have multiple installations, only XL 2002.

    I doubt that the installation of more than one version of Office concurrently is officially supported, by MSFT, on a Mac. At best, it is asking for trouble.
    Because I have been helping troubleshoot applications set up to run on both Windows and Macs, I use both XL 2001 and XL 2004 because of the clients' sprectrum of installations. However, having said that, keep in mind that XL 2001 runs only in Classic (which is an emulation of Mac OS 9 within OS X), and XL 2004 runs only in OS X (and can not run in CLassic). There are two completely different operating environments, using different system folders and different resources.

    Now if it were XL v. X and XL 2004 both running in OS X, then I would agree that it is asking for trouble.

    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

  20. #20
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by shades
    While our corporation has settled on Office XP, some of our vendors have not. Thus, exchanging files with them requires that they work in several environments with different versions of XL. BTW, at work I do not have multiple installations, only XL 2002.

    Because I have been helping troubleshoot applications set up to run on both Windows and Macs, I use both XL 2001 and XL 2004 because of the clients' sprectrum of installations. However, having said that, keep in mind that XL 2001 runs only in Classic (which is an emulation of Mac OS 9 within OS X), and XL 2004 runs only in OS X (and can not run in CLassic). There are two completely different operating environments, using different system folders and different resources.

    Now if it were XL v. X and XL 2004 both running in OS X, then I would agree that it is asking for trouble.
    Lemme clarify further.

    You still do not need the version number. You just use:
    Set PPApp = GetObject(, "Powerpoint.Application")

    And then, if there is code that is version dependent, you conditionally execute the code depending on the running version and OS. For example:

    Select Case lng(PPApp.Version)
    case 10
    'put 2002 code here
    case 11
    'put 2003 code here
    case else
    'Stop execution and figure out what tou gotta do
    End Select

Posting Permissions

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