VBA Code works in 2010, but not in 2016

We have a set of about 15 worksheets, all about 90% identical, that are used to pull data from some different databases for running some performance metrics. We moved from Office 2010 to Office 365/2016 back in July. Before that move was made, we spent a few months testing for compatibility with the Excel and Access files we had, and once we were satisfied, we migrated everyone to 365/2016.

The week before Thanksgiving, everything was working fine. Then we went home for turkey, and came back on Monday, and it was a shitshow...

The theory is that over the weekend, Office updates were rolled out to everyone's systems. IT has acknowledged that an update was rolled out over the weekend...

Now this set of spreadsheets no longer functions properly.

After spending a day and a half, I had IT restore the spreadsheets from the Tuesday prior to Thanksgiving, hoping that would fix the issue. No such luck.

The issue is that about 95% of the vba code functions properly. Several of the spreadsheets use the table feature, and for some reason the table range (which initially should be the width of the active data, and the length of the active data) is getting reset to 1 row. So when we select the data in that table, it only returns 1 row instead of the correct 350-400 rows.

The maddening thing is that if I step through the code, about 1 out of 5 times it will function properly. But if I just let the code run, it fails 100% of the time.

I just had IT uninstall office 365/2016, and reinstall 2010. The file works properly. This is a diagnostic step, not a solution.

Does anyone know of any issues with a recent office update?

This is a set of spreadsheets that I've inherited - I didn't create them. An overview: go to the master tab, click on the 'delete data' button to clear out the target sheet. Then click on the 'update' button, which takes you to a download tab. Click the download button, and the vba pulls data from a few databases, and puts the results into the clipboard. Go back to the master tab, and click on the 'copy data', which pastes the data into the worksheet. This all works fine. The last step is to click on 'update comments', which does a vlookup to pull in additional info from an existing tab. This is where the vba code is failing, because the table for the data that was pasted should be 350 rows, but for some reason it is getting set to 1 row in length.

Thanks!

Steve

Originally Posted by CK76

there really hasn't been much change from 2010 to 2016

My experience has been quite different. You are right in that it shouldn't make a difference but I have seen code misbehave and show different symptoms even between subversions of Excel. VBA is not one size fits all, just because it ran in 2007 or 2010, etc doesnt mean it will run in the current version or the next.

What I have found is that with each iteration of Office they get more and more strict with how code is compiled/executed. Implicit code that used to run fine breaks and I have found typically is resolved by re-writing code explicitly. All in all VBA has become much less forgiving in what is passable as runable code.

Without specific code/sample files the best I can say is debug the code. Add debug.print, add watches, step through the code and observe what the variables and such return as you go through the code. When possible declare variables/constants, use explicit statements (ex: workbook.worksheet.range() instead of Range()), etc.

Compare versions, are both Office 2010 and 2016 32 bit or is one of them 64 bit? This matters greatly for VBA.

Implicit code that used to run fine breaks

That's really not issue with VBA or Excel. That's more of issue with how the code is written and making sure appropriate object is active when the code is run (or using explicit reference).

However, I've not really encountered issue myself in this regard when moving from versions (any versions from 2003 to 2016).
Often, codes are run on at least 2 different versions or more by users and haven't had issue.

32 bit vs. 64 bit only really impacts if you have API call, running Shell command, some Form.Controls etc.
Object model within Excel, such as ListObject or Range will not be impacted.

I had IT uninstall Office 365/2016 and install Office 2010. The spreadsheets functioned properly.
So I had them uninstall Office 2010 and re-install Office 2016.
Surprisingly, the spreadsheets now work.
Looking at the version info, the Excel that was just installed (and works) was Office Professional Plus 2016, while the version that doesn't work was Office 365 Pro Plus. Both versions were 32 bit versions.

On 11/23/2017, we had an update pushed out: Update for Office 2010 (KB4011188) even though we didn't have Office 2010. That is what seems to have broken the program...

IT is going to try following the same steps on the other two peoples systems that use these spreadsheets. If it corrects their problems, then we will still have to investigate what broke the Office 365 version. Because we don't know what the upper level of IT will want pushed out to users...

I'm working on putting together a de-sensitized version of the spreadsheet that I could post. Although I know that the code is not that efficient - TMS has already posted a few changes he recommended last week. To my eye it looks like someone took some code generated by a macro-recorder and then added a bunch of vba... plus it uses a ton of vlookups...

...just installed (and works) was Office Professional Plus 2016, while the version that doesn't work was Office 365 Pro Plus.

This shouldn't be an issue. I use Office 365 Pro Plus and run all my codes written in 2010 or before without issue (though I had to convert 32 bit API to 64 bit for some codes, like Sleep Lib etc).

More than likely, the issue was that when upgrade is made from 2010 to 2016/365, something went wrong without it being flagged. This is why first step to trouble shooting when upgrade is made on Office installation, is to do clean install of latest version. Be it issue with VBA, or Add-in like PowerQuery/PowerPivot.

I had some trouble with an Excel 2016 update about 6 months ago with code that was working fine before the update.
After many hours of frustration I managed to solve my problem.

My code was copying data between ranges and inserting rows...if there was anything in the clipboard when the the insert row code ran it would behave not as it should.


I solved my problem by setting application.cutcopymode = false after the copy/paste code completed so as to leave nothing in the clipboard.

Not sure if that is of any help to you.