Vb Editor For Excel 2016 For Mac

  1. Vb Editor Excel 2016
  2. Visual Basic For Excel 2016
  3. How To Open Vba In Excel 2016
  4. Vba Editor In Excel 2016

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 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.

You can access the VBA environment in Excel 2016 by opening the Microsoft Visual Basic for Applications window. First, be sure that the Developer tab is visible in the toolbar in Excel. The Developer tab is the toolbar that has the buttons to open the VBA editor and create Form/ActiveX Controls like buttons, checkboxes, etc. Sep 26, 2018  Bring back a fully functional VBE (Visual Basic Editor for VBA) Please bring back the full editor capability of VBE to Mac Excel 2016, like we had in Mac Excel 2011. As it is, the 'slimmed down' editing experience of VBA is more like a notepad experience than anything. Sep 04, 2015  Office 2016 for Mac provides a simplified Visual Basic Editor (VBE) for viewing/debugging existing macros. We recommend that customers develop macros or VB add-ins in Office for Windows and use Office for Mac to debug if needed.

One thing to note, our engineering focus is Office web add-ins – 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’ll come to the rest of Office clients over time.

Office 2016 for Windows

New VBA object model features

Several Office desktop applications have new features that are exposed in the VBA object model. Here are some of the highlights:

  • 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 Queries and WorkbookQuery objects, and their associated properties and methods. For more information about Power Query, see this blog post and download this sample. Excel exposes several new methods on the WorksheetFunction object to enable forecasting of statistical functions. Finally, there are a number of new objects related to the existing Model object, such as the ModelFormatBoolean and ModelFormatCurrency objects, and many new properties on each of these new objects.
  • Outlook has added a new PreviewPane object and a new WordEditor 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.
  • Project adds new Engagement and EngagementComment objects and associated methods and properties to allow programmatic access to engagements scheduled in a project.
  • Visio has added new OM to permit information rights management (IRM) of Visio documents.

Office 2016 for Mac

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.

In response to this feedback, we’ll further improve Office 2016 for Mac’s support for VBA add-ins. Specifically, we’ll 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.

Ribbon customization via Ribbon XML

We are excited to announce that with the latest Office for Mac update, you’ll be able to fully customize the ribbon across Word, Excel, and PowerPoint for the Mac using familiar Ribbon XML.

Here’s how the Mac Word ribbon looks with a custom tab.

If you’ve been customizing the ribbon on Office for Windows, you’ll find the Mac experience to be very similar. However, there are certain important differences to know about, as described in the table below.

Comparison of ribbon customization functionality in Office 2016 for Mac and Windows


* Most familiar Office Fluent Control Identifiers are compatible with Office for Mac. Some may not be available.
** May support this in future.
*** Office 2016 for Mac doesn’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.

To enable Ribbon XML:

  1. Open Terminal.
  2. Type this command:
    “defaults write com.microsoft.office EnableRibbonXmlDeveloperMode -bool YES”
  3. Reboot the app.

To disable Ribbon XML:

  1. Open Terminal.
  2. Type this command:
    “defaults write com.microsoft.office EnableRibbonXmlDeveloperMode -bool NO”
  3. Reboot the app.

In early 2016, ribbon customization via Ribbon XML will be enabled by default for all customers.

FAQ

How do I edit Ribbon XML?

You can edit Ribbon XML by using familiar Custom UI Editor Tool, which remains available only on Windows.

Will these updates be pushed as part of the auto-update process?

Yes. These updates will be pushed to all customers using Microsoft Auto Updater.

Is the structure of the XML file the same as that for Office for Windows?

Yes, Office 2016 for Mac ribbon reads customizations from CustomUI.xml and CustomUI14.xml.

Will the Office 2016 for Mac ribbon load the XML from both files, CustomUI.xml and CustomUI14.xml?

Yes, and just like the behavior in Office for Windows, CustomUI14.xml will be given preference over CustomUI.xml.

Will the Office 2016 for Mac ribbon accept PNG files that are loaded into the document’s custom UI as control images?

Yes.

Visual Basic Editor

You spoke, we heard!

As we continue to improve the overall Visual Basic story on Office 2016 for Mac, we’ve added the following features to Visual Basic Editor:

  • Ability to add modules from within the Project Viewer
  • Ability to add library references using a dialog
  • Ability to use shortcut keys for debugging within Visual Basic Editor, such as Command+Shift+I for Step Into, Command+Shift+O for Step Over.

New Commands

You might not be aware that we recently added two new commands to the Visual Basic object model for Mac: GrantAccessToMultipleFiles and AppleScriptTask.

