XLA development best practices

[Maury Markowitz]

I work in a multi-user environment with a large amount of VBA code in
a monolithic XLA located on one of our fileservers. This is causing
all sorts of problems, and I'm looking for input on how to address
some of them.

To start with, having all the code in one large XLA makes maintenance
annoying, and the save and load times of the spreadsheets that use
them are way too high. I'd like to break this up into different parts,
the code is highly orthogonal so this should be easy to do.

However, since VBA can't easily call from one XLA to another, this
becomes extremely problematic. How have you all dealt with multi-XLA
code?

Another problem is that Excel seems to randomly remember or forget
XLA's in the AddIns list. This is especially common if the network
goes down, even for a second. Now Excel thinks the XLA is loaded, but
every attempt to call it fails with a different error each time. In
other cases Excel has the AddIn checked on, but the XLA is not
actually loaded. In still other cases the opposite occurs, the AddIn
shows that it's not loaded, but it is. In all of these cases there's
no simple solution to get things working about.

So how do you all deal with these sorts of issues? Do you copy the
XLA's to the user's machines? If so, does anyone have useful code for
finding the XLA folder? Is there some way of using XLA's reliably that
doesn't make the AddIn system see it. Any other tricks?

Maury

 

[Charles Williams]

I use an XLA automatically reversioning addin loader.
You can down load the basic working template code from
http://www.decisionmodels.com/downloads.htm

Most people will want to customise the code to suit their needs: the xla
password is dm

The basic idea is to ignore Excel's addin loader and just dynamically open
and close the XLAs from a Master XLA as though they were workbooks (XLAs are
treated as hidden members of the workbooks collection).
Note that using this method does not trigger AddIn_Load/Unload events,

One approach to calling other XLAs would be to do this always from the
master XLA using RUN (the master XLA knows the names etc of all the other
XLAs since its in charge of loading and unloading them).

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Maury Markowitz]

I use an XLA automatically reversioning addin loader.
You can down load the basic working template code fromhttp://www.decisionmodels.com/downloads.htm

I'm downloading now. Thanks for the tip!

Maury

 

[Maury Markowitz]

The need to use Run remains a problem however. I have a lot of
"utility" code (date handling, MAX/MIN functions, writing to logs,
etc.) that should _really_ be in it's own library. I could move it,
but if I do then the rest of the code becomes extremely cluttered, too
much so IMHO.

Anyone know any ways around this? Someone suggested using References,
but I cannot get this to work. I have also thought of moving it to a
VB ActiveX plugin, but that makes it extremely difficult to debug
common problems.

Maury