List of Mac Excel 2011 differences from Excel 2007

1) DIR() returns error rather than null string, so trap it
2) rg.Find Searchformat named argument not found Same for ReplaceFormat

3) rg.Autofilter Help says
“If you omit all the arguments, this method simply toggles the display of the AutoFilter drop-down arrows in the specified range.” Actually have to use worksheet.autofiltermode=false

4) References MISSING on Mac
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ADO Ext 2.8 for DDL and Security

I changed to late binding.

5) missing object iRibbonControl

6) Application.FileDialog(msoFileDialogFolderPicker)  not available
FileDialog does not exist, see Ron’s site for applescript equivalent.

7) shp.TextFrame2.TextRange.Characters.Text = “..”  -> argument not optional
need to specify Characters(1,100) or whatever

8 ) Local:=true named argument not found
Help web site doc on .Open is on RecentFile object, not Workbooks.

9) in QueryTables.Add
.PreserveFormatting not found
.RefreshPeriond no

10) GetSetting raises error 5 if key is not found, rather than returning nothing

11) UserForms cannot be modeless

12) GetSaveAsFilename on a Mac the filefilter is not working

13) CreatePivotTable does not allow xlPivotTableVersion10

14) Saveas FileFormat constants are 1 higher than Excel 2007. Best to use the xl* constants
15) I can’t see any UI way to sort a pivot table column by values.
Range.sort always raises an error when sorting pivottable data but sorts anyway.
RTE 1004 The specified dimension is not valid for the current chart type
or Application-defined or Object-defined error

Recorded Sort macro gets
ExecuteExcel4Macro “(1,””R14C3″”,1,1,1)”
which will not run – it needs SORT before the first bracket.
That sorts a pivottable without throwing an error.

16) Powerpoint does not support shapes.pastespecial but it’s in the UI

If I try .Paste I get msg in Excel “…xlsx is already open reopening will cause any changes you made to be discarded” even though all I did in Excel was a copypicture. In fact the whole content is in the clipboard.

That’s enough for now!

About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Mac, Risk. Bookmark the permalink.

1 Response to List of Mac Excel 2011 differences from Excel 2007

  1. Pingback: compatability issues between excel for mac and excel for pc

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s