Simpler Set Reference Dialog (VBA Office 2K)

[Gary Frieder]

Inspired by the code in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=vb&Number=100564&page=0 &view=expanded&sb=5&o=0&vc=1#Post100564>this post</A> by Andrew Cronnolly, here's (see attached Word doc) an implementation that provides a simplified interface for setting a reference to some common libraries (Access, Excel, PowerPoint, Word, VB Extensibility, and Scripting Runtime).
- not necessarily at the top of anyone's wish list; but Andrew's code just seems too cool to not use it for something.

This could be taken further: have the userform automatically display all currently set references, as well as a number of frequently-used ones. Via checkboxes you could permit the setting/unsetting of multiple references in one operation. (maybe to do some snowy weekend....)

Also, currently this has to be run from the application interface - it would be better if this could be run from the VBE instead - anyone know how?

Gary

[Gary Frieder]

Hi Andrew,

I like it.
One good tweak deserves another - and those checkboxes are crying out for letting you remove as well as set references via this dialog - so attached is version 3 which allows you to do both.

Haven't looked up the Stephen Bullen reference but will do so next....

Gary

[Andrew Cronnolly]

Gary,

Nice tweak. Hope you don't mind one more. I have tried to make it less application specific by making some minor changes. I have replaced ThisDocument.VBProject.References with Application.VBE.ActiveVBProject.References which should run in all flavours of VBA (I tried it in Word and Excel without problem). I also changed If aRef.GUID = sGUID And aRef.Name <> "Word" to If aRef.GUID = sGUID And aRef.BuiltIn = False which seems to do the same job. The BuiltIn property of a Reference is true if that particular reference is an integral part of the active application and cannot be removed. See the attached document. The form can be exported and imported into other Office applications and should run without change.

I tested it using Office 97, and everything works fine except setting a reference to Word. I have no idea why it fails. It does however work in Office 97 if it co-exists with Office 2000, so I suspect that there must be a problem with Word 97 entries in the registry. I did a search at the MS KB and <A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q249626>Q249626</A> is the nearest I could come up with, though it applies to Word 2000. Maybe somebody else can up with an explanation.

Andrew

[Andrew Cronnolly]

Gary,

This is a good idea, have the frequent libraries available without havg to scroll through dozens of references that you will never set. One useful addition might be DS: OLE File Properties (dsofile.dll - GUID = {93769740-DE48-11D2-B7C8-A62255602516} ).

I have never done much in the way of VBE add-ins or customisations, so I don't really know of an easy way to launch it from within the VBE. Stephen Bullen has a VBEOnKey (XL workbook) utility available for download, which you might be able to adapt.

I adjusted the code slightly so that it is all contained in the userform, and by using the the tag property (to store the GUID) of the controls, was able to use a loop instead of using select case. File attached.

Andrew
Edited by Andrew Cronnolly Mar 13 2004 to update link to Business Modelling Solutions (Stephen Bullen) website