We also added a new conditional, “MAC_OFFICE_VERSION”. (See New Commands Documentation later in this article.)

As Office 2016 for Mac is sandboxed, users are prompted to grant access every time a file access request is made. GrantAccessToMultipleFiles is a command that takes an array of file pointers and helps minimize the number of these prompts.

Sandboxing also severely breaks the previously existing MacScript command that allows the use of inline AppleScript in Visual Basic. This is where AppleScriptTask can help. Users can store an AppleScript file at a specified location on the disk and use AppleScriptTask within VB to invoke it. The location of these scripts is specified by the operating system and cannot be altered.

The MAC_OFFICE_VERSION 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.

Since Office 2016 for Mac Beta, we’ve been keeping close watch on issues relating to these new commands and have been making fixes. With this update, we’re releasing some important fixes that will considerably improve the overall performance of these commands. In particular, we’ve fixed various timeout issues related to AppleScriptTask.

New Commands Documentation

AppleScriptTask

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.

For example:

on myapplescripthandler(paramString)#do something with paramString return 'You told me ' & paramString end myapplescripthandler

GrantAccessToMultipleFiles

Unlike VB macros in Office for Mac 2011, VB macros in Office 2016 for Mac don’t 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.

Existing macro file commands are changed to prompt the user for file access if the app doesn’t already have access to it. This means that macros that access external files cannot run unattended; they’ll need user interaction to approve file access the first time each file is referenced. You can use the GrantAccessToMultipleFiles command to minimize the number of prompts and make the experience better (see below).

GrantAccessToMultipleFiles

This command lets you input an array of file paths and prompt the user for permission to access them.

Sub VersionConditionals()#If MAC_OFFICE_VERSION >= 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

Note The “#If Mac” conditional remains unchanged from Office for Mac 2011.

Plan Ahead

Starting in Office 2016 for Mac, custom menus that were supported in Office for Mac 2011 are not supported anymore. We’re officially deprecating support for custom menus and CommandBar controls. A few legacy CommandBar controls may continue to appear on the Add-ins tab, however, we advise that you don’t write new code that uses either of the two. Starting today, if you have a VB add-in that requires a UI, it’s best to use Ribbon XML to write that UI.

Overview of UI Extensibility points and corresponding support in Office 2016 for Mac

Overall, as we’ve 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’ll continue to ensure compatibility of existing macros and make improvements to the IDE.

Documentation & GitHub

Our developer help for VBA has also undergone some changes. The first thing to highlight is that we’ve moved our content to an “evergreen” 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 Other Versions right below the topic title.

In addition to the evergreen model, we’ve published the VBA documentation on GitHub. 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’ll see a contributors notice like this:

Simply click the suggest and submit changes link in the Contribute to this content section and you’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’ll receive notifications about your change request and status; our team may also notify you if we need more information about your change request.

For more details on how to submit changes, bugs, or issues, or to contribute to the content itself through GitHub, see this article.

To save time on tasks you do often, you can record the steps of those tasks as a macro. Then, when you want to perform one of those recorded tasks, you can activate the macro to perform the steps for you.

Newer versionsOffice 2011

Word

In Word 2016 for Mac, you can create a macro that has a keyboard shortcut, run a macro, and delete a macro that you no longer need.

Create a macro with a keyboard shortcut

  1. Click View > Macros > Record Macro.

  2. Type a name for the macro, or accept the default that Word provides.

  3. To use this macro in any new documents you create, verify that All Documents (Normal) is selected in the Store macro in list.

  4. Click Keyboard to assign a key combination to your macro.

    Word displays the Customize Keyboard dialog box.

  5. Type a combination of keys in the Press new keyboard shortcut box.

    When you press a key combination, Word displays the command or action currently assigned to that key combination, if any.

  6. If the key combination that you chose is already assigned, delete it from the Press new keyboard shortcut box, and then choose a new key combination.

    Go on to the next step after you have found an unassigned key combination.

  7. To use this keyboard shortcut in any new documents, be sure Normal.dotm, the global template, is selected in the Save changes in list.

  8. Click Assign.

  9. When you are ready to record the macro, click OK.

  10. Click the commands or press the keys for each step in the task.

    Word records your clicks and keystrokes, but it does not record text that you select with the mouse. To select text while recording a macro, use the keyboard.

  11. To stop recording, click View > Macros > Stop Recording.

Run a macro

To run a macro, press the keyboard shortcut assigned to it, or run the macro from the Macros list.

  1. Click View > Macros > View Macros.

  2. In the list under Macro name, select the macro that you want to run.

  3. Click Run.

