Variable not defined errors with Excel update 2212 dated 04 January

Question

I'm an Excel developer and one of my customers started getting "Variable not defined" errors when opening an xlsm file that I provided. (All the flagged variables are definitely declared.) The macros in the file have run for years without any issues. Upon digging into the problem, it appears it started when update 2212 dated 04 January installed on his computer. He sent me the offending file and it ran fine on my computer. Comparing Excel versions, I found that my Office 365 installation had not updated, so I updated it and started getting the exact same errors. Digging further, I discovered that many of my other macro containing files were throwing the same error.

The error occurs when the VBA modules compile right when the file opens and runs the file open routine. Although the same variables are flagged each time, there does not appear to be anything unusual with those particular variables. In one file it actually flags a form control as not being defined. After stopping the compile process, Excel sometimes becomes unstable.

Long story short, it seemed to be related to the upgrade, so I chatted with Microsoft support, they rolled back the upgrade and we're no longer getting the errors. I was curious if anyone else had experienced this problem or something similar with the 2212 update. It only seems related to .xlsm files as far as i could tell, although I didn't do a lot of testing before rolling back the update.

Answer 1

Shivaji,

Glad that worked for you. For what it's worth, I've got hundreds of users (most aren't very computer savvy) and I've only had one report of the problem. This was on 09 January and at that time my version of Office had not updated to 2212 on the 3 computers where I'm running it. It was only after I did the File - Account - Update Options - Update Now function on one of my computers that I started having the problem.

I'm not sure how Microsoft rolls out its Office updates, but it does not appear that many people have the update, or it doesn't affect all users. I'm hoping they fix the problem, otherwise I'm going to get inundated with support requests and most people would struggle to do the rollback; they just expect things to work.

I may update one of my other computers next week to the 10 January release and see if the problem persists. I'll post back here with any new findings.

Good Luck ..... Dan

Answer 2

we can just wait and hope for the release of new build .

Answer 3

Same here - any speadsheet with macro's are having complie errors.

Rolling back to December's update has fixed

used the following to roll back

"C:\Program Files\Common Files\microsoft shared\ClickToRun\officec2rclient.exe" /update user updatetoversion=16.0.15831.20208

Answer 4

I thought I might have solved the problem with what i describe below, but after saving and re-opening the file a few times the problem is re-appearing so back to square one I'm afraid. I hope this problem gets addressed soon. The previous text in red is based on feedback from one of my users but he may have been opening the wrong file. I've been testing a couple files that I cleaned using the method described below and they seem to be working. I also have a couple users testing them and so far, so good. However, I've only tested on a handful of computers and this fix might not work for all files. I don't consider this a permanent fix as the files could start misbehaving in the future as the code is exactly the same. Any future changes could cause the problem to re-appear. Hopefully Microsoft addresses this issue soon.

Thanks for posting the simplified rollback command. Since there don't seem to be a ton of people with this problem, I figured there must be a lot of files with the macros still working so I've been experimenting with some solutions. I've been able to get two different Excel/VBA projects working with the latest 2212 release. They're both complex files with a lot of code and forms. I got them working by exporting all the forms to frm/frx files, copying all the module, class and worksheet code into text files. Then I deleted all the modules, forms, code, etc. from the file and saving it as an .xlsx file. From there I imported all the forms, then created new modules and classes and copied the code into from the text files I had saved earlier. I also had a small amount of ThisWorkbook code and a few lines in a couple worksheets that I copied back in from text files. (The first time I tried this, I just exported and deleted the forms, modules and classes then reimported them without copying/pasting or converting the file to .xlsx but that didn't solve the problem. It wasn't until I did the rigorous cleaning described above that it solved the problem.)

After restoring all the code, I saved the file as an .xlsm. I did all the work on a computer I had rolled back and then tested the files on a computer with the update. I haven't had a chance to rigorously test them but so far so good. They open without error and all the automated functions I've run so far seem to be fine. I plan on doing some additional testing tomorrow and will post again here with whatever I discover.

Answer 5

As all of you above my company is also hit by this issue.

We have hundreds of users which create thousands of copies from an Excel Template they acquired from us.

One thing to note. In our case, as far as we know, it only happens on the 32-bit version of Excel and not on the 64-bit version.

We are awaiting a response from Microsoft incident support, but up until now no update from them.

UPDATE:

Today we got response from our clients who are using Office 64-bit and also have the issue common in this topic.