{"id":1090,"date":"2015-10-20T00:00:00","date_gmt":"2015-10-20T00:00:00","guid":{"rendered":"http:\/\/officedevblogs.wpengine.com\/?p=1090"},"modified":"2021-11-15T10:56:43","modified_gmt":"2021-11-15T18:56:43","slug":"vba-improvements-in-office-2016","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/vba-improvements-in-office-2016\/","title":{"rendered":"VBA improvements in Office 2016"},"content":{"rendered":"<div id=\"body-content\">\n<p>Recently, <a href=\"https:\/\/.office.com\/2015\/09\/28\/whats-new-in-office-2016-for-developers\/\">we shared<\/a> an overview of the new extensibility capabilities available for developers as part of the <a href=\"https:\/\/.office.com\/2015\/09\/22\/thenewoffice\/\">new Office 2016<\/a>. In addition to the improvements discussed there, including connecting the applications you build to the data and services available in Office 365 and creating add-ins that connect to third-party services, we continue to support developers working in VBA by adding new objects and members to the VBA object model across nearly all the Office applications.<\/p>\n<p>One thing to note, our engineering focus is Office web add-ins \u2013 web add-ins work across devices and platforms, and developers enjoy a modern web development experience. Support for web add-ins is already available in Office 2013 and 2016 for Windows, Office for iPad, and Outlook for Mac today, and they\u2019ll come to the rest of Office clients over time.<\/p>\n<h2>Office 2016 for Windows<\/h2>\n<h3>New VBA object model features<\/h3>\n<p>Several Office desktop applications have new features that are exposed in the VBA object model. Here are some of the highlights:<\/p>\n<ul>\n<li>Excel has a new set of data import and shaping features that are based on Power Query technology and that let users query information in files, databases, Azure sites, and from other sources, and to combine queries. To support this feature, Excel exposes the new <b>Queries<\/b> and <b>WorkbookQuery<\/b> objects, and their associated properties and methods. For more information about Power Query, see this <a href=\"https:\/\/.office.com\/2015\/09\/10\/integrating-power-query-technology-in-excel-2016\/\">blog post<\/a> and download this <a href=\"https:\/\/gallery.technet.microsoft.com\/VBA-to-automate-Power-956a52d1\">sample<\/a>. Excel exposes several new methods on the <b>WorksheetFunction<\/b> object to enable forecasting of statistical functions. Finally, there are a number of new objects related to the existing <b>Model<\/b> object, such as the <b>ModelFormatBoolean<\/b> and <b>ModelFormatCurrency<\/b> objects, and many new properties on each of these new objects.<\/li>\n<li><b>Outlook<\/b> has added a new <b>PreviewPane<\/b> object and a new <b>WordEditor<\/b> property on that object, which allow access to the body of the message currently loaded in the preview pane. This property is intended to let Windows-Eyes and other screen readers to have access to the body of a message in the preview pane.<\/li>\n<li><b>Project<\/b> adds new <b>Engagement<\/b> and <b>EngagementComment<\/b> objects and associated methods and properties to allow programmatic access to engagements scheduled in a project.<\/li>\n<li><b>Visio<\/b> has added new OM to permit information rights management (IRM) of Visio documents.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2>Office 2016 for Mac<\/h2>\n<p>Since the launch of Office 2016 for Mac in July, Mac OS X users around the world have been able to take advantage of the highly anticipated new features and improvements since the 2011 release. At the same time, some of our users also have sent us feedback requesting further improvements to our VBA support, particularly around VBA add-ins.<\/p>\n<p>In response to this feedback, we\u2019ll further improve Office 2016 for Mac&#8217;s support for VBA add-ins. Specifically, we\u2019ll be adding Office ribbon customization via Ribbon XML, and making other improvements to Word, Excel, and PowerPoint for Mac to match their Windows desktop versions.<\/p>\n<h3>Ribbon customization\u00a0via Ribbon XML<\/h3>\n<p>We are excited to announce that with the latest Office for Mac update, you\u2019ll be able to fully customize the ribbon across Word, Excel, and PowerPoint for the Mac using familiar Ribbon XML.<\/p>\n<p>Here\u2019s how the Mac Word ribbon looks with a custom tab.<\/p>\n<p><img decoding=\"async\" class=\"img-responsive\" src=\"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-content\/uploads\/sites\/73\/2018\/05\/VBAUpdates1.png\" alt=\"Ribbon customization via Ribbon XML\" width=\"490\" height=\"354\" \/><\/p>\n<p>If you\u2019ve been customizing the ribbon on Office for Windows, you\u2019ll find the Mac experience to be very similar. However, there are certain important differences to know about, as described in the table below.<\/p>\n<p>Comparison of ribbon customization functionality in Office 2016 for Mac\u00a0and Windows<\/p>\n<p><img decoding=\"async\" class=\"img-responsive\" src=\"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-content\/uploads\/sites\/73\/2018\/05\/VBAUpdates2.png\" alt=\"Comparison of ribbon customization functionality in Office 2016 for Mac and Windows\" width=\"675\" height=\"160\" \/>\n* Most familiar Office Fluent Control Identifiers are compatible with Office for Mac. Some may not be available.\n** May support this in future.\n*** Office 2016 for Mac doesn&#8217;t support third-party COM-Add-ins. No current plans to support these in the future. Ribbon customization via Ribbon XML is now available, however, it remains in preview and is disabled by default for this update. Follow the steps below to enable or disable it across Word, Excel and PowerPoint using the Terminal on Mac.<\/p>\n<p>To enable Ribbon XML:<\/p>\n<ol>\n<li>Open Terminal.<\/li>\n<li>Type this command:\n\u201cdefaults write com.microsoft.office EnableRibbonXmlDeveloperMode -bool YES\u201d<\/li>\n<li>Reboot the app.<\/li>\n<\/ol>\n<p>To disable Ribbon XML:<\/p>\n<ol>\n<li>Open Terminal.<\/li>\n<li>Type this command:\n\u201cdefaults write com.microsoft.office EnableRibbonXmlDeveloperMode -bool NO\u201d<\/li>\n<li>Reboot the app.<\/li>\n<\/ol>\n<p>In early 2016, ribbon customization via Ribbon XML will be enabled by default for all customers.<\/p>\n<h3>FAQ<\/h3>\n<h4>How do I edit Ribbon XML?<\/h4>\n<p>You can edit Ribbon XML by using familiar <a href=\"http:\/\/openxmldeveloper.org\/blog\/b\/openxmldeveloper\/archive\/2010\/08\/10\/23248.aspx\">Custom UI Editor Tool<\/a>, which remains available only on Windows.<\/p>\n<h4>Will these updates be pushed as part of the auto-update process?<\/h4>\n<p>Yes. These updates will be pushed to all customers using Microsoft Auto Updater.<\/p>\n<h4>Is the structure of the XML file the same as that for Office for Windows?<\/h4>\n<p>Yes, Office 2016 for Mac ribbon reads customizations from CustomUI.xml and CustomUI14.xml.<\/p>\n<h4>Will the Office 2016 for Mac ribbon load the XML from both files, CustomUI.xml and CustomUI14.xml?<\/h4>\n<p>Yes, and just like the behavior in Office for Windows, CustomUI14.xml will be given preference over CustomUI.xml.<\/p>\n<h4>Will the Office 2016 for Mac ribbon accept PNG files that are loaded into the document&#8217;s custom UI as control images?<\/h4>\n<p>Yes.<\/p>\n<h3>Visual Basic Editor<\/h3>\n<p>You spoke, we heard!<\/p>\n<p>As we continue to improve the overall Visual Basic story on Office 2016 for Mac, we\u2019ve added the following features to Visual Basic Editor:<\/p>\n<ul>\n<li>Ability to add modules from within the Project Viewer<img decoding=\"async\" class=\"img-responsive\" src=\"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-content\/uploads\/sites\/73\/2018\/05\/VBAUpdates3.png\" alt=\"Ability to add modules from within the Project Viewer\" width=\"478\" height=\"344\" \/><\/li>\n<li>Ability to add library references using a dialog<img decoding=\"async\" class=\"img-responsive\" src=\"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-content\/uploads\/sites\/73\/2018\/05\/VBAUpdates4.png\" alt=\"Ability to add library references using a dialog\" width=\"352\" height=\"282\" \/><\/li>\n<li>Ability to use shortcut keys for debugging within Visual Basic Editor, such as <b>Command+Shift+I<\/b> for <b>Step Into<\/b>, <b>Command+Shift+O<\/b> for <b>Step Over<\/b>.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h4>New Commands<\/h4>\n<p>You might not be aware that we recently added two new commands to the Visual Basic object model for Mac: <b>GrantAccessToMultipleFiles<\/b> and <b>AppleScriptTask<\/b>.<\/p>\n<p>We also added a new conditional, \u201cMAC_OFFICE_VERSION\u201d. (See <b>New Commands Documentation<\/b> later in this article.)<\/p>\n<p>As Office 2016 for Mac is sandboxed, users are prompted to grant access every time a file access request is made. <b>GrantAccessToMultipleFiles<\/b> is a command that takes an array of file pointers and helps minimize the number of these prompts.<\/p>\n<p>Sandboxing also severely breaks the previously existing <b>MacScript<\/b> command that allows the use of inline AppleScript in Visual Basic. This is where <b>AppleScriptTask<\/b> can help. Users can store an AppleScript file at a specified location on the disk and use <b>AppleScriptTask<\/b> within VB to invoke it. The location of these scripts is specified by the operating system and cannot be altered.<\/p>\n<p>The <b>MAC_OFFICE_VERSION<\/b> conditional lets macros determine what version of Mac Office the user is running. This comes handy in cases where certain commands (like the two above) are available only on a given version, and invoking them on another version may result in errors.<\/p>\n<p>Since Office 2016 for Mac Beta, we\u2019ve been keeping close watch on issues relating to these new commands and have been making fixes. With this update, we\u2019re releasing some important fixes that will considerably improve the overall performance of these commands. In particular, we\u2019ve fixed various timeout issues related to <b>AppleScriptTask<\/b>.<\/p>\n<h4>New Commands Documentation<\/h4>\n<p>AppleScriptTask<\/p>\n<p>The AppleScriptTask command executes an AppleScript script. This is similar to the MacScript command except that it runs an AppleScript file located outside the sandboxed app.<\/p>\n<p>For example:<\/p>\n<pre><code>  Dim myScriptResult as String  myScriptResult = AppleScriptTask (\"MyAppleScriptFile.applescript\", \"myapplescripthandler\", \"my parameter string\")   <\/code><\/pre>\n<p>Where:<\/p>\n<ul>\n<li>The \u201cMyAppleScriptFile.applescript\u201d file must be in ~\/Library\/Application Scripts\/[bundle id]\/. The extension applescript is not mandatory; .scpt may also be used.<\/li>\n<li>\u201cmyapplescripthandler\u201d is the name of a script handler in the \u201cMyAppleScriptFile.applescript\u201d file<\/li>\n<li>\u201cmy parameter string\u201d is the single input parameter to the \u201cmyapplescripthandler\u201d script handler.<\/li>\n<li>The corresponding AppleScript for Excel would be in a file named &#8220;MyAppleScriptFile.applescript&#8221; that is located in ~\/Library\/Application Scripts\/com.microsoft.Excel\/.<\/li>\n<\/ul>\n<p><b>Note<\/b> The bundle IDs for Mac Word, Excel, and PowerPoint are:<\/p>\n<ul>\n<li>com.microsoft.Word<\/li>\n<li>com.microsoft.Excel<\/li>\n<li>com.microsoft.Powerpoint<\/li>\n<\/ul>\n<p>The following is an example of a handler.<\/p>\n<pre><code>  on myapplescripthandler(paramString)    #do something with paramString   return \"You told me \" &amp; paramString       end myapplescripthandler   <\/code><\/pre>\n<p>GrantAccessToMultipleFiles<\/p>\n<p>Unlike VB macros in Office for Mac 2011, VB macros in Office 2016 for Mac don\u2019t have access to external files by default. The Office 2016 for Mac apps are sandboxed and so they lack the required permissions to access external files.<\/p>\n<p>Existing macro file commands are changed to prompt the user for file access if the app doesn\u2019t already have access to it. This means that macros that access external files cannot run unattended; they\u2019ll need user interaction to approve file access the first time each file is referenced. You can use the <b>GrantAccessToMultipleFiles<\/b> command to minimize the number of prompts and make the experience better (see below).<\/p>\n<h4>GrantAccessToMultipleFiles<\/h4>\n<p>This command lets you input an array of file paths and prompt the user for permission to access them.<\/p>\n<pre><code>  Boolean  GrantAccessToMultipleFiles(fileArray)     Parameters   <i>fileArray<\/i>  An array of POSIX file paths.    Return values  <b>True<\/b>   The user grants permission to the files.   <b>False<\/b>  The user denies permission to the files.   <\/code><\/pre>\n<p><b>Note<\/b> Once permissions are granted, they\u2019re stored with the app and the user doesn\u2019t need to grant permission to the particular file anymore.<\/p>\n<p>Example:<\/p>\n<pre><code>Sub requestFileAccess()\u202f   \u202f   'Declare Variables\u202f   \u202f \u202f Dim fileAccessGranted As Boolean\u202f   \u202f \u202f Dim filePermissionCandidates   \u202f   \u202f'Create an array with file paths for which permissions are needed\u202f   \u202f \u202f filePermissionCandidates = Array(\"\/Users\/\/Desktop\/test1.txt\", \"\/Users\/\/Desktop\/test2.txt\")   \u202f   'Request access from user\u202f   \u202f \u202f fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)   'returns true if access granted, otherwise, false   End Sub  <\/code><\/pre>\n<p>MAC_OFFICE_VERSION<\/p>\n<p>In Office 2016 for Mac, this new conditional tests which VB version the user is running. The following example shows how to use it in your code.<\/p>\n<pre><code>Sub VersionConditionals()    #If MAC_OFFICE_VERSION &gt;= 15 Then      Debug.Print \"We are running on Mac 15+\"  #Else      Debug.Print \"We are not running on Mac 15+\"  #End If  #If Mac Then      Debug.Print \"We are running on a Mac\"  #Else      Debug.Print \"We are not running on a Mac\"  #End If  End Sub  <\/code><\/pre>\n<p><b>Note<\/b> The &#8220;#If Mac&#8221; conditional remains unchanged from Office for Mac 2011.<\/p>\n<h4>Plan Ahead<\/h4>\n<p>Starting in Office 2016 for Mac, custom menus that were supported in Office for Mac 2011 are not supported anymore. We\u2019re officially deprecating support for custom menus and <em>CommandBar<\/em> controls. A few legacy <em>CommandBar<\/em> controls may continue to appear on the <em>Add-ins<\/em> tab, however, we advise that you don\u2019t write new code that uses either of the two. Starting today, if you have a VB add-in that requires a UI, it\u2019s best to use Ribbon XML to write that UI.<\/p>\n<p>Overview of UI Extensibility points and corresponding support in Office 2016 for Mac<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-content\/uploads\/sites\/73\/2018\/05\/VBAUpdates5-2.png\" alt=\"Overview of UI Extensibility points and corresponding support in Office 2016 for Mac\" width=\"557\" height=\"363\" \/><\/p>\n<p>Overall, as we\u2019ve mentioned before, we recommend that customers develop macros or VB add-ins in Office for Windows and use Office for Mac to debug, if needed. We\u2019ll continue to ensure compatibility of existing macros and make improvements to the IDE.<\/p>\n<h2>Documentation &amp; GitHub<\/h2>\n<p>Our developer help for VBA has also undergone some changes. The first thing to highlight is that we\u2019ve moved our content to an \u201cevergreen\u201d model. That means that starting with Office 2013, our help topics are no longer specific to a single version of Office, but instead are combined topics that span all the versions of Office to which they apply. You can still view topics from Office 2010 by clicking <em>Other Versions<\/em> right below the topic title.<\/p>\n<p>In addition to the evergreen model, we\u2019ve published the <a href=\"https:\/\/github.com\/OfficeDev\/VBA-content\">VBA documentation on GitHub<\/a>. By doing so, it allows a much faster process to improve our documentation and a way for you in the community to engage and contribute to the content. At the top of each VBA topic in MSDN, you\u2019ll see a contributors notice like this:<\/p>\n<p><img decoding=\"async\" class=\"img-responsive\" src=\"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-content\/uploads\/sites\/73\/2018\/05\/VBAUpdates6.png\" alt=\"Documentation &amp; GitHub\" width=\"672\" height=\"400\" \/><\/p>\n<p>Simply click the <em>suggest and submit changes<\/em> link in the <em>Contribute to this content<\/em> section and you&#8217;ll be taken to the same article on GitHub where you can propose changes. After you submit your changes, our team is notified immediately and will respond to your change request. You\u2019ll receive notifications about your change request and status; our team may also notify you if we need more information about your change request.<\/p>\n<p>For more details on how to submit changes, bugs, or issues, or to contribute to the content itself through GitHub, <a href=\"https:\/\/github.com\/OfficeDev\/VBA-content\/blob\/master\/CONTRIBUTING.md\">see this article<\/a>.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Recently, we shared an overview of the new extensibility capabilities available for developers as part of the new Office 2016. In addition to the improvements discussed there, including connecting<\/p>\n","protected":false},"author":69076,"featured_media":1091,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[11],"tags":[],"class_list":["post-1090","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office-add-ins"],"acf":[],"blog_post_summary":"<p>Recently, we shared an overview of the new extensibility capabilities available for developers as part of the new Office 2016. In addition to the improvements discussed there, including connecting<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/posts\/1090","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/users\/69076"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/comments?post=1090"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/posts\/1090\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/media\/1091"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/media?parent=1090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/categories?post=1090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/tags?post=1090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}