Delete a macro

  1. Click View > Macros > View Macros.

  2. Verify that the location specified by Macros in includes the location of the macro that you want to delete.

    The list will include the document that is open in the current window, as well as the global template and Word commands.

  3. Under Macro name, select the macro that you want to delete, and then click the minus sign below the list.

  4. When Word asks to confirm that you want to delete the macro, click Yes.

Excel

In Excel 2016 for Mac, you can create a macro that has a keyboard shortcut, run a macro, and delete a macro that you no longer need.

When you record a macro, the macro recorder records all the steps required to complete the actions that you want your macro to perform. These steps can include typing text or numbers, clicking cells or commands on the ribbon or on menus, formatting, selecting cells, rows, or columns, and dragging your mouse to select cells on your spreadsheet.

Note: The commands for recording, creating, and deleting macros, as well as other advanced features, are also available on the Developer tab on the Ribbon. For more information about using the Developer tab, see Use the Developer tab to create or delete a macro in Excel 2016 for Mac.

Create a macro with a keyboard shortcut

  1. Select Tools > Macro > Record New.

  2. In the Macro name box, enter a name for the macro.

  3. To create a keyboard shortcut for the macro, type a letter in the Option + Cmd box.

  4. Select a location for the macro from the drop-down list.

    To save the macro

    Click

    In the document that you are creating macro in

    This Workbook

    In all open documents

    New Workbook

    To be available whenever you use Excel

    Personal Macro Workbook

  5. (Optional) Add a description of your macro.

  6. When you are ready to record, click OK.

  7. Click the commands or press the keys for each step in the task.

  8. To stop recording, select Tools > Macro > Stop Recording. (This step will not be recorded in your macro.)

Vb Editor For Excel 2016 For Mac

Run a macro

To run a macro, press the keyboard shortcut assigned to it, or run the macro from the Macros dialog.

  1. On the View tab, click View Macros.

  2. Click Macros in the pop up menu and select This Workbook.

  3. Select your macro from the list and click Run.

Delete a macro

  1. Select Tools > Macro > Macros.

  2. Select the macro to delete, and then press the minus sign.

  3. A confirmation message will appear. Click Yes to confirm the deletion.

You can also delete a macro from the Developer tab.

  1. On the Developer tab, click Macros.

  2. In the list, click the macro that you want to delete, and click the delete button.

Word

To automate a repetitive task, you can quickly create a macro in Word. You can also use the Visual Basic Editor in Microsoft Visual Basic for Applications to write your own macro script, or to copy all or part of a macro to a new macro.

Record a macro

When you record a macro, all the steps that are required to complete the actions that you want your macro to perform are recorded. However, navigation back to the tab to stop recording is not included in the recorded steps.

  1. On the Developer tab, under Visual Basic, click Record.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the Macro name box, enter a name for the macro.

  3. On the Store macro in pop-up menu, do one of the following:

    To save the macro

    Click

    In the document that you are creating the macro in

    [document name] (document)

    In all open documents

    All Documents (Normal)

  4. Click OK, and then perform the actions that you want to record.

  5. On the Developer tab, under Visual Basic, click Stop.

Use Visual Basic for Applications to create a macro

You can use the built-in Visual Basic Editor to create a macro.

  1. On the Developer tab, under Visual Basic, click Editor.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. If you need to insert a module, in the Visual Basic Editor, on the Insert menu, click Module.

  3. In the code window of the module, type or paste the macro code that you want to use.

  4. When you are finished, on the Word menu, click Close and Return to Microsoft Word.

Run a macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to run, and then click Run.

Edit a macro

To edit a macro, you use the Visual Basic Editor.

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to edit, and then click Edit.

    The Visual Basic Editor opens.

  3. Make the changes that you want.

  4. When you are finished, on the Word menu, click Close and Return to Microsoft Word.

Vb Editor Excel 2016

Copy part of a macro to create another macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to copy, and then click Edit.

    The Visual Basic Editor opens.

  3. In the code window of the Visual Basic Editor, select the lines of the macro that you want to copy.

    Tip: To copy the whole macro, include the Sub and End Sub lines in the selection.

  4. On the Edit menu, click Copy.

  5. In the Project box of the code window, click the module where you want to put the code.

  6. On the Edit menu, click Paste.

Delete a macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to delete, and then click Delete.

PowerPoint

To automate a repetitive task, you can use Visual Basic for Applications to create a macro in PowerPoint.

Use Visual Basic for Applications to create a macro

