Cannot add code in Sheet1 or ThisWorkbook within Visual Basic for Applications

Question

Dear Team,

I have Microsoft 365 Personal License

I am using macOS Tahoe 26.5, Microsoft® Excel for Mac, Version 16.109.1 (26051717)

An old problem I'm running into again today. Start Excel, choose ‘Developer’, choose ‘Visual Basic’. Within Visual Basic Editor, Select ‘ThisWorkbook’, Under (General) select ‘Workbook” the following error occurs: -Variable uses an Automation type not supported in Visual Basic-

How can it be solved.

Answer 1

This is a known issue in the Visual Basic Editor in Excel for Mac when using the event drop-downs (for example, selecting Workbook under (General) in ThisWorkbook). The editor fails to insert the event procedure and instead shows the error “Variable uses an Automation type not supported in Visual Basic.”

This cannot currently be fixed by a setting; instead, use one of these supported workarounds:

1. On a Windows PC with Excel, open the same workbook, go into the Visual Basic Editor, select ThisWorkbook, choose the desired object (Workbook) and event from the drop-downs so that Excel generates the event procedure stub, then save the workbook and copy it back to the Mac.
2. Look up the correct event syntax in the VBA reference (for example, the Workbook events documentation) and type the event procedure manually in ThisWorkbook or the relevant sheet module. Ensure the procedure signature exactly matches the documented event (from Private Sub ... through End Sub).
3. Use sample event code: copy a complete event procedure (starting with Private Sub ... and ending with End Sub) from trusted samples and paste it into ThisWorkbook or the sheet module, then edit the body of the procedure as needed.

Once the event procedure is present in the module (created on Windows or typed/pasted manually), it runs normally on Excel for Mac despite the designer limitation.

---

References:

• "Variable uses an Automation type not supported" error in Visual Basic editor in Excel for Mac
• Fixes or workarounds for recent issues in Excel for Mac
• Office for Mac
• Using Excel worksheet functions in Visual Basic