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"

Brian Gormley 0 Reputation points
2024-09-26T14:59:20.9966667+00:00

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?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,875 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,882 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jiajing Hua-MFST 9,760 Reputation points Microsoft Vendor
    2024-09-27T07:34:39.7666667+00:00

    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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.