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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When processing information using a macro, you may need to know if there are any other instances of Excel running on a ...
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 MoreThe VBA programming language provided with Excel allows you to create and modify text files quite easily. Here's how to ...
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 © 2024 Sharon Parq Associates, Inc.
Comments