Peltier Tech

Peltier Technical Services - Excel Charts and Programming

Office 2016 For Mac Is Here (well…)

by 22 Comments

Monday, July 13, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

Note: This article was originally published on 13 July. I have made modifications on 17 July.

The big announcement from Microsoft last Thursday was Office 2016 for Mac is here! Ed Bott has a glowing review at New Office 2016 for Mac makes life easier for the cross-platform crowd.

We’ve known for months that Microsoft has been working on Office 2016 for both Windows and Mac. I’ve been playing a bit with the Office 2016 for Windows preview for a while, but I haven’t done much with the Mac preview.

Highlights

Mac Office 2016 User Interface

The Office 2016 for Mac user interface is very much like that for Office for Windows. It no longer feels like a toy program built by an 8th grader in shop class. This is a very good sign. Now Office looks and feels the same across all platforms: Windows, Mac, Android, iOS.

New Charts (Windows only, so far)

Earlier, Microsoft Introduc[ed] new and modern chart types now available in Office 2016 Preview. The latest Office 2016 preview features a few new chart types, including some that were previously only available to users of add-ins like the Peltier Tech Charts for Excel. Microsoft has added waterfalls, histograms, paretos, treemaps, and sunbursts.

Lowlights

Office 365

The Windows Office 2016 preview has only been available through Office 365 (the kludgy online subscription service). I never can remember which of my umpteen Microsoft logins is valid for Office 365, and even when I look it up, the correct user ID and password don’t work the first time. Updating Windows Office 2016 preview versions has never gone without several major hitches for me, followed by complete uninstallation then reinstallation from scratch.

So far, Mac Office 2016 is only available via Office 365, which Microsoft treats as a good thing. In Windows, installing from Office 365 wipes out any older versions of Office, so you can’t run Office 2007, 2010, and 2013 side-by-side to support different clients. On the Mac, I was glad to see that I could install Office 2016 without wiping out the previously installed Office 2011. I did have to rebuild the LaunchPad links to Office 2011 and put the icons back onto the Dock, but that only wasted an hour.

Mac Office 2016 VBA

This is the painful one. Mac Office 2016 does still support VBA, of course, and Microsoft has rebuilt the VBA Editor from scratch, which was drastically. Unfortunately the VBA Editor is not really ready to use: you can’t insert new modules or UserForms, and while you can edit existing modules, you can’t even view the UserForms.

Microsoft is making small improvements to the VB editor, and these improvements will be part of the monthly updates. but they are not expecting to make the editor as functional as that in Windows. They encourage developers to build their add-ins in Windows versions of Excel or in Excel 2011, then test in Mac Excel 2016.

Mac Office 2016 Custom User Interface Elements

First, a little background…

In Office 2003 and earlier, developers and users could construct custom menus and toolbars to access built-in and custom functionality.

Starting in Office 2007, Office for Windows has had the ribbon. It could readily be modified for developers willing to delve into RibbonX, a variation of XML used to control and customize the ribbon. Since Office 2010, the ribbon could also be easily modified through the user interface. While custom menus and toolbars were no longer supported in Office 2007, any custom menu items were put into a special Add-Ins ribbon tab. Windows Office add-in developers, myself included, have migrated to custom ribbon interfaces, to great effect.

Mac Office 2011 introduced a low-functioning ribbon, which did not allow any customization programmatically or through the Office interface. However, the custom menus and toolbars that were discontinued in Windows Office are still supported in Mac Office 2011.

Mac Office 2016 has a better ribbon than 2011, but it is still not customizable by the user, nor can it be it controlled using RibbonX. Custom menus and toolbars are no longer supported in Mac Office 2016, but there is an Add-Ins ribbon tab to handle legacy menu items.

Microsoft is working on implementing RibbonX interface controls for Mac Excel, and it will be rolled out as part of a regular update. Until then a Mac Office add-in developer should still build legacy menu-type user interfaces.

Sandbox Mode

One more drawback is that Mac Office 2016 now runs in a sandboxed mode, so certain features are more difficult for developers to implement, features such as accessing folders and opening files. I don’t know the full ramifications of this behavior, since my code isn’t yet running properly in Mac Office 2016.

Bottom Line

Therefore, if you are using any add-ins to extend the features of Mac Office, you should not upgrade until you know whether these add-ins will work properly in Mac Excel 2016.

Peltier Tech Charts

Peltier Tech Chart Utility 2.0

Peltier Tech has taken steps to prepare for Microsoft Office 2016. The Windows version of the current Peltier Tech Chart Utility was designed for Office 2007 through 2013, and it can to run in the preview of Office 2016, but it will not work in the eventual commercial release of Office 2016. As always, there are a few minor changes that need to be made to accommodate new features and syntax of the new Office version.

Peltier Tech Charts for Excel 3.0

In addition, Peltier Tech is working on a major upgrade to the utility, called Peltier Tech Charts for Excel. This edition will work in Windows Office 2016, but also 2013, 2010, and 2007. It will also run in Mac Office 2016, when that has become capable of supporting add-ins, and of course, it will run in Mac Office 2011. The exciting part is that the same add-in file will work in both Windows and Mac, so users stuck between platforms will not need to license two separate add-ins.

The new Peltier Tech Utility will feature all of the old charts, including the ones that Microsoft is introducing in Office 2016, because some Peltier Tech customers will still be using older Office versions. New chart types are being developed, including grouped box plots and sensitivity tornado charts. If you have a favorite chart type that neither Microsoft nor I have made available, mention it in the comments below, and Peltier Tech will add it to the long long list of suggestions.

The new Utility will also offer some new features, including more powerful chart data manipulation tools. Again, if there’s a feature you can’t live without, let us know in the comments.

The new Utility will likely have Standard and Advanced editions like the current Utility, and these editions will likely be priced at the same levels as the current utility. Existing users of the current Utility will be able to upgrade at a 50% discount, and users of the older family of utilities will qualify for a 25% discount. In addition, bulk and academic discounts will be available.

Another exciting development is Peltier Tech Charts for PowerPoint, and in all likelihood Peltier Tech Charts for Word. These are still now in their infancy, but it will bring easier linking of standard PowerPoint and Word charts to Excel data, as well as at least a partial menu of Peltier Tech custom charts.

Peltier Tech Charts for Excel 3.0 Beta

So far the new utility is only in the alpha testing stage, meaning new pieces are being incorporated and tested in-house only.

In about a month a beta version will become available. Current and potential new users will be able to test and comment on it.

During the beta testing period, users of the current Utility will be able to lock in their upgrade at a discount of 60%, users of the older family of utilities at 35%, and new users at a discount of 10%.

More details will become available over the coming weeks.

Posted: Monday, July 13th, 2015 under Excel 2016.
Tags: , , .
Comments: 22

Peltier Tech Charts for Excel

Microsoft MVP Logo

Reader Interactions

Comments

  1. Felipe says

    Great article, John. I read just for fun, because I think I’ll never get a Mac project to do. It is too much headache.
    Just a correction: “Now Windows looks and feels the same across all platforms: Windows, Mac, Android, iOS”
    should be “Now Office looks and feels (…)”.

  5. Dave Dudus says

    Hi Jon- how about the ability to add a discriminator to charts like scatter plots? For example of plot of engine size vs horse power with the option to color the scatter plot based on things like number of cylinders, mileage, etc. I’m in the energy business so I use plots like this to determine things like variables that impact oil/gas/water production, rock quality, etc. I basically bust the bulk data into bins based on the discrimator variable and that allows me to create individual regressions on each of the bins, then finally interpolating the regressions into a universal relationship. The actual process is pretty simple, but would work best as an add-in. Thanks for reading this comment.

  7. Jay Arthur says

    Your post speaks to “an Add-Ins ribbon tab to handle legacy menu items”. I’ve been fiddling with Excel 2016 for the Mac, but haven’t found a way to add a menu anywhere. Got any suggestions?

  9. Jon Peltier says

    Jay –

    Long story. Remember how we used to use Commandbars("Worksheet").Controls.Add(msoControlPopup) to add a menu to the Worksheet menu bar in Classic Excel? Sure you do. And remember how Excel 20907 didn’t give us the menu any more, but it put all the menu’s controls onto the Add-Ins tab? It wasn’t pretty, but it was what you’d call “compatible”.

    Well, Excel 2016 for Mac doesn’t do this. And if you ask anyone, they’ll say, yeah, we don’t support commandbars anymore. And what’s true is that Controls.Add(msoCommandBarPopup) doesn’t work anymore. So you can’t add a menu, which is essentially a popup on the Worksheet or Chart menubar. And you can’t add a cascading menu or toolbar, which is a menu or toolbar with buttons organized into popups.

    But after a couple days of extensive testing of commandbars code, in Excel 2003 and 2013 for Windows and 2011 and 2016 for Mac, is that a couple things still work, after a fashion:

    Application.CommandBars.Add("My Cmd Bar") creates a custom toolbar that would have floated in Excel 2003 and 2011, but is now displayed in the Add-Ins tab in 2016 for Mac.

    Application.CommandBars("My Cmd Bar").Controls.Add(msoControlButton) adds a button to this toolbar that resides on the Add-Ins tab.

    Since my add-in has dozens of buttons, I can’t use one toolbar to show them all, so I’ve split them into half a dozen smaller toolbars, and I start with a master toolbar that I build when the workbook opens.

    So when the workbook loads, I run this procedure, which creates and populates the Main toolbar on the ribbon. The first button shows an About dialog, each of the others will create one of the submenus when clicked. I only run this in Mac Excel 2016, but I can test it in Mac Excel 2011 or any Windows Excel version.

    Sub CreateMac2015ToolbarMain()
  Dim CmdBar As CommandBar
  Dim CmdBtn As CommandBarButton

  DeleteMac2015Toolbars

  Set CmdBar = Application.CommandBars.Add("Main1")
  With CmdBar
    .Visible = True

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "About Button"
      .OnAction = "'" & ThisWorkbook.Name & "'!AboutDialog"
      .Enabled = True
    End With

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "Data Functions"
      .OnAction = "'" & ThisWorkbook.Name & "'!CreateMac2015ToolbarData"
      .Enabled = True
    End With

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "Format Functions"
      .OnAction = "'" & ThisWorkbook.Name & "'!CreateMac2015ToolbarFormat"
      .Enabled = True
    End With

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "Export Functions"
      .OnAction = "'" & ThisWorkbook.Name & "'!CreateMac2015ToolbarExport"
      .Enabled = True
    End With

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "Misc Functions"
      .OnAction = "'" & ThisWorkbook.Name & "'!CreateMac2015ToolbarMisc"
      .Enabled = True
    End With
  End With
End Sub

    DeleteMac2015Toolbars removes all toolbars, so that the one being drawn is the only one showing.

    Sub DeleteMac2015Toolbars()
  On Error Resume Next
  Application.CommandBars("Main1").Delete
  Application.CommandBars("Data1").Delete
  Application.CommandBars("Format1").Delete
  Application.CommandBars("Export1").Delete
  Application.CommandBars("Misc1").Delete
End Sub

    When the Data Functions button on the main menu is clicked, it runs the following procedures, which clears all toolbars and adds the Data toolbar. The first button here redraws the Main toolbar, each of the others run a specific procedure in the add-in.

    Sub CreateMac2015ToolbarData()
  Dim CmdBar As CommandBar
  Dim CmdBtn As CommandBarButton

  DeleteMac2015Toolbars

  Set CmdBar = Application.CommandBars.Add("Data1")
  With CmdBar
    .Visible = True

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "Go To Main Toolbar"
      .OnAction = "'" & ThisWorkbook.Name & "'!CreateMac2015ToolbarMain"
      .Enabled = True
    End With

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "Data Function 1"
      .OnAction = "'" & ThisWorkbook.Name & "'!MacDataFunc1"
      .Enabled = True
    End With

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "Data Function 2"
      .OnAction = "'" & ThisWorkbook.Name & "'!MacDataFunc2"
      .Enabled = True
    End With

    Set CmdBtn = .Controls.Add(msoControlButton)
    With CmdBtn
      .Style = msoButtonCaption
      .Caption = "Data Function 3"
      .OnAction = "'" & ThisWorkbook.Name & "'!MacDataFunc3"
      .Enabled = True
    End With
  End With
