VBA Excel - Add references programmatically

Question

I built a excel macro using the "Microsoft Outlook 15.0 Object Library" reference, but when I send the file to other people they get this error: "Can’t find project or library".

This file will be used by a lot of people, so I have to add it from VBA Code.

I'm using this code that runs when you open the excel file, which is returning the following error: "Error in loading DLL"

Private Sub Workbook_Open()
       Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (x86)\Microsoft Office\Office15\MSOUTL.OLB\"
End Sub

Do you have any idea why this is happening?

Thanks

Because you are early binding. Use late binding. It is not necessary that you and the other person is using the same version of MS Office. — Siddharth Rout, Commented Dec 17, 2019 at 9:08
Probably because the other person uses another version of Office (not 15.0). If this is the case then use Late Binding (works without references) instead of Early Binding (uses references). See excelmatters.com/2013/09/23/… — Pᴇʜ, Commented Dec 17, 2019 at 9:08
[From the comment below my deleted answer]... Would you be distributing mscomct2.ocx with the excel file? If yes then you will have to get an elevated registration of mscomct2.ocx. This ocx is not native to VBA environment. It is a VB6 control and should be avoided in such a case. — Siddharth Rout, Commented Dec 17, 2019 at 9:39
You might be interested in How can I create a calendar input in VBA Excel? which comes with pure VBA. — Pᴇʜ, Commented Dec 17, 2019 at 10:36

Storax · Accepted Answer · 2019-12-17 09:48:50Z

2

If you still want to keep the early binding you could use the GUID in order to add the libray, i.e.

Sub AddOL_Ref()

Application.VBE.ActiveVBProject.REFERENCES. _
    AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 1, 0

End Sub

Advantage of using the GUID is that it is constant and does not change with the version of the program.

Here one finds an extened discussion on dis/advantages of early resp. late binding.

edited Dec 17, 2019 at 9:48

answered Dec 17, 2019 at 9:24

Storax

12.7k33 gold badges2121 silver badges3636 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Siddharth Rout Over a year ago

Before you add the GUID, remove the broken references as shown Here :)

Storax Over a year ago

Right, and one also need to check if the reference has already been added regardless if you use AddFromGUID or AddFromfile. Broken references are cause for headaches anyway.

Gerard Ramon Over a year ago

Thanks for the response, it doesn't return errors this way, but I need to add another reference, from a file I downloaded (mscomct2.ocx), so I don't know if I can use AddFromGuid for this one. What could I do?

Storax Over a year ago

Each reference has a property GUID which you can determine with VBA, for example if oRef is the reference then oRef.GUID will give you the GUID. Here is code how to do it.

Harshad Nannaware · Accepted Answer · 2019-12-17 09:14:28Z

0

You will have to remove the references & use late binding

or you can also try this. Send your code to one of the user having 2010MS.

Goto VBA > Tools > References

Check for any missing references. You may have Microsoft Outlook 15.0 Object Library showing as missing. Un-check this, browse down and select Microsoft Outlook 14.0 Object Library.

answered Dec 17, 2019 at 9:14

Harshad Nannaware

1199 bronze badges

Collectives™ on Stack Overflow

VBA Excel - Add references programmatically

2 Answers 2

4 Comments

Comments

Your Answer

Post as a guest

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Linked

Related