The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Excel VBA References


  I've got a COM DLL which has to be installed on customers' machines and used in Excel VBA programs. Currently the users have to manually add a reference to a COM library via the Tools|References menu for each new project.

  Is there a way to automate this process? Where does Excel store the list of default references and is there a way to modify it programmatically? How can I have Excel automatically add a reference to a COM library for all new VBA projects?

Thank you!
Friday, November 09, 2007
Use Regmon to find out yourself.
Friday, November 09, 2007
You could change the default workbook template to include a ref to your dll.
(call it book.xlt and store in xlstart.)
but that would add it for every single wb they create.
You could do an VBAIDE add-in so devs can choose which wbs get the ref.
In code you can use .AddFromGuid or .AddFromFile
Simon Send private email
Saturday, November 10, 2007
Thanks Simon!
Monday, November 12, 2007
You could create the reference programmatically within your VBA by using the 'CreateObject' function.  Include it in the 'WorkBook_Open' Sub.

From Excel Help:

  Dim ExcelSheet As Object
  Set ExcelSheet = CreateObject("Excel.Sheet")
Kevin Doyle Send private email
Friday, November 23, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz