Using function from vba add-ins: looking for a solution

[Bura Tino]

Hi,

This is the same issue as posted before, but this time I will start in a
different place.

There seems to be fundamental problems with using functions from a vba
project. If user A and user B both have the same vba add-in installed (but
in different paths), and user A builds a worksheet which uses a function
from the add-in, he cannot send this worksheet to user B. This is because
Excel will look for the add-in to be installed in the same place and won't
find it there. This clearly defies logic, but I won't get into that.

Charles Williams proposed two solutions:

- always install the addin in the same place (use an install package to make
sure)
- change links in the workbook (either manually or make your addin check
each workbook that is opened for links to itself and change them to itself)

Solution 1 is unacceptable because it is good practice to put add-ins on the
a shared drive so they can be upgraded invisibly to the users. At the same
time, to me as the developer, the shared drive is not always available.
Solution 2 is acceptable, but c'mon!

A third solution that I will pursue if I get no suggestions is to rewrite
the add-in in C++, since C++ add-ins seem to be free of this ridiculous
problem.

Does anyone have any suggestions?

Bura

PS: Perhaps I could go with the first solution proposed by Charles if (in
abcense of a network) I could fake having an F: drive by mounting one of the
local directories. Does anyone one if this is possible? Sorry for the OT.

 

[Rob van Gelder]

I too have had this issue with Add-Ins.
Given this obstacle, I personally find UDF within XLA a waste of time.

What I've resorted to is UDF within a COM AddIn (Automation AddIn) which is
available in XL2002+
You don't have Links with this addin, Edit | Links is ghosted out.
Even Automation Addins have problems, but they are minor in comparison.

The best option I have seen is still XLL for UDF. This technology has been
available since very early versions of Excel but is not available to be
written in VB (at least, no way I know of).

 

[Jens Thiel]

The best option I have seen is still XLL for UDF. This technology has been
available since very early versions of Excel but is not available to be
written in VB (at least, no way I know of).

If VB is your language of choice, try VB.NET + ManagedXLL !


Jens.

 

[Jens Thiel]

Solution 1 is unacceptable because it is good practice to put add-ins on the
a shared drive so they can be upgraded invisibly to the users.

Have you tried updating the add-in while it is opened by a user..?

A third solution that I will pursue if I get no suggestions is to rewrite
the add-in in C++, since C++ add-ins seem to be free of this ridiculous
problem.

Does anyone have any suggestions?

I can give you an evaluation copy of ManagedXLL if you like to try that ;-)

I could fake having an F: drive by mounting one of the
local directories. Does anyone one if this is possible? Sorry for the OT.

use "SUBST F: C:\PATH\TO\ADDIN" but be warned: I remember we had problems
with that


Jens.

 

[Bura Tino]

Have you tried updating the add-in while it is opened by a user..?

Actually yes, although it sounds bad. It's seems ok - they notice changes
when they restart excel.

I can give you an evaluation copy of ManagedXLL if you like to try that ;-)

Don't know what ManagedXLL is. I will look into that.

OT.

use "SUBST F: C:\PATH\TO\ADDIN" but be warned: I remember we had problems
with that

What kinds of problems?

I've also discovered that you can mount it as \\127.0.0.1\SHARED_DIR

 

[Jerry W. Lewis]

Like it or not, including the path is the nature of the way Excel embeds
the links to VBA add-ins. It gets worse: Excel uses the drive letter
path, VBA uses the UNC path. In my experience too, links to .xll files
are not plagued by this.

Why is the shared drive sometimes unavailable to you? If you mean that
you want to work from a PC that is not connected to the network, you can
always use the DOS SUBST command to mimic the .xla's path on a virtual
drive. That would solve the problem for functions called from a worksheet.

Jerry