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, Dec 17 '19 at 09: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 http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/ — Pᴇʜ, Dec 17 '19 at 09: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, Dec 17 '19 at 09:39
For a drop down calendar, so the user can pick a date and don't write it manually — Gerard Ramon, Dec 17 '19 at 10:26
You might be interested in [How can I create a calendar input in VBA Excel?](https://stackoverflow.com/questions/54650417/how-can-i-create-a-calendar-input-in-vba-excel) which comes with pure VBA. — Pᴇʜ, Dec 17 '19 at 10:36
Yes, i would. If I dont need to use any references it would be much simpler. Thanks! — Gerard Ramon, Dec 17 '19 at 10:42

Storax · Answer 1 · 2019-12-17T09:48:50.500

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 '19 at 09:48

answered Dec 17 '19 at 09:24

Storax

11,158
3
16
33

1

Before you add the GUID, remove the broken references as shown [Here](https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically/9880276#9880276) :) – Siddharth Rout Dec 17 '19 at 09:28
1

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. – Storax Dec 17 '19 at 09:29
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? – Gerard Ramon Dec 17 '19 at 09:35
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](http://www.vbaexpress.com/kb/getarticle.php?kb_id=278) is code how to do it. – Storax Dec 17 '19 at 09:39

score 0 · Answer 2 · answered Dec 17 '19 at 09:14

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.

VBA Excel - Add references programmatically

2 Answers2