VBA compile error: variable not defined in Excel after Office updates to v2212. What's going on?

Question

I have a number of users, myself included, who are getting the variable not defined error when opening files with macros that have worked for years. The common element in all these cases is that Office had updated to v2212. Users with v2211 work fine. Excel often becomes unstable after OKing out of the errors. In addition, I've had a number of people with the same problem respond to a discussion forum post I created a couple days ago. Again, the common element is v2212. If someone could please elevate this situation or provide some additional insight, that would be great. It seems to be affecting a lot of users with VBA code in their files.

Answer 1

@DanIreland,

Have you actually reported this via File -> Feedback -> Send a frown? Might be a silly question on my part but if you have not sent the feedback, I suggest that you do so and provide access to the file you gave to me plus a link to this thread and other thread/s that you are aware of with similar problems.

Answer 2

@OssieMac,

Thanks for the suggestion. I did that on Friday with the frownie face as you suggested. I didn't have quite the knowledge of the problem that I do now, but I did indicate that it appeared to be related to the v2212 update. I may send another feedback with some additional details and a link to the file. I did the VBA cleaning on another more complex file today that had the same problem and although I've only tested it briefly, it seems to have solved the problem, at least temporarily. I plan on doing a lot more testing over the next couple days before I draw any conclusions. Also, I still have a few other files that I need to test to see if they exhibit the same behavior. I'll post any updates here. Thanks again.

Answer 3

We have a similar issue with our macro enabled workbooks (significant VBA projects including userforms).

We use M365 for Business, and we have been updated to Excel Build v2212. When opening the affected workbook via teams/sharepoint or syncing it to the local machine with the OneDrive sync client we get compile errors; i.e. we get VBA errors when the file is trusted.

If we download the workbook from Teams/Sharepoint then the file is 'foreign' and marked with the 'Mark of the Web' (i.e. not trusted), subsequently when opening the file we are required to unblock macros - then the file can be opened with no compile errors and everything works fine

We have only experienced this since Excel v2212 (Jan 4 Build 15928.20198, and Jan 10 Build 15928.20216)

Also: Recently Excel 2212 Build 15928.20216 has been rolled out and is - Microsoft Community

Answer 4

@AlexC84,

Yes, as long as you don't trust the file and keep getting the "Security Warning - Enable Content" warning, the errors don't occur. Very strange. It seems like the code starts compiling before all the forms/modules load and it doesn't see some of the form controls. Getting the warning allows the complete file to load and then compile properly. Obviously, that's just speculation on my part and has no relevance to fixing the problem. I have a lot of macro enabled files and the error only seems to occur on the more complex ones.

I've been doing some additional testing on the VBA cleaning process that I described in an earlier post. (@Andreas described what it does in an earlier post in this thread.) It seemed to be working when I tested it initially, but I sent a cleaned file to a few users and one of them said it didn't work. However, in all my testing since, it still seems to be working and a few more users have indicated it is working for them. I'm going to be testing it a lot more over the next few days and sending it out to more users, so I'll post back here with the results. However, I don't consider this an acceptable solution as the problem could re-occur in the future or might not work for all users depending on their setups.

Here's another thread on the problem: Variable not defined errors with Excel update 2212 dated 04 January - Microsoft Community

Answer 5

I've also had clients reporting errors on opening a complex addin with 30 forms and 20 modules. Again it only affects some despite all users having upgrade. My difference to what I'm reading in the various threads is that when I open the file to compile it, the compile happens without issue. When the file is subsequently re-opened there are no errors but the next day the user will report the same issue and Groundhog Day ensues. Sending others the re-compiled file does nothing to fix other another user. I've asked users to just hibernate machines rather than switching off but can't guarantee this is being done.

I had tried rebuilding the file by copying code into a new workbook and then dragging the forms into the new workbook rather than exporting and importing. I will try that but I'm not optimistic.

If the above gives anyone an idea to solve then I'd love to hear them!