Written by Allen Wyatt (last updated June 10, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
When Steve tries to create an event handler, either for a workbook or for a worksheet, he gets an error message: "Variable Uses an Automation Type not Supported in Visual Basic." Excel won't allow the creation of any event handlers, even if Steve saves the workbook in XLSM format. He wonders what causes this error and how he can create the event handlers he needs?
It is not clear from the question which version of Excel Steve is using, but if he is using on a Mac platform, this problem has been known for some time. There is information about the problem here, on this web page:
https://support.microsoft.com/en-us/topic/-c3125bf3-c30b-4d62-be42-d2f1bec0e5ad
It is amazing that Microsoft has known about this problem for at least six years (since 2017) and it still has not been fixed. The web page provides some workarounds for the issue, as well. (And one jaw-dropping workaround is to create your event handler using Excel on a Windows system, and then transfer it to the Mac.)
Head-shaking workarounds aside, it is possible that Steve is experiencing the error on a Windows-based system. If this is the case, then the most likely issue is a problem with a variable defined in some pre-existing event handler or macro already on the system. The way to see if this is the cause is to get out of Excel, rename the personal macro workbook, restart Excel, and see if the problem exists with a new, blank workbook. If it goes away under those conditions, then you know you have some detective work to do in order to track down the problem in one of the macros in the personal macro workbook.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13374) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Do you get tired of the dialog box that says "do you want to enable macros" that is displayed when you open a workbook. ...
Discover MoreWant a quick way to convert positive values to negative and vice versa? You can create your own plus/minus button by ...
Discover MoreWhen you assign a macro to a shortcut key, you make it easy to run the macro without ever removing your hands from the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments