Some machines requiring reference prefix such as VBA. for functions

[brusk]

I have a workbook that I've been trying to make all the few changes to work with 32 and 64 bit Excel. Internally I have it working on both as long as the machines have .NET 3.5 and 4.8 loaded. I have a customer in Greece that seems to work on their 32bit machines but it is giving errors on their 64bit clients. When trying to compile it initially complained about a few FileSystemObject references which I changed from declaring a FileSystemObject to Scripting.FileSystemObject. Then I noticed it didn't like Date, which changing to VBA.Date resolved. Diving down the rabbit hole it seems to complain about every single reference of Date, UCase, UBound, LBound, Left, Right, Environ$ etc. With about 15k lines of code I surely don't want to be running these down one at a time. It appears all the same references are checked from the 32 to 64bit. Is there any easy way to remedy this? I even seen a solution for some of selecting UCMapi 1.0 Type Library but that didn't help either.

[CK76]

it didn't like Date, which changing to VBA.Date resolved

This is likely due to missing and/or mismatch of VBA Project library references. I've seen this happen couple of times when I wrote code in Excel 2016 and transferred file to Excel 2010 system.
Fix is to change/add reference(s) missing.

Check that the client doesn't have MS Office/Excel 16.0 selected (or missing), when they should have other version selected.

[brusk]

This is likely due to missing and/or mismatch of VBA Project library references. I've seen this happen couple of times when I wrote code in Excel 2016 and transferred file to Excel 2010 system.
Fix is to change/add reference(s) missing.

Check that the client doesn't have MS Office/Excel 16.0 selected (or missing), when they should have other version selected.

A mismatch I guess is possible. I tried un-selecting a few like the Microsoft Scripting Runtime and re-selecting but that didn't help. Here's a screen shot of the references selected. I can confirm what version of Office they are running. My 32bit excel is 16.0.11727.20222 and my 64bit is 16.0.12430.20198.

Attachment 664873

[CK76]

Sorry, can't see your attachment.

You may have to remote into their desktop and check if there are any missing references.

[brusk]

Sorry, can't see your attachment.

You may have to remote into their desktop and check if there are any missing references.

That was the screen shot I was trying to attach. All the references are still checked off.

[CK76]

Hmm, if there is no missing reference...

Only 2 other reasons that I can think of...
1. Ambiguity created by using Date as variable, subname etc causing issue.
2. MS Office installation was done as upgrade from previous version.

In your case it is more likely to be 2. This is caused when update didn't complete cleanly and there are leftover from previous version (never have 2 versions of Office on the machine if it can be helped).
First try Office Repair. If that doesn't work, uninstall and do clean install of MS Office.

[brusk]

Hmm, if there is no missing reference...

Only 2 other reasons that I can think of...
1. Ambiguity created by using Date as variable, subname etc causing issue.
2. MS Office installation was done as upgrade from previous version.

In your case it is more likely to be 2. This is caused when update didn't complete cleanly and there are leftover from previous version (never have 2 versions of Office on the machine if it can be helped).
First try Office Repair. If that doesn't work, uninstall and do clean install of MS Office.

Thanks I'll have them try that.

[rorya]

What did you mean by "it seems to complain about every single reference of Date, UCase, UBound, " in your first post? If you got a "can't find project or library" error, then it's a reference problem as CK76 said.

[brusk]

What did you mean by "it seems to complain about every single reference of Date, UCase, UBound, " in your first post? If you got a "can't find project or library" error, then it's a reference problem as CK76 said.

Yes that was the error. It fails to compile for most things but the pieces required in the reference are there. I even tried to uncheck Visual Basic For Applications and recheck but once it's used you cannot uncheck it. I believe the other one used for FileSystemObject is Microsoft Scripting Runtime which was also still selected. Since it works on the 32bit machines it makes sense that maybe there's something with the install of the 64bit clients.

[rorya]

You should probably look into late binding so that you don’t need to worry about reference compatibility.

[brusk]

You should probably look into late binding so that you don’t need to worry about reference compatibility.

Thanks for the info on that. I did actually have to use the late version in several pieces, didn't know there was an early vs late just saw that some environments seems to work better using late so I'll keep that in mind going forward.