End Sub

    When I run the first procedure, CreateMac2015ToolbarMain, in Mac Excel 2011, I get the Main floating toolbar in the middle of the Excel window.

    When I click on the Data Functions button, the Data floating toolbar replaces it.

    Clicking the Go To Main Toolbar button brings back the original Main toolbar.

    Okay, this isn’t real pretty, but our proof of concept works in 2011 (and in 2003 it would work the same way).

    When I test in Excel 2016, I get this Main toolbar in the Add-Ins tab:

    And clicking the appropriate button displays the Data toolbar in the Add-Ins tab.

    Clicking the right button brings the main toolbar back.

    Again, not pretty. Arguably ugly as all hell. But at least it works, and I can offer my charting add-in to Mac Excel 2016 users.

    I hear that Mac Excel 2016 will be ready to host customUI xml code sometime in the future, I’m hoping sometime this fall. This could not happen too soon. Even so, Mac Excel should still treat legacy commandbar code as courteously as does Excel 2007 and later, just to support users who have existing projects that use menus and toolbars.

  11. Jon Peltier says

    Felipe –

    The people doing Mac 2016 were probably not around for the fustercluck when Office 2007 came out, so they don’t remember (a) the severe disruption it caused, and (b) the extreme steps Microsoft took to minimize this disruption. I also think they are not familiar with most of the dark dusty corners of VBA, where we make toolbars and do other important but unobvious things. I’ve shared all of this with them, and I hope they at least appreciate my effort.

  12. Jay Arthur says

    I tried this code in Excel 2016 Mac and noticed some odd things.
    If I put the code in an xla or xlam and run it, the addin menu doesn’t appear, but if I put it in an xlsm, it does.
    If I run the code in an xla or xlam and than open any xlsm, the addin menu will appear.
    Very strange.

    If I put a vbCr inbetween caption words, it will word wrap:
    .Caption = “About” & vbCr & “Button”

    msoControlPopUp does not seem to work.
    msoControlDropdown doesn’t seem to allow .addItem.
    msoControlIconAndButton allows adding .faceId.

  13. Jon Peltier says

    Jay –

    It’s very strange. I wonder if they tried taking it out but only partly succeeded. It seems the Mac programmers at Microsoft don’t just build on what they had before, they completely trash it and try to rebuild. Maybe that’s the nature of programming for Mac, because Apple cares not one bit for reverse compatibility.

    If that code is in an .xla or .xlam, and you install the add-in in 2016-Mac, the add-ins tab doesn’t appear, but if you restart Excel, the add-in is still installed (as expected) and the add-ins menu now appears and sticks.

    I hadn’t specificially noticed that the add-ins menu also appears if another workbook is open, but I’ll bet it appears on a workbook-by-workbook basis: workbooks already open before opening the add-in will not show the add-ins tab, those opened after opening the add-in will show it.

    I need to revisit the .FaceId thing, because I’ve never had much luck with it on the Mac. Maybe because I’ve also used it in the same menus with .PasteFace, and Mac Excel choked on the images I used. I’ve only recently learned that only the most simple images can be used in UserForms on the Mac. If you use 24-bit BMPs, you’re fine. GIFs cause issues, copying and pasting a metafile into the Picture property of a control will cause crashing.

  14. AbbyLee says

    Totally agree with you on VBA. The new-released Excel (mac version) still cannot accept new module inserted manually, which bring big troubles for me. Will Microsoft notice this?

  15. Jon Peltier says

    Microsoft knows about this. They intentionally haven’t done this (yet). It was more important to them to get the product released now, so they’ll incorporate features as they go along.

    Microsoft’s official position is that you should do your programming in Excel 2011 for Mac or in Windows, and then do the last testing in 2016 for Mac. That’s what I’m doing, and it’s what I did for code I intended to run in Excel 2011 for Mac, because editing VBA in that version was also no picnic.

  16. Nathalie says

    Hello! You said: “I did have to rebuild the LaunchPad links to Office 2011 and put the icons back onto the Dock”

    I have installed Office 2016 but I would like to keep Word 2011 in my Launchpad – I have searched everywhere and can’t seem to find an answer; my Launchpad only displays the Office 2016 app! (I can add the Office 2011 apps to my Dock, though.) If you could please tell me how to add links to Office 2011 to the Launchpad, I would be most grateful!

  18. mv says

    “Microsoft’s official position is that you should do your programming in Excel 2011 for Mac or in Windows, and then do the last testing in 2016 for Mac. ” Microsoft’s official position show that they’re a bunch of idiots!!!!!

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Peltier Tech Charts for Excel