VBE references and versions handling

[Indigo744]

Hello @joyfullservice

Looking at a recent commit 620999300bbf5884dc3d4b337730ac1d32393639, I was wondering if we needed to do the same for other references, like Word, Powerpoint, Outlook and so on...

For instance, Word ref GUID is 00020905-0000-0000-C000-000000000046, and versions are 8.7 (2016), 8.6 (2013) and 8.5 (2010).

But I feel like it will be hard to scale as we will need to add all GUID and matching Office version...

And after scouring the web, I stumbled upon this little information that may just resolve the issue once and for all:

The References.AddFromGuid method requires three parameters: [...] By using 0 as major and 0 as minor VBA will add the latest available reference.

Source: https://www.excelcise.org/add-or-remove-object-library-reference-via-vba/

I mean, what!? It's exactly what we need!

This behavior is, as often with Microsoft, not documented but my (limited to Office 2016) testing showed it works reliably for any references I could set.

Could you try it on Office 2010?

Thank you 😸

[joyfullservice]

WOW! That is awesome!! Nice find! 👍 👍 👍 That will do a great job of solving that issue!

It works perfectly in Access 2010 as well, so I think I will go ahead and simplify the reference handling to just use the GUIDs. With this approach, I don't see any reason to keep the version numbers in version control, so I think I will just drop those as well. That way you will get the same references.json export file content regardless of which version you export from.

If we have a case in the future where someone has a legitimate need to preserve the version number, such as when using multiple versions of Office, and need to target an older version, we could add it back as an option.

I also added a pull request to add this usage and an example to the Microsoft docs, so we will see what happens with that...

[Indigo744]

Welp, I guess we were too enthusiast and too quick, I discovered some edge cases sadly...

For instance, "Rebuilding from source" does not work for this very ACCDA: it adds Microsoft VBScript Regular Expression 1.0 instead of Microsoft VBScript Regular Expression 5.5 and the code won't compile because of early bindings to VBScript_RegExp_55.RegExp...

Before:

After:

So I guess this leaves us with the following approach:

1. Keep the version in JSON
2. Try to add the specific version
3. If it fails, try to add the version 0.0

What do you think?

[joyfullservice]

Yes, that sounds like a reasonable approach to me.

[joyfullservice]

I just saw a notification today that my proposed update to the Microsoft docs was merged, so hopefully that will be helpful for other developers going forward. 😄