Use Relative References Macro Excel For Mac
Use VBA add-ins and macros that you developed for Office for Windows with Office for Mac.
Applies to: Excel for Mac PowerPoint for Mac Word for Mac Office 2016 for Mac
By default, Excel records macros in absolute mode. However, sometimes it is useful to record macros in relative mode. This program teaches you how to do this.
If you are authoring Macros for Office for Mac, you can use most of the same objects that are available in VBA for Office. For information about VBA for Excel, PowerPoint, and Word, see the following:
Note
Outlook for Mac and OneNote for Mac do not support VBA. Perpuswebpro password.
Office 2016 for Mac is sandboxed
Unlike other versions of Office apps that support VBA, Office 2016 for Mac apps are sandboxed.
Sandboxing restricts the apps from accessing resources outside the app container. This affects any add-ins or macros that involve file access or communication across processes. You can minimize the effects of sandboxing by using the new commands described in the following section.
Creating an installer or putting user content
For instructions on creating an installer for your add-in, please refer to the article here: Installing User Content in Office 2016 for Mac
New VBA commands for Office 2016 for Mac
The following VBA commands are new and unique to Office 2016 for Mac.
| Command | Use to |
|---|---|
| GrantAccessToMultipleFiles | Request a user's permission to access multiple files at once. |
| AppleScriptTask | Call external AppleScript scripts from VB. |
| MAC_OFFICE_VERSION | IFDEF between different Mac Office versions at compile time. |
Ribbon customization in Office for Mac
Office 2016 for Mac supports ribbon customization using Ribbon XML. Note that there are some differences in ribbon support in Office 2016 for Mac and Office for Windows.
| Ribbon customization feature | Office for Windows | Office for Mac |
|---|---|---|
| Ability to customize the ribbon using Ribbon XML | Available | Available |
| Support for document based add-ins | Available | Available |
| Ability to invoke Macros using custom ribbon controls | Available | Available |
| Customization of custom menus | Available | Available |
| Ability to include and invoke Office Fluent Controls within a custom ribbon tab | Available | Most familiar Office Fluent Control Identifiers are compatible with Office for Mac. Some might not be available. For commands that are compatible with Office 2016 for Mac, see idMSOs compatible with Office 2016 for Mac. |
| Support for COM add-ins that use custom ribbon controls | Available | Office 2016 for Mac doesn't support third-party COM add-ins. |
idMSOs compatible with Office 2016 for Mac
For information about the idMSOs that are compatible with Office 2016 for Mac, see the following:
See also
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
A relative reference in an Excel macro means relative to the currently active cell. So use caution with your active cell choice — both when you record the relative reference macro and when you run it.
First, open Sample File.xlsx available online. Then, use the following steps to record a relative reference macro:
On the Developer tab, select the Use Relative References option.
Make sure cell A1 is selected.
On the Developer tab, select Record Macro.
Name the macro AddTotalRelative.
Choose This Workbook for the save location.
Click OK to start recording.
Select cell A16 and type Total in the cell.
Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15).
On the Developer tab, click Stop Recording to stop recording the macro.
At this point, you’ve recorded two macros. Take a moment to examine the code for your newly created macro by selecting Macros on the Developer tab to open the Macro dialog box. Choose the AddTotalRelative macro and click Edit.
Again, Visual Basic Editor opens and shows you the code that was written when you recorded your macro. This time, your code looks something like the following:
First note that the code does not contain references to specific cell ranges (other than the starting point, A1). Note that in this macro, Excel uses the Offset property of the active cell. This property tells the cursor to move a certain number of cells up or down and a certain number of cells left or right.
In this case, the Offset property code tells Excel to move 15 rows down and 0 columns across from the active cell (A1). Because the macro was recorded using relative reference, Excel will not explicitly select a particular cell as it did when recording an absolute reference macro.
To see this macro in action, delete the Total row for both tables and do the following:
Select cell A1.
On the Developer tab, select Macros.
Find and select the AddTotalRelative macro.
Click the Run button.
Select cell F1.
On the Developer tab, select Macros.
Find and select the AddTotalRelative macro.
Click the Run button.
Note that this macro, unlike your previous macro, works on both sets of data. Because the macro applies the totals relative to the currently active cell, the totals are applied correctly.
For this macro to work, you simply need to ensure that
You’ve selected the correct starting cell before running the macro.
The block of data has the same number of rows and columns as the data on which you recorded the macro.