Printable View
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.
From another post, Brad wrote:
Quote:
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.
Brad also wrote:
Quote:
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
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.
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. :(
:dunno
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! :roll:
"Save As PDF" is not part of Windows VBA, it is installed by Acrobat, or maybe some other 3rd party PDF writer.Quote:
Originally Posted by shades
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.
Yeah, to create a PDF just try to print to the acrobat printer and it should work.
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.
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.Quote:
Originally Posted by Paleo
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.
Oh ok, thanks.
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.Quote:
Originally Posted by Howard Kaikow
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().
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.
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.
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.....
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.
That's great. I wonder whether Xl2004 would be better than Excel v.X, fixing some of the limitations?Quote:
Originally Posted by BlueCactus
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.
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.Quote:
Originally Posted by BlueCactus
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.
Must be in a different module, not just in a different sub.Quote:
Originally Posted by BlueCactus
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:Quote:
Originally Posted by Howard Kaikow
[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" :motz2:
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.
go into the Object Broswer on the Mac and see if the Mac constant is defined for that verion of Office.Quote:
Originally Posted by BlueCactus
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.
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.Quote:
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.
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.Quote:
Originally Posted by BlueCactus
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.Quote:
Originally Posted by Howard Kaikow