Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

VBA Code problems list (Mac)

In this section it will add problems that not need a seperate part on my Mac page.
And I not forget them when I add it to my website <g>

 

1: UnMerge not working

For example, this is not working
ActiveSheet.UsedRange.UnMerge

You can replace it with
ActiveSheet.UsedRange.MergeCells = False

 

2: Merge gives error if the range is one cell

In Windows if you use code to Merge a variable range it is no problem if the range is one cell.
On a Mac it will give you a 1004 error if the range is one cell.

This is not working on a Mac

Dim MaxColumns As Long
MaxColumns = 1 ' can be any number or cell value

Range(Cells(1, 1), Cells(1, MaxColumns)).Merge


But we can use other code that is working in Windows and on a Mac to avoid this problem

Range(Cells(1, 1), Cells(1, MaxColumns)).MergeCells = True

 

3: Read text value of cell in Add-in is not working

This will not work if the workbook is a Add-in on a Mac
ThisWorkbook.Sheets("CalendarSheet").Range("B9").Text

No problem if you use .Value

 

4. UserForm and controls problems on Mac

Rowsource does not exist on a Mac to fill the listbox, you must use List to add your data on a Mac.
ColumnHeads = True is also not working on a Mac

MatchEntry is also not working properly on the Mac
I always like Option 0 : fmMatchEntryFirstLetter, but no luck on a Mac.

And many, many more things are not working OK on a Mac.
To much to list them al here

 

5. ActiveWorkbook.DisplayDrawingObjects = xlDisplayShapes

This line to hide DrawingObjects is working ok in Windows and on the Mac
ActiveWorkbook.DisplayDrawingObjects = xlHide

But this line will blow on a Mac to display all drawingobjects
ActiveWorkbook.DisplayDrawingObjects = xlDisplayShapes

You must replace it with the line below, working correct in Windows and on a Mac.
ActiveWorkbook.DisplayDrawingObjects = xlAll

 

6: Application.DisplayFormulaBar = False

In Windows you can use this to Show/Hide and Toggle the Formula bar

Application.DisplayFormulaBar = True
Application.DisplayFormulaBar = False
Application.DisplayFormulaBar = Not Application.DisplayFormulaBar


But on a Mac this is not working,there used to be just one formula bar on the screen.
Now you can have lots of them if you have more workbooks open, maybe that is the reason.

The Work-Around is to execute the Formula Bar button on the View menu like this to toggle the view. Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=849, Recursive:=True).Execute

 

7: Application.Dialogs(xlDialogSaveAs).Show

In Windows if you call Application.Dialogs(xlDialogSaveAs).Show
it will wait for the user to save or cancel before it run the other code lines in the macro.

That's good

But on a Mac it will not wait and run the whole macro when you run the code from Excel
with a button on the sheet or in a menu or from the Macro dialog(Alt F8).
This is no problem if this is your only code line in the macro or if there is no problem if it finish the macro.

If you have problems with it you can use the GetSaveAsFileName example on this page as a workeround.

Note: this is fixed in a update(14.3.2) of Office 2011

 

8: Application.Evaluate

In Excel 2011 Application.Evaluate is broken
For example the test macro below will show you 1 in Excel 2004 and all Windows Excel versions.

Sub TestAppEvaluate()
    ThisWorkbook.Names.Add "Version", "=1"
    MsgBox Application.Evaluate("'" & ThisWorkbook.Name & "'!Version")
End Sub

But in Excel 2011 this will not work, you can use this as a workeround :

Sub TestAppEvaluate2()
    Dim VersionConstant As String
    ThisWorkbook.Names.Add "Version", "=2"
    VersionConstant = ThisWorkbook.Names("Version").RefersTo
    MsgBox Mid(VersionConstant, 2, Len(VersionConstant) - 1)
End Sub

 

9: Folder names

When the folder path has an accented glyph VBA code will give you wrong results on a Mac. When you have a folder named Activités with your file in it and want to read the path of your file with ActiveWorkbook.Path or ThisWorkbook.Path you get this wrong result for the folder name : Activite_s

 

10: CutCopyMode

This is not working on a Mac to clear the clipboard : Application.CutCopyMode = False

As a workeround you can use the code from this article : http://www.mcgimpsey.com/macoffice/excel/clearclipboard.html

 

11: Context and PopUp menus

You can't use FaceId's on a Mac and it will add item called "Help" on top of the new menu that you create with VBA code, no way to remove this as far as I know.