Automatically set VBA Reference to Excel add-in

[Curt Johnson]

I've written an Excel add-in using VC++ 6.0. This .xll also provides VBA
functions and subs that return a HRESULT. I've included the .tlb in the
resource fork of the .xll. My employer wants to set up the .xll so all a
customer need to do in open the add-in (.xll) and its functions are
automatically available to VBA code we provide on a spreadsheet without path
information. Is there a way to set a VBA reference to the add-in?

Thanks,

Curt

 

[Simon Murphy]

Curt
If your xll provides a COM interface and gets registered in the registry
for COM then your users can set a ref to it in the VBA IDE and be
sorted. Mixng COM and CAPI can be troublesome though. Not sure if thats
what you have in mind by including the type lib?

normally though xll stuff is a wrapper round native code dll type stuff.
To access these from VBA you normally use Declares, but you could also
use Application.Run, once they are registered with Excel.

Anything you xlfRegister in your xll will be available via
application.run in VBA, as well as in the sheet depending how you
registered it.

The XLM CALL() function may also be useful to you.

hth
Cheers
Simon
Blog: www.smurfonspreadsheets.net

UK Excel user conf 29 Nov - 1 Dec Cambridge
http://www.exceluserconference.com/UKEUC.html