Custom Functions and Excel Add-in.

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I am using Excel 2002 (SR-2), but the problem occurs in 97 as well.

I have created an Add-in (xla file) that contains numerous functions.
Some of these functions are used directly by a number of workbooks to
insert a value in a cell. For example, Cell A1 has the following
formula '=getProduct()', where the getProduct() is a function in my
add-in.

My problem arises from the fact that my add-in and workbooks are
distributed within the company and our clients. Why is it a problem,
well, unless the add-in is in the same directory as the workbooks, or
located in the same directory on every PC it is on, the above mentioned
formula causes a dialog with the following error to occur:

This workbook contains links to other data sources.

The dialog gives you options, but the I don't want the clients to see
it. Now, I can stop the dialog from being displayed, but the cells
that have '=getProduct()' in them don't function correctly.

I do have a workaround for the problem ... in the ActivateWorkbook
method of the workbook, I have written code to determine the full path
to my add-in, and to change the links to use this path. However, it
just seems to be overkill for my problem.


Does anybody have any ideas about what else I could do ...


Many Thanks :)
 
Alex said:
I am using Excel 2002 (SR-2), but the problem occurs in 97 as well.

I have created an Add-in (xla file) that contains numerous functions.
Some of these functions are used directly by a number of workbooks to
insert a value in a cell. For example, Cell A1 has the following
formula '=getProduct()', where the getProduct() is a function in my
add-in.

My problem arises from the fact that my add-in and workbooks are
distributed within the company and our clients. Why is it a problem,
well, unless the add-in is in the same directory as the workbooks, or
located in the same directory on every PC it is on, the above mentioned
formula causes a dialog with the following error to occur:

This workbook contains links to other data sources.

The dialog gives you options, but the I don't want the clients to see
it. Now, I can stop the dialog from being displayed, but the cells
that have '=getProduct()' in them don't function correctly.

I do have a workaround for the problem ... in the ActivateWorkbook
method of the workbook, I have written code to determine the full path
to my add-in, and to change the links to use this path. However, it
just seems to be overkill for my problem.


Does anybody have any ideas about what else I could do ...
Click to expand...

I think you probably have the best work-around. An alternative is to put the
functions which are in the xla into each of your workbooks.
 
If I were sharing this with clients, I think I'd put the functions into the
workbook. (but it depends on lots of things...)

What happens if you have your users/clients open your addin first, then your
workbook?
 
Dave said:
*If I were sharing this with clients, I think I'd put the functions
into the
workbook. (but it depends on lots of things...)
*
Click to expand...

Why aren't these functions in the workbook ??? There are currently 85
workbooks which use these functions, and there could be more ... so, it
is not a good idea.

Dave said:
*What happens if you have your users/clients open your addin first,
then your
workbook?
*
Click to expand...

Like you, I thought this would solve the problem ... but it doesn't,
why, I don't know.
 
Alex said:
Why aren't these functions in the workbook ??? There are currently 85
workbooks which use these functions, and there could be more ... so, it
is not a good idea.



Like you, I thought this would solve the problem ... but it doesn't,
why, I don't know.
Click to expand...

Alex

I presume that the problem with your present work-around is that it takes
time to scan the user's hard disk to locate the xla file, so that you can
update the links? That's why you say it is overkill?

I always assumed that the xla file had to be in xlstart, but I gather it
works fine as long as it is in the same directory as the excel spreadsheet
that you have just opened. In that case, may I suggest that you change your
work-around which occurs when you activate the workbook to check whether the
xla file is in the right place and, if not, copy it to the user's directory
which contained the spreadsheet that has just been opened. That way you only
have to trawl through the hard disk once, not every time the worksheets are
opened.

I am sorry that I am a beginner with these things, so please forgive me if I
am misunderstanding your query or giving a silly response.

Geoff
 
Back
Top