Download Mac Date Picker add-in version 3.2 for Excel 2016 and higher released on 16-Nov-2020. Many thanks to John McGhie, Mourad Louha, Sergio Alejandro Campos and Bernard Rey for the translations of the button captions and msgboxes in the add-in. Note: Version 3.2 will postion the userform in the center of the Excel window, also with more. Even with the absence of userforms, this new VB Editor is a huge leap forward from the editor that was originally shipped with Excel 2016 for Mac. If you are a Mac user, you will now be able to write macros to help you automate Excel and save time with your job.
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.
Detect if the Shift, Ctrl, Alt or CMD key has been pressed
In Excel for Windows you can use the GetKeyState API to check this but on a Mac it is not so easy. below you find examples for Office for Windows and for Office for the Mac. Code like this can be very useful to run the code you want depending of the key status.
Example for Office for Windows
Excel for Windows example below for the Shift key, on top of a normal module add this :
In your macro you can use this then :
Example 1 for Office for the Mac 2011 and 2016
Thanks to DJ Bazzie Wazzie from the script forum i was able to create this VBA example. Note: it is not very fast but if you use Mavericks (10.9 or higher) and Office 2011 you can use the second example on this page if the speed is important.
You can run the macro named TestKey below to test it, do not forget to copy the function KeyPressedCheck inside the same module because it is called by the macro. In the function call in the macro you see that I use argument 1 for testing the Shift key now, see the comments in the macro for using argument 2, 3 or 4.
Note: When you call the function from a userform button or button on a worksheet the Ctrl key test is not working, when you run the Ctrl test from the macro dialog it is working. Reason is that Ctrl + click is right click on a Mac so the Ctrl test is only working from the macro dialog.
Example 2 for Office for the Mac 2011
Note : This example is not working in Excel 2016, example 1 is also working in Excel 2016
Thanks to Shane Stanley from the script forum I was able to create this VBA example. Note: you must run Mavericks (10.9 or up) on your Mac if you want to use this example. This example is much faster then the first example on this page but you need the extra script bundle file (checkModifier.scptd).
Excel Userform Code
When you want to test the code in the Excel file the script bundle file (checkModifier.scptd) must be in the same folder of the workbook or add-in because the VBA code call this script file to get the info we need. You can change the vba code if you want to place the script bundle file (checkModifier.scptd) into another folder on your Mac.
Excel Vba Userform Close
This is the VBA code that you see in the Excel workbook and it will work if the the script bundle file (checkModifier.scptd) is in the same folder. Important: do not change anything in the script bundle file.
Note: When you call the function from a userform button or button on a worksheet the Ctrl key test is not working, when you run the Ctrl test from the macro dialog it is working. Reason is that Ctrl + click is right click on a Mac so the Ctrl test is only working from the macro dialog.
If you have suggestions or have problems please let me know.