Excel Macro for setting Reference VBA Project

[bennyys]

Hi,
At some point some vb code will work fine if related Reference VBA Project was already tick on the list.
So far I did it manually by selecting Tools-References and Tick on which one I need (on VBA code window)

My question is: Is there any macro which can replace my manual work for doing the same task?
for example I want to switch on the next three Reference VBA:
1. OLE Automation
2. Microsoft Office 15.0 Object Library
3. Microsoft HTML Object Library

It is something like , go and check if the XXXXX Reference VBA is not available the make it available, if already on then perform the next task.
just for additional info, I am using excel 2013.

Regards,
Benny

[TMS]

Short answer: no, probably not.

Use Late Binding instead of Early Binding. So, Dim your variables as Object rather than specific elements, like Outlook or Word.

https://support.microsoft.com/en-us/...-in-automation

[bennyys]

Hi Trevor,
Thank you for given link.

I try to search and found the following stuff.

1. To get a list of all available references of a excel VBA project Regardless on whether they are selected or not.
Reference : https://social.msdn.microsoft.com/Fo...t?forum=isvvba
and
https://www.experts-exchange.com/que...A-project.html

Please Login or Register  to view this content.

The result is as shown in the next picture
Reference_list.JPG


2. To get the list of all active (ticked on) reference Use the following code (Thank you for Robert, Trebor76, who sent me the code written by John Walkenbach).
I modify a bit for print the outcome on range A1.

Please Login or Register  to view this content.

3. To set a reference programmatically use the following code (reference: https://social.msdn.microsoft.com/Fo...forum=exceldev)

Please Login or Register  to view this content.

4. As a test I did the following.
-ticked off almost all references, just left the one in use
Before_Running_Macro.JPG

-running the following macro

Please Login or Register  to view this content.

-See the change on on the list
After_Running_Macro.JPG


Regards,
Benny

[TMS]

Thank you for the follow up. Clearly you have learnt a lot in a very short time! And I have benefited from your research.

I think you would need to test it in different environments. Given that you may develop an application in, say, Excel 2007, and then want to run it in Excel 2010 and/or Excel 2013, you may want/need to remove redundant references and add the relevant ones.

I still suspect that it would be simpler to use Late Binding

Thanks again