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 downloaded the file and extracted it. (I did not try to open it inside the Zip file so that I can keep the original download).

I then navigated to the file in Windows Explorer, right clicked the file and got the result as per the screen shot below, showing the file is blocked.

I checked the Unblock box -> Clicked Apply -> OK (Note must click Apply before OK or does not work)

I then opened the file in Excel and got the message "variable not defined" and it highlighted obSortByName as the error.

I used the Find Feature in VBA and set the parameter to "Current project" and I cannot find the Dim statement anywhere in the project. See second screen shot below.

Please advise if I am missing something because the variable appears in multiple subs throughout the project so probably needs to be dimensioned as a public variable.

Answer 2

That is a control inside the form:

Unfortunately I can't reproduce the error because I don't have the version.

I suspect the problem is related to the MsForms DLL, resp. the controls. If you can use Userforms without issues in a new file, I would try the VBA Code Cleaner to rebuild the project. And delete the *.exd files too.

http://www.appspro.com/Utilities/CodeCleaner.htm

Andreas.

Answer 3

@Andreas,

Yes! You are so correct. Thanks for that. I had some difficulties working out what the project was doing, and I was not thinking clearly enough.

@DanIreland,

I performed the following procedures.

• As per my previous post, I unblocked the file.
• Exported the fReports Userform.
• Deleted (Removed) the Userform.
• Imported the Userform again from the exported files.
• Saved the file.

I can now open the project without errors. and the problem appears to have resolved itself. However, you need to test this solution and see what occurs.

On a number of previous occasions, I have performed the above procedure successfully, because I have been told that it can remove corruption from a Userform and on some occasions, I have found that it works.

Makes me wonder if the process that Microsoft are using to protect the users is causing corruption in the Userforms, because if so, it might also apply to your other Userforms.

Answer 4

• Deleted (Removed) the Userform.
• Imported the Userform again from the exported files.
• Saved the file.

I can now open the project without errors. and the problem appears to have resolved itself. However, you need to test this solution and see what occurs.

Inside the VBA project is a stream (binary cache) where the compiled data is stored in. (it's not possible to see/view that data with the VBA environment)

If we export / delete a code module and import it again removes also the associated stream and the compiler creates the references/links between the code and objects from scratch.

There is a step that we can try before we export / delete / import all modules:

Make a change in all code modules (insert a blank line and remove it immediately), then compile the project.

In many cases this works also.

Andreas.

Answer 5

@Andreas and @OssieMac,

I can't thank you enough for the time you devoted to this. I've got several more users with the problem this morning. Like you, I thought cleaning the code modules might solve the problem. So on Friday, I cleaned all the code as folllows: I exported all the forms and deleted them from the file I copied all the code from the modules and class modules to text files and deleted them from the file. I copied all the workbook/worksheet code to text files and deleted the code. I then saved the file as a .xlsx file.

From there I imported the forms, created new class modules and modules and copied the code back in, then copied the code back into the workbook/worksheets and saved the file as an xlsm again. As you indicated, after this I was able to open the file without the issue. So, I imported the data from the four users who had been affected up to that point into separate copies of the empty, cleaned file and tested them to make sure they worked and they did. I then sent the newly cleaned files back to them and yesterday two of the users reported back to me that after closing and re-opening the file a few times, the problem had reappeared. I took their word for it and worked on other options after that, so I haven't rigorously tested that fix myself yet on multiple computers. I plan on doing that today. I also have another file that I'll be testing it on today as this is not the only program file that is throwing this error. I'll be posting back here with any new insights I get from my testing.

One last thing (sorry to be so long winded). The error does not occur if you get the Security Warning - Enable Content prompt. So, if you never trust the file, the error doesn't occur. I've confirmed that with multiple users and tested it pretty thoroughly myself. It's almost as though the warning allows the file to completely load before Excel compiles the code and runs the Open macro. I've even tried using code to delay the start macro as I've used this before to prevent other weird vba behavior but since the error occurs while the code is compiling, that doesn't work.

Your insights have been very helpful. I'll be testing this solution more thoroughly today, but I'm convinced there is some underlying issue in the 2212 update. I've encountered weird VBA behavior before in Excel, but it's never been this repeatable across so many users. Thanks again.