Mac Office 2011 VBA Bugs/Jokes
There are plenty of people who have asked the same question about "difference between Windows and Mac VBA". I think I am in a very good position to answer this question, as I really have a lot of experience in developing tools for running across the platforms.
You can get the first part of the answer from the internet, which I'm not going to repeat here. I'm going to tell you the part which no one have mentioned:
The key points which cause Windows macros to fail on the Mac are in the BUGS of Mac Office 2011 VBA.
Joke 1: Cannot DIR
In VBA, try to DIR a file which doesn't exist. e.g.:
A stupid run-time error 68: "Device Unavailable" will pop up.
This very annoying error makes all standard macros which uses DIR to fail. (This is one of the major reasons plenty of Win Excel macro fail on Mac.)
A work around (which looks very stupid), is to replace wherever DIR is used with a call to a macro like the one below:
Function dirfile(ByVal input_file As String) As Boolean
dirfile = False
On Error GoTo dirfile_err
tmp = Dir(input_file)
On Error GoTo 0
dirfile = True
Joke 2: Cannot FIND in VB Editor - Out of Memory
(Every developer in the Mac Business Unit deserves a slap for this issue. This is a Top 1 stupid bug I've ever seen since I used Office in my life.)
- Start Excel
- From the Tools menu, choose Macros, then VB Editor
- Insert a module
- Press Command+F to Find. Enter to find anything, e.g. "a". Click Find
OUT OF MEMORY!!!!
This bug is simply scary. It is therefore not possible to Find anything in your Excel VBA Project. Good luck.
Joke 3: Default font, font size, and backcolor of UserForm controls are all wrong
If you tried to create a macro project with a UserForm, you'll find that it simply looks VERY UGLY. Why? Because Microsoft has carefully defined wrong default font and font size for all the UserForm controls. Also, for those controls with a grey backcolor (e.g. the UserForm itself), the grey color is also wrong (far too dark).
This is how a UserForm in Mac Excel 2011 looks like "by default". You can see I've already tried my best
Microsoft has done so much with great care to make sure Mac VBA works properly, but the forms look VERY ugly and inconsistent with the look of Mac applications (even look different from Office itself! OMG...)
But, no panic, you can actually manually fix these yourself in your VBA projects. I'm teaching you how:
- Set the font of all controls to "Lucida Grande", 11 pt
- Set the backcolor of UserForm to 92% brightness
- Set buttons to height 27, width 72 (width may depend on caption of button)
This is how the same form looks like after you changed the defaults and move the controls neatly:
Joke 4: Cannot Unprotect a VBA Project Even you know the Password
I put a sample file for this stupid bug in my web site:
Steps to do:
- In Excel for Windows (e.g. Excel 2013), password protect a VB Project with password like "hello1234!"
- Save the Excel workbook in .xlsm format
- Open it in Mac Excel 2011.
- Go to VB Editor and try to unlock the project with "hello1234!"
Result: Invalid Password.
Work Around: Unlock the project in Win Excel, and lock the project again in Mac Excel.....
Joke 5: GetSetting may fail on Mac (Incompatibility with Windows)
In Win Excel, the following macro will run happily.
(Variable a, b, c tries to obtain values from the registry. If unsuccessful, will be assigned the "default" values of 1, False and "" (empty string).)
a = GetSetting("Test", "Test1", Key:="Option", Default:=1)
b = GetSetting("Test", "Test1", Key:="Option", Default:=False)
c = GetSetting("Test", "Test1", Key:="Option", Default:="")
However, in Mac Excel 2011, the line "c" will fail and will cause an error.
This is because, in Excel 2011, you cannot assign an empty string default value in "GetSetting". This is not logical at all. Why empty string is not allowed??!?!?
The solution is to assign a dummy value, whatever other than an empty string, in the default value.... (sigh...)
Joke 6: Win Macros may fail in Mac Excel 2011 because of invisible characters at end of statement
In Mac Excel 2011, when trying to run macros created in Windows Excel, sometimes they may just fail for no reason. No matter how to try to check the macros with your eyes or debug them, they just won't compile and report error.
In such case, go to the end of the statement(s) which VB Editor reported an error, press delete (backspace) to remove the last character (and then type to complete the complete statement again). Now, your macro may run properly.
The problem is, sometimes, Windows Excel macros may contain an invisible character at the end of the line of statement which will cause the macro to fail. (This problem only happen in Excel 2011.)