Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLA development best practices
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLA development best practices
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLA development best practices
On Jul 24, 11:41*am, "Charles Williams"
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XLA development best practices
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Best Practices (Counter) | Excel Discussion (Misc queries) | |||
Comparing Spreadsheets - best practices | Excel Discussion (Misc queries) | |||
Best Practices Question | Excel Programming | |||
Task pane best practices | Excel Programming | |||
best practices question | Excel Programming |