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.

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.

Quote:

---

...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!!! :)

Quote:

---

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")

:)

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.

Being incompatible with a MAC I wouldn't have a clue. Does a MAC have something equivalent to the windows registry?

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.

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]

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.

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?

Is there anything new in 2004 that's worth having?

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.

Quote:

---

Originally Posted by firefytr

Hey shades,

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

Or does it fail the same?

---

It also fails.

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.

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

Jack

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

Moved to the appropriate (new) forum. Hi Brad!!

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")

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.

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.

Quote:

---

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.

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

I never use Range.copy without specifying the range's parent (usually the worksheet), and it sometimes helps.

Quote:

---

Originally Posted by shades

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"

---

I'm also using Excel 2004 and it seems to work OK for me. Strange!

Actually, that was corrected. Thus, no longer an issue. :)