Unable to access the Worksheet window in Visual Basic editor in Excel (Microsoft 365) on Mac with error message "Variable uses an Automation type not supported in Visual Basic"

Question

Long time Excel user, but new to VBA. Attempted to copy VBA code to sort a worksheet into the VB Editor Worksheet window. VBE would not open the Worksheet widow and produced the error message "Variable uses an Automation type not supported in Visual Basic." Error message appears regardless of which existing Excel file VBE Worksheet widow I try to open and even in new blank workbooks. There is no user entered VBA code in any of the workbooks in which I've tried to access the Worksheet window. I completely uninstalled MS 365 on my Mac and reinstalled it, but the error still appears. I had previously (before deleting and reinstalling MS 365) copied the code into one Excel file on my MAC, but I had deleted the code and saved the file after deleting it.

Can anyone tell me how to overcome this problem?

Answer 1

Hi @Brian GormleyWhich version of Office are you using?

If "even in new blank workbooks", VBE cannot be opened with such error message, I suggest you clear the Excel cache.

• Close Excel, open Finder.

• Press Command + Shift + G, input “~/Library/Containers”, move com.microsoft.Excel to desktop.

• Press Command + Shift + G, input “~/Library/Preferences”, move com.microsoft.office.plist to desktop.

• Open Excel, and check if VBE can be opened correctly.

If the problem remains, please try creating a new user profile in Mac then use the new one to open Excel to check if the issue is caused by account settings and preferences.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---