Excel 97: Add-In (xla) Path

Situation: I developed an own Excel 97 add-in (xla). The problem I have arises from the the fact that the xlstart directory at work is on a network drive. So, if somebody take his/her workbook home and update it and takes it back to work, all calls to my functions will have the full home xlstart path, and vice-versa. In a simple to medium workbook a search and replace for each worksheet is the (tedious) cure, for complex workbooks the calculation can be derailed to a point where even a Ctrl+Alt+F9 does not help anymore, then all the calculation cells have to be refreshed individually by 'F2+Enter'. The obvious solution is to set up a alternate startup directory but this gets a) overwriten by our belowed admins b) people are not able to keep them in sync. Arghhh...

Thefore: Is there a solution except to compile the stuff in a dll, because there the problem does not arise ?

cri, I dont want to misunderstand this, but can you accomplish what you want to do by storing the xla in a specific alternate startup folser, then resetting the alternate setup folder on opening the workbook

cri,
I don't know if I've understood your problem properly. I have VBA macros in a number of workbooks. Sometimes, if a server is unavailable, workbooks may have to run from a different server. In many of the macros I have path names defined, but I always have a range in each workbook defined as SERVER_NAME. Then a string variable for the server name e.g Public ServerName as string.
Then when filling variables later: -
ServerName = _
Sheets("Sheet2").Range"SERVER_NAME").Text
If users take workbooks home, you could use a macro to check for existence of the server, and then substitute C: for SERVER_NAME. When running on the server, uses value in named range. I don't know if you can use this method in the function. The advantage of course if you can use it, is that if your "beloved" admin peeps do change servers etc. you only need to edit 1 range in each workbook (you can do this with another macro if you keep the sheet name and range name consistent.
Hope this helps.
Geoff (macbone2)

macbone2, thank you for tackling this one. I do not have any hardcoded paths in my functions. Excel/Microsoft seems to handle functions in a xla like a Link. And the problem is not limited to server drives, all it (seems to) take is 2 Excel 97 configurations having a different xlstart directory.

cri, I dont want to misunderstand this, but can you accomplish what you want to do by storing the xla in a specific alternate startup folser, then resetting the alternate setup folder on opening the workbook at work.

macbone2, this solution requires the _workbook_ having the path setting macro. However, my xla contains general purpose functions (i.e. what Microsoft should have included in version 3 or 4...). Either I try to explain to the users how to include this code or I have to make an macro which does this, either automatically or manually. Whereas this certainly is an option, I will not pursue it as it complicates the stuff beyond 'in-house' practicability.

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…

This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses.
Navigate to the “Start Mail Merge” button in the Mailings tab:
Follow the step-by-step process until asked to find the address doc…