You can use the built-in Visual Basic Editor to create a macro.

  1. On the Developer tab, under Visual Basic, click Editor.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. If you need to insert a module, in the Visual Basic Editor, on the Insert menu, click Module.

  3. In the code window of the module, type or paste the macro code that you want to use.

  4. When you are finished, on the PowerPoint menu, click Close and Return to Microsoft PowerPoint.

Run a macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to run, and then click Run.

Edit a macro

To edit a macro, you use the Visual Basic Editor.

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to edit, and then click Edit.

    The Visual Basic Editor opens.

  3. Make the changes that you want.

  4. When you are finished, on the PowerPoint menu, click Close and Return to Microsoft PowerPoint.

Copy part of a macro to create another macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to copy, and then click Edit.

    The Visual Basic Editor opens.

  3. In the code window of the Visual Basic Editor, select the lines of the macro that you want to copy.

    Tip: To copy the whole macro, include the Sub and End Sub lines in the selection.

  4. On the Edit menu, click Copy.

  5. In the Project box of the code window, click the module where you want to put the code.

  6. On the Edit menu, click Paste.

Visual Basic For Excel 2016

Delete a macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to delete, and then click Delete.

Excel

To automate a repetitive task, you can quickly create a macro in Excel. You can also use the Visual Basic Editor in Microsoft Visual Basic for Applications to write your own macro script, or to copy all or part of a macro to a new macro.

Editor

Record a macro

How To Open Vba In Excel 2016

All the steps that are required to complete the actions that you want your macro to perform are recorded. However, navigation back to the tab to stop recording is not included in the recorded steps.

  1. On the Developer tab, under Visual Basic, click Record.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the Macro name box, enter a name for the macro.

    Tip: To create a keyboard shortcut for the macro, type a letter in the Option+Cmd+ box.

  3. On the Store macro in pop-up menu, do one of the following:

    To save the macro

    Click

    In the document that you are creating the macro in

    This Workbook

    In all open documents

    New Workbook

    To be available whenever you use Excel

    Personal Macro Workbook

    The personal macro workbook is located at Users/ username/Library/Application Support/ Microsoft/Office/Excel.

  4. Click OK, and then perform the actions that you want to record.

  5. On the Developer tab, under Visual Basic, click Record again.

Use Visual Basic for Applications to create a macro

You can use the built-in Visual Basic Editor to create a macro.

  1. On the Developer tab, under Visual Basic, click Editor.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. If you need to insert a module, in the Visual Basic Editor, on the Insert menu, click Module.

  3. In the code window of the module, type or paste the macro code that you want to use.

  4. When you are finished, on the Excel menu, click Close and Return to Microsoft Excel.

Run a macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to run, and then click Run.

    Tip: To run a macro by using a keyboard shortcut, press OPTION + +the letter you typed when you created the keyboard shortcut.

Edit a macro

To edit a macro, you use the Visual Basic Editor.

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to edit, and then click Edit.

    The Visual Basic Editor opens.

  3. Make the changes that you want.

  4. When you are finished, on the Excel menu, click Close and Return to Microsoft Excel.

Vba Editor In Excel 2016

Configure a macro to run automatically when a workbook is opened

After you complete this procedure, the macro will run whenever you open the workbook that contains the macro.

  1. On the Developer tab, under Visual Basic, click Record.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the Macro name box, type Auto_Open.

  3. On the Store macro in pop-up menu, do one of the following:

    To save the macro

    Click

    In the document that you are creating the macro in

    This Workbook

    In all open documents

    New Workbook

    To be available whenever you use Excel

    Personal Macro Workbook

  4. Click OK, and then perform the actions that you want to record.

  5. On the Developer tab, under Visual Basic, click Record again.

  6. On the File menu, click Save As.

  7. On the Format pop-up menu, click Excel Macro-Enabled Workbook (.xlsm), and then click Save.

Copy part of a macro to create another macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to copy, and then click Edit.

    The Visual Basic Editor opens.

  3. In the code window of the Visual Basic Editor, select the lines of the macro that you want to copy.

    Tip: To copy the whole macro, include the Sub and End Sub lines in the selection.

  4. On the Edit menu, click Copy.

  5. In the Project box of the code window, click the module where you want to put the code.

  6. On the Edit menu, click Paste.

Delete a macro

  1. On the Developer tab, under Visual Basic, click Macros.

    If the Developer tab is not available

    1. On the right side of the ribbon, click , and then click Ribbon Preferences.

    2. Under Customize, select the Developer check box.

  2. In the list, click the macro that you want to delete, and then click Delete.