Reference to XLA functions

I have developed a "function library" that involves call to a DLL i've written.

The xla is distributed trough a setup routine that also register the DLL.

Based on the XLA libary, I have developed som stadard XLS sheets that uses some of the functions in the XLA.

So far so good...

The XLS -XLA are sold separatly.

When the customer gets the XLS all the function references goes to where I, on my machine have installed the XLA. He then have to use a search-replace to get it to point where he has installed the xla.

My customer are not the most computer experienced people and I get a lot of calls on how to it, It has to be a better way.....

The XLA are installed, so somehow I want all this to go automaticly.

Any Ideas


Who is Participating?
mixaConnect With a Mentor Commented:
Well perove, I say you the right answer :)

XL supports autodetection of libraries only when they are declared in References of VBA project. So you have to declare additional functions in the source xls file and call functions from xla there. For example:


function testf()
end function


function tesfcall()
end function

test.xls must have in references testproj.xla. I suggest also to declare testproj.xla VBA project as VBAtestproj (instead of standard 'VBAProject'

After that if you change location of testproj.xla and reassign it in Service/Add-ins menu it will be used in the way you need.

In case if you're installing xla on the user's PC using XL VBA add-in manager (via registry, as described in documentation) you will have xla turned on after installation, and this way must work.

your users may use Excel menu command "Edit - Links" and change source link to the new location of xla. The VBA statement for it will be something like:

ActiveWorkbook.ChangeLink Name:="C:\TMP\test.xla", NewName:="C:\NewLocation\test.xla",         Type:=xlExcelLinks

After installing you may open special xls file that will open the data file without resolving link (see workboos.Open method, updatelinks parameter), then perform that statement for it.

In general, XL has special algorithm for changin links for the standard xla (they may be different on the PCs) but I don't know the exact way. May be someone has the better answer - let me know.

peroveAuthor Commented:
I'm rejceting you (for now-you will be rewarded) but I'll like to get som more info on the subj.

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Do not understand: Do you mean Tools/Add-ins does not allow to register your xla ?

XL97: How to Create an Add-in File in Microsoft Excel 97

Consider packing all the code into the DLL i.e. skip the xla and use the xls as front end. Should it be necessary that the calls can be inserted easily by your _users_ the best option is to buy the SDK (Software Developer Kit) in order to make DLL's which appear in the function dialog, if wanted with help.

VBA: Programming Resources for Visual Basic for Applications
Hi perove,

In addition more on the subject :O)

Hi perove

It sounds like what you are after is the "Auto_Add" macro.

all you need to do would be place a procedure in your add-inn called Auto_Add.

sub Auto_Add()
<Your code>
End sub

Then whenever your add-inn is istalled the procedure will run.

Let me know if I'm on the right track

peroveAuthor Commented:
Think I explain myself badly..sorry,

The xla I've bulid is a "function library" - FUNC_LIB.XLA that collect information from a accounting system. Ex. I have the function INVOICE_SUM(inv_number) that gives back the sum of a certian invoice.

I also sell a sheet: KEY_FIGURES.XLS that is a pre-defined sheet, that uses (some of ) the functions in FUNC_LIB.XLA

The problem is when a user that uses the XLS have the XLA installed in a different place then where the XLA was installed when the XLS was build. The functions are -as you know- stored with the full path ie. c:\mylib\func_lib.xla!invioce_sum(23456)

This is the problem, I cannot trust my users to  manually do a search-replace to fix it, I want the link to automatic be replaced/fixed to where the FUNC_LIB.XLA are installed.

mixa is on the right track, but i'll need som more detalis,; how to find the path to where the xla is on the current installation, how to test that the links are "true"/valid and finally how to change them.
With this info on place I can add som code to the auto_open and do this elegant and in-visible for the user.

<<Do not understand: Do you mean Tools/Add-ins does not allow to register your xla ? >>

No, the xla is installed and work OK, but if I take a XLS sheet made on a different computer the "pointer" to the XLA may not be valid.

Skip the XLA and just use the DLL directly - to time consuming, but I'll keep it in muind to next version.

Hope my problem is clearer now, any Q -> let me know


Hi perove

Is this along the right lines

Sub Auto_Add()
ActiveWorkbook.ChangeLink Name:="C:\MyDocuments/BILLS.xla", NewName:= ActiveWorkbook.FullName, Type:=xlExcelLinks
End Sub

Or this:

Maintaining References to add-inn files

To access subroutines and functions in another add-in file. You must establish a reference to the add-in by choosing the references command from the tools menu in the VBE and then locating the add-in file using the browse button. Certain problems arise when you save a VBA application with a reference to a add-inn file-Namely, that Excel hard codes the path of the add-inn file when the reference is established and the file that contains the reference is saved. When you open an Excel file that has saved add-in in reference (eg the working folder). If Excel finds the reference add-inn in either of these locations,Excel successfuly re-establishes the reference. Therefore it is imperative the relevant add-in file must be saved in the same folder as the Excel file that contains the reference-otherwise ,Excel fails to find the add-in file, and the reference is not established on the users machine.

An alternative to establishing references to add-in is to use the Run Method of the Application object to call routines in the add-in. The Run Method will run the requested routine without a reference assumming the file containing the routine is in memory. Using this approach, you could open the add-in file through code from whatever location it is in, then call the Run Method. This Method takes the name of the routine or function to run as a string argument, and then takes any arguments to the routine being called as additional optional string argumets. For example:

Application.Run "MyRoutine", "Argument1", "Argument2"

The drawbacks of this approach are the arguments must be converted to text and that pass by reference arguments cannot return values(since arguments are converted to pass by value automatically).

I hope the above is of some use to you.

peroveAuthor Commented:
Thanks for the input. Nothing new for me actually, I've discoverd that the functions run approx 10% faster when the xla is installed locally. So It is imperative for me that is is the matter

<<Therefore it is imperative the relevant add-in file must be saved in the same folder as the Excel file that contains the reference-otherwise ,Excel fails to find the add-in file, and the reference is not established on the users machine.>>

I cannot presuppose that this is the situation the user want to share the XLS , this is the problem in a nutshell. What I had in mind is that on the open of the xls i need to.

1.Check if the link is valid

if no

-find the link to my xla
-replace all the function with the correct link

if yes

open the sheet

Thanks for your input, it is most welcome, I'm more a VB delveloper and deploying VBA and XL is a bit of a new world to me.

regards from perove in norway

peroveAuthor Commented:
Think I will solve it with your way if I can get the path to the xla currently installed. So a function.

GetLinkPath(libName as string) as string
'returns full path to the library libName

end function

would do the trick for me

GetLinkPath("solver add-In")

should return
C:\Program Files\Microsoft Office\Office\Library\Solver\sover.xla

Then I could use your  excel(ent)example like this:

Sub Auto_Add()
ActiveWorkbook.ChangeLink Name:="C:\MyDocuments/BILLS.xla", NewName:= GetLinkPath("solver add-In")
, Type:=xlExcelLinks
End Sub

Take the point, we are really close here..


peroveAuthor Commented:
The KEY_FIGURES.XLS (that uses function from FUNC_LIB.XLA) use them not in VB but they are maked my Choosing the Fx button and then user defined functions wher I can locate my function invioce_sum()

So there are no VBA involved in the XLS just plain "cell referencing to functions"

I can still use your suggestion, please verify

Write a VB installation program!

There ain't no easy way, as you can see from the comments your getting.

I find it best to just write your installation and make the things that need to happen.

All Courses

From novice to tech pro — start learning today.