vba - Network files randomly being affected since migrating to Windows10 with Excel 2019 and working from home

Question

This is the first time I'm posting so I hope I'm doing it the right way.

The company I work for decided to allow work-from-home for my entire department. Ideally we would all get laptops (Windows 10 + Office 2019) to replace our old desktops (Windows 7 + Office 2010), but the change had to be done gradually and it has proven to be a nightmare.

Initially we had several different files using macros to parse XML data into excel that worked only in the old computers but not in the new ones, but with an easy fix (we changed all DOMDocuments references to DOMDocument60) issue was resolved; however, more issues started arising as soon as co-workers started getting their new laptops.

Right now our main issue is that sometimes (it seems to be completely arbitrary) files work perfectly and all macros execute well; however, as soon as another user opens that same file (files are Trusted Documents within our network) either a compile error shows - Can’t find project or library - or in most cases it doesn't but whenever a macro needs to run, file freezes and shuts down.

I have been keeping backups in my personal folder so that I can simply replace the corrupted files whenever this issues happens, but it's getting very frustrating not finding the root-cause of the problem.

Again I would share the Macro script, but as I explained is not just one file that is giving errors but multiple files scattered across the different folders of the network. Also it is not an issue that arises only when a certain user manipulates the file, but it happens with multiple users. Sometimes the file works well for user X all day, but tomorrow user X will start having issues using that same file.

I'm not sure what additional details you may require but any light either one of you could shed in regards to this matter will be greatly appreciated.

Thanks!

Answer 1

Ask users, when it happens to either call you to do the following, or do it themselves:

1. Open the VBA editor (Alt + F11)
2. Click Tools >> References
3. See which Projects or Libraries at the top of the list say MISSING
4. Work out why they are MISSING on that particular machine

Note that incorrect versions of seemingly the same project or library will report as MISSING if they are incompatible (as with your DOMDocument60 update). Excel will normally update a reference to a more recent version of a Microsoft Office DLL, for example, but it will not revert to an earlier version. For this reason, you should ensure that any references to MS libraries are the earliest versions you can expect to be on any of the machines being used, in your "source" (master) file.