Adding library references programmatically

[Alan Beban]

I have a procedure with code that requires that there be a reference to
Microsoft Scripting Runtime. Can that reference be accomplished
programmatically at the beginning of the procedure? If so, what is the
syntax?

Thanks,
Alan Beban

 

[Frank Kabel]

Hi
you may use late binding. See:
http://www.xldynamic.com/source/xld.EarlyLate.html

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;160647
XL97: How to Programmatically Create a Reference

Add the scripting runtime manually, then you query it for particulars. You
might want to use the addfromGUID so you don't have to search for it.

This code Sub CCTester()
Dim refs As References
Dim rf As Reference
Set refs = ThisWorkbook.VBProject.References
For Each rf In refs
Debug.Print rf.Name, rf.GUID
Next
End Sub

showed
Scripting {420B2830-E718-11CF-893D-00A0C9054228}

and a variation showed:
Scripting C:\WINNT\system32\scrrun.dll

 

[Stephen Bullen]

Hi Alan,

Can that reference be accomplished
programmatically at the beginning of the procedure?

No, you't can set the reference at the start of the *procedure*. Excel
compiles workbooks module-by-module, so if you're dimming As
Dictionary, that would fail the compile before the code to add the
reference was run.

You could put the code to add the reference in a different module to
the code that uses the objects and make sure that's run first, but
doing that successfully means the VBProject will have to be unlocked
and the user will have to 'Trust access to visual basic project'.

Personally, I just ship my workbooks with the reference to the
scripting runtime set. I've yet to find a machine with Office installed
but not the scripting runtime.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Alan Beban]

Hi Alan,




No, you't can set the reference at the start of the *procedure*. Excel
compiles workbooks module-by-module, so if you're dimming As
Dictionary, that would fail the compile before the code to add the
reference was run.

You could put the code to add the reference in a different module to
the code that uses the objects and make sure that's run first, but
doing that successfully means the VBProject will have to be unlocked
and the user will have to 'Trust access to visual basic project'.

Personally, I just ship my workbooks with the reference to the
scripting runtime set. I've yet to find a machine with Office installed
but not the scripting runtime.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Thanks, Stephen, and to Tom Ogilvy and Frank Kabel as well, for
responding. I'll set the reference in the downloadable file at my web
site and hope that takes care of it. Since the error (if the reference
doesn't take) is a compile error, I don't see anything else to do
besides adding the comment at the beginning of the procedure indicating
that an effective library reference is required.

By the way, I take it that Frank's late binding comment is a red
herring; it certainly is for me, but that might be a statement about me
rather than about his comment

Thanks again,
Alan Beban

 

[Stephen Bullen]

Hi Alan,

I take it that Frank's late binding comment is a red
herring; it certainly is for me, but that might be a statement about me
rather than about his comment

No, you can always write your code using the reference to the Scripting
library, then change it to late-bound code prior to shipping, by removing
the reference and declaring the object as:

Dim dictItems As Object

Set dictItems = CreateObject("Scripting.Dictionary")

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk