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

I have run some tests but so far, I have not been able to emulate the problem.

Are you using Option Explicit as the first line in the module and then declaring the variables with the Dim statement? If not, try inserting Option Explicit at the start of the module and then declare the variables. After declaring the variables, Select menu item Debug -> Compile and it will identify any variables that have not been declared. More information on declaring variables at the following Microsoft link.

https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables

Also, I am wondering if it is specific types of variables so could you please post some code and tell us which line is producing the error.

Answer 2

Thanks OssieMac for responding. I'm an experienced programmer so I'm doing all the basic things you're suggesting. This is code that has worked for years across multiple Excel versions. The problem seems to only come up in complex files with a lot of code. I'm the sole proprietor of a small company called DJI Computer Solutions and I've been selling Excel applications online for more than a decade. The files have a lot of forms and thousands of lines of code.

I can send you an affected file if you like as that's the only way I know for you to duplicate the problem The weird thing is that if the file is untrusted and you get the Enable Content warning, the file opens fine. However, if you trust the file and open it directly without the warning, the errors occur.

I would appreciate any help you can provide. I have dozens of customers contacting me looking for a solution and the only thing I can offer them is the rollback, which is not that easy since most of them are not computer savvy and is in no way a permanent solution. Thanks again.

Answer 3

I have found that sometimes the error messages returned are ambiguous and one has difficulty relating them to the actual problem. I am wondering if this is such an issue.

Therefore, refer to the following link and see if it helps. Note that if the file came from another source, then download the file to your computer without opening it first, or if an attachment in an email, save the attachment without opening it first. Then navigate to the file in Windows Explorer and right click the file and select properties and then follow the guidelines in the following link.

https://support.microsoft.com/en-au/topic/a-potentially-dangerous-macro-has-been-blocked-0952faa0-37e7-4316-b61d-5b5ed6024216 

As a matter of interest, even a file containing VBA that you create and develop yourself, if you email it to yourself then it gets blocked, and then need to follow the procedure in the above link to unblock it.

PS. Added with edit: I have also answered another question recently where it had lost one of the default references in Tools -> References, so check that the default references are there, plus any that you have added for the project.

Answer 4

I edited and appended to my previous post so if read before getting this message then check it again.

Answer 5

Thanks OssieMac, I initially thought there might be an issue with the references, but I've checked them and they're all intact and pointing to the correct file. I have fourteen users of two different programs reporting the problem so far and I expect to get a lot more. I get a few every day since the Office update has been rolling out. I've reliably reproduced the problem on multiple computers. I'd love to send you a file so you can see for yourself. If you'd like, you can download a copy of the affected file by going to my website at www.djicomputer.com and clicking on the Handicap Manager link and downloading the zip file version of the program. Unblock the macros in the included Excel file and open the Excel file. When you get the security warning, click the Enable Content button, then click Yes when prompted to Trust the file. The file will open without error. Then close the file and re-open it and you should get the "Variable not defined" error as long as you're not prompted again to Enable Content. I'm not sure if these files run on the Mac version of Excel so it would be best to test it on a Windows machine if possible.

Myself and all my users have been using these files for months/years with no issues and they're getting the errors since the update. Rolling back the updates solves the problem so there has to be something related to the update. I posted on another forum (see link below) and a few other people have chimed in with the same issue. I hate to take up your time but I'm desperate for an answer. If you could look into it further or somehow escalate my concern or point to some way that I can escalate it. I've contacted tech support and they just want to roll back the update to fix the problem. I've cleaned all my code by exporting the modules, creating new ones and copying the code text back in thinking that might solve it but no such luck. I have hundreds of users and I expect to get more complaints every day as the update rolls out and they start using their files. Thanks in advance for any assistance you can provide.

Link to other forum post: Variable not defined errors with Excel update 2212 dated 04 January - Microsoft Community