Adding reference in code

[Andrew O'Brien]

Hello all,
Is there a way to programatically add a reference to a dll or .xla file from
an Excel workbook? (i.e. Tools>References from the VB Editor) .

Thanks,
Andrew

 

[AA]

To add an XLA file:

AddIns("ODBC Add-in").Installed = True

ODBC Add-in is added; setting to False removes it.

If a Win32 DLL, then something like:

Declare Function CopyFile Lib "kernel32" Alias "CopyFileA"
(ByVal lpExistingFileName As String, ByVal lpNewFileName
As String, ByVal bFailIfExists As Long) As Long

The DLL is KERNEL32.DLL, the function is CopyFile (the
arguments must be known).

If an ActiveX DLL (or EXE)

Set Myobj=CreateObject(name.class)

e.g.

Set objWrd=CreateObject("Word.Application")
Set objAPL=CreateObject("APLW.WSEngine")

 

[Andrew O'Brien]

Thanks for the prompt reply. I'm looking to programatically add a VB
Reference, not an Add-In. Is it possible? In the second example you
provided, you are demonstrating late-binding. I'm looking for a way to add
a reference to an .xla file so that the public functions in that .xla file
are available for use in the current workbook.

Thanks,
Andrew

 

[Chip Pearson]

Andrew,

Try something like the following:

ThisWorkbook.VBProject.References.AddFromFile _
Filename:=Application.AddIns("addin_name").FullName


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Andrew O'Brien]

Thank you so much, Chip. This is exactly what I was looking for.

 

[AA]

If you have added an addin, either with

AddIns("ODBC Add-in").Installed = True

or with Tools|AddIns etc,

the functions in that XLA become available. You can use
them in the worksheet

=fnname(arg1,,arg2)

or in macros

ActiveCell.FormulaR1C1 = "=fname(arg1,,arg2)"

IF you need the value in the macro, read it from the
activecell or other cell where you have written it.

 

[Andrew O'Brien]

You're right. I'm sorry, I don't think I was clear about what I was looking
for. I needed to make the external formulas available on the document
level...not the Excel application level. For example, if I create workbook
wb1.xls, I want to have a reference saved with the workbook to lib1.xla so
that I don't have to worry about whether or not another user has the add-in
installed. Thanks again.

 

[Chip Pearson]

Andrew,

Try something like

ThisWorkbook.VBProject.References.AddFromFile _
Filename:="C:\filename.xla"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)