UC San Diego Health Information Services

Knowledge Base: Fixes and workarounds For Excel for Mac

P a g e 1 | 7

Excel for Mac

Word/Excel does not respond when using "Save as Picture"

ISSUE

Word and Excel do not respond when you right-click an object or image and select Save as

Picture.

STATUS: FIXED

This issue has been fixed in version 16.33

Excel prompts you to grant access to files and then hangs or shows a warning that it

cannot open the .xlsx file because the format or extension is not valid

ISSUE

You may receive an unexpected prompt to Grant Access to files when you try to open a file or

do "Save as..." or "Save a copy". When this happens, Excel may become unresponsive and you

will need to force quit the application to recover.

STATUS: FIXED

This issue has been fixed with version 16.34. Please update to this version or newer to address

this issue. For help installing the latest Office updates, please visit Install Office updates.

"Variable uses an Automation type not supported" error in Visual Basic editor in Excel

for Mac

ISSUE

In Excel for Mac, a new Visual Basic Editor was introduced in October 2017. With this new VB

editor, there's an issue with creating object declarations by choosing from the drop-down menu

at the top of the code window.

If you click the drop-down on the left and choose an item from the list, you should get some

code for the selected event in the drop-down list on the right. You can make additional

Knowledge Base: Fixes and workarounds For Excel for Mac

P a g e 2 | 7

UC San Diego Health Information Services

selections from the drop-down list on the right to create additional code to handle the chosen

event.

Instead, an error occurs that the code is not created.

STATUS: WORKAROUND

Choose one of these workarounds for this issue:

WORKAROUND #1

You can perform the similar step on a computer running Excel for Windows and then copy the

code to Excel for Mac.

WORKAROUND #2

You can go to MSDN and find the syntax for the event you wish to use and type the code

manually into your VBA project. This article describes the Microsoft Excel "workbook" events

- Workbook Events.

WORKAROUND #3

Knowledge Base: Fixes and workarounds For Excel for Mac

P a g e 3 | 7

UC San Diego Health Information Services

Copy the appropriate event code from the samples below and paste it to your VBA project. For

each event, be sure to copy from "Private Sub..." to "End Sub", inclusive.

ThisWorkbook

Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_AddinInstall()

End Sub

Private Sub Workbook_AddinUninstall()

End Sub

Private Sub Workbook_AfterRemoteChange()

End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

Private Sub Workbook_BeforeRemoteChange()

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

Private Sub Workbook_Deactivate()

End Sub

Private Sub Workbook_NewChart(ByVal Ch As Chart)

End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)

End Sub

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)

End Sub

Private Sub Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)

End Sub

Knowledge Base: Fixes and workarounds For Excel for Mac

P a g e 4 | 7

UC San Diego Health Information Services

Private Sub Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal

Success As Boolean)

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As

Boolean)

End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As

Boolean)

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

End Sub

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

End Sub

Private Sub Workbook_SheetPivotTableAfterValueChange(ByVal Sh As Object, ByVal TargetPivotTable As

PivotTable, ByVal TargetRange As Range)

End Sub

Private Sub Workbook_SheetPivotTableBeforeAllocateChanges(ByVal Sh As Object, ByVal

TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long,

Cancel As Boolean)

End Sub

Private Sub Workbook_SheetPivotTableBeforeCommitChanges(ByVal Sh As Object, ByVal

TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long,

Cancel As Boolean)

End Sub

Private Sub Workbook_SheetPivotTableBeforeDiscardChanges(ByVal Sh As Object, ByVal

TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long)

End Sub

Knowledge Base: Fixes and workarounds For Excel for Mac

P a g e 5 | 7

UC San Diego Health Information Services

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)

End Sub

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Private Sub Workbook_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject)

End Sub

Private Sub Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType)

End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)

End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

End Sub

Private Sub Workbook_WindowResize(ByVal Wn As Window)

End Sub

Worksheet

Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_BeforeDelete()

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Private Sub Worksheet_Calculate()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Private Sub Worksheet_Deactivate()

End Sub

Knowledge Base: Fixes and workarounds For Excel for Mac

P a g e 6 | 7

UC San Diego Health Information Services

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

End Sub

Private Sub Worksheet_LensGalleryRenderComplete()

End Sub

Private Sub Worksheet_PivotTableAfterValueChange(ByVal TargetPivotTable As PivotTable, ByVal

TargetRange As Range)

End Sub

Private Sub Worksheet_PivotTableBeforeAllocateChanges(ByVal TargetPivotTable As PivotTable, ByVal

ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

End Sub

Private Sub Worksheet_PivotTableBeforeCommitChanges(ByVal TargetPivotTable As PivotTable, ByVal

ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

End Sub

Private Sub Worksheet_PivotTableBeforeDiscardChanges(ByVal TargetPivotTable As PivotTable, ByVal

ValueChangeStart As Long, ByVal ValueChangeEnd As Long)

End Sub

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)

End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)

End Sub

Knowledge Base: Fixes and workarounds For Excel for Mac

P a g e 7 | 7

UC San Diego Health Information Services

Can't export a SharePoint list to Excel for Mac

ISSUE

When you click Export to Excel to export a SharePoint list on a Mac, it doesn't open the file in

Excel for Mac and instead downloads the query.iqy file. This is working as expected as the

internet query connection is not supported by Excel for Mac.

WORKAROUND

If you have Excel for Windows, you can save the file as XLSX and then open it in Excel for Mac.

You'll be able to see the data but you won't be able to refresh the data connection.