VBA excel code does not work in Excel 2016

Question

I have an excel file (.xlsm) with extensive calculations running through VBA coding. The file works fine in Excel 2007 and Excel 2013. Last week I installed Excel 2016 (MSO 16.0.7030.1021 32 bit) on my new Surface Pro running Windows 10 Pro Ver 1607 Build 14393.51.

Now when I open the file in this Excel 2016, I get Compile error saying "can't find project or library". I try to go past this by deleting that part of code on which get this flag and reopen the file after saving, but the calculations still do not work ( do not get any error flag).  I only get #Value in all cells using VBA code.  for deleted part of the code it is understandable but all the rest of the code should at least work . I have checked the Reference Libraries and none is missing.

The original  excel file still works fine in Excel 2007 and 2013 without any issues.

I have checked all security, macro and other settings on new Excel 2016.

I need urgent help in resolving this issue please.

Thanks much.

Answer 1

Bill,

After a lot of research, I finally know where the problem really lies. The issue seems to be with the compatibility of the 3rd party DLL with the Windows 10 Versions.

I zeroed on to the issue by testing my program (including same DLL) on couple of different versions of Windows as well as Excel versions. My program works on Windows 7, 8, 8.1, as well as 10 (home) except with Windows 10 Pro. It runs fine on Windows 10 Home edition with Excel 2016 as well as Excel 2013. But it does not work on Windows 10 Pro with any Excel. So, the real issue is compatibility of DLL with 10 Pro.

Now that I know the issue, it can e resolved as I got in touch with the guys who wrote the DLL for me and they have agreed to help resolve the issue. They sent me a test DLL with just one function which has worked fine and now they are redoing the full DLL to be compatible with Windows 10 Pro.

Meanwhile, I wish to thanks yo for all the help you had provided.

Thanks. Regards.

Answer 2

Well, that's progress.

At least we now have our sights on the DLL as the problem area.

Not an area I am an expert on as I have never built a custom DLL and called it from VBA.

So forgive me if either of these ideas is obviously rubbish:

• Does it help if you create a reference to the DLL in the VB editor?
• Does it help if you run Regserver <your dll filename>

Answer 3

In the VB editor, Tools > References

Note which referenced file is MISSING

Copy/install the relevant file on your new computer if it is not present.

If it is in a different location than it was previously, uncheck the old MISSING reference and browse to the new location and add a reference to the file, making sure there is a checkmark against the new reference.

Answer 4

Bill,

Thanks for your reply. This was the first thing I checked when my codes were not running. As I mentioned earlier in my message (last line of 2nd para), I have already checked the Reference Libraries and none is missing.

After my message above i tried to check if my other files with User Defined Functions are working or not, I noticed none of the UDF written by me are running in any of the files.

One additional point I noticed that Windows had changed the List Separator from "," to ";" which seemed to be one of the issues. I changed the Windows Region>Format setting for List Separator from semi-colon to Coma. However, this did not resolve the code issue.

My problem is still unresolved. Appreciate urgent help on this.

Thanks much.

Answer 5

Sorry.

Should have read your post more carefully!

I don't know what will be the cause of your problem (as generally people don't encounter such problems moving from 2013 to 2016) so just throwing out some ideas:

Do you have any ActiveX controls other than the standard ones available for userforms?

Are your other regional settings different from those on the earlier machine?

Try making them all the same as before.

Does Debug > Compile go through OK?

Does Application.CalculateFullRebuild fix it?

Good luck!