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

Just a quick update on this. We had a call with MS Support on this today and in between I had added a variable at the top of the code for the form that was causing the issue:

Dim Test as string

I then saved the workbook and re-opened it and the issue went away.

It also went away for all other users that had been updated to v2212 prior to me saving the workbook. However, one of my users auto-updated to v2212 today and the issue came back for her...

MS Support also gave me a registry setting to try as well. See below:

Reg.exe add "HKCU\Software\Microsoft\Office\16.0\Excel\Options" /v "ForceVBALoadFromSource" /t REG_DWORD /d "1" /f

You may want to try this with users that are having the Compile Error issue where you get a Variable not defined error.

I haven't tried the above solution yet so I cannot confirm if it worked or not. Just passing on the info from MS Support.

Answer 2

@davidr505,

Thanks for the update. The registry entry is especially interesting. It has apparently been around for a while and was mentioned as an option in an Excel 2016 update from 2017 (August 8, 2017, update for Excel 2016 (KB3085435) - Microsoft Support). I really think this problem has been around for a while in some form but whatever changed in 2212 made it more frequent and repeatable across Office installations. With VBA being pretty much a dying language, I really think Microsoft would love to get rid of it and doesn't bother much with it, but die-hards like me keep using it lol.

OK, enough rambling from me.

Answer 3

@DanIreland

Yes. This Registry setting is nothing new but doesn't exist unless you create it. So it may provide a temporary reprieve until a proper fix can be deployed, hopefully in a very near update.

With regards to VBA in Office and Microsoft's continuing push to move all their applications to the web, I fear they have all but abandoned VBA support in favor of their Javascript APIs as this is the future of Office. Given the fact that pretty much the whole financial world runs on Excel it's a shame that they've gone down this road as VBA had a tremendous amount of power in cross application integration, and those of us that go back far enough with Office know that MS really touted the VBA macro side of things and how you could create fully integrated Office applications when it was first introduced. I've used it for years to work in Excel, Access, Visio and even AutoCAD to connect these applications together and exchange data between them. They even seem to have abandoned VSTO in favor of the Javascript APIs.

Ramble mode off ;)

Answer 4

is the problem still present after updating to Excel v2301 (Jan 26th)