XLA design

I have been revisiting the design of some XLAs I wrote a while ago.

What I have tended to do is populate the worksheet part of the XLA and at the last minute copy the worksheets out to a new workbook.

I actually think it may be better to create the new workbook early on, populate its worksheets directly and at the last minute unhide it.

Which approach do you tend to follow?

The reason I think the second may be better is it stops the addin keep getting written to and possibly ending up in a corrupt state should things go wrong. Not that any add-in I ever wrote crashed Excel, No Sir-ee Sir! ;-)  ;-).

It also occurs to me that the second approach will make the XLA easier to migrate to VB6 + .xlt, which is something I have begun to do.

Do you have a general preference? Or a better way?



8 Responses to “XLA design”

  1. dermot Says:

    I would definitely leave the XLA alone and write to a new workbook, as you don’t contaminate the XLA if something goes wrong

    I wouldn’t hide the new workbook either. If something goes wrong, the user has no way of seeing what it is, or of closing the hidden workbook. I think it’s better to leave everything visible, but turn off screen updating.

  2. Jon Peltier Says:

    I don’t like to do anything that changes the add-in file. If that’s not constant, then it makes debugging harder, especially when a remote client has some issue. If I want to save some settings, they go into a text file or workbook stored in the add-in’s directory. Keeping the add-in clean also keeps the logic separate from the data and presentation.

    Your second approach is better, to create the workbook from scratch. Make a template with all the formatting and boilerplate you need, and create the new workbook based on this template. It’s easier to make changes to a template than to wade through the code to find where some obscure formula or format needs to be adjusted.

  3. Marcus Says:

    Hi Guys,

    There’s a coupe of scenarios where we might be tempted to save/manipulate data in worksheets of the xla. One is calculations and processing of data which comprise the add-ins functionality. The other is lists of (reference) data which are used to populate combo and list boxes. Experience has taught me not to do either.

    If the add-in is performing calculations or data manipulation, I’ll do this is a separate workbook or use arrays and persist the results to a workbook (depending on the purpose this might be a preformatted xlt or just a blank workbook).

    Where I have combo & list boxes which need populating, I’ll keep that data separate from the add-in. I can’t think of an example where I’ve stored that information in another workbook although I’ve used Access databases, XML and CSV files. Keeping lists in Access is very handy as sometimes, the list options a user should have are conditional and the relational structure makes this easy to manage.
    ADO’s capacity to save recordsets as XML files makes this very easy to do. reference data can be maintained in a central location but easily distributed.

    The only time I really store lists in the file is XLT’s.

    Regards – Marcus

  4. Simon Says:

    Pretty conclusive then!
    I wonder if its because I tend to develop things as wbs initially then only convert them to xlas if it offers significant advantages. I have caught myself out plenty with UI issues going from xls to xla.

  5. Ross Says:

    >>>Experience has taught me not to do either.
    I have never been able to save XLA properly, if you keep data in the xla, it’s buggered. So i always go for a text file (csv) or another workbook – i often change the extention for the file, too, just so i know it’s with that app – it seems to work ok.

    The secod way, for me. Also i would think it has performace gains aswell as you only touch the data once?

    Hide/not hide? I think i would hide it, but I think Deromts’ points are valid. I guess you have to have some sound error handling. IS there are peformace change when a work book is hidden?

  6. gobansaor Says:

    For some of my add-ins I’ve started to save lookup lists, datawarehouse extracts etc. in a separate ‘data’ workbook. Prior to using the datasets within my add-in I suck the data into a SQLite memory-based database giving me all the power of a relational engine.

    I’m also experimenting with using disk based SQLite databases as stores for data collection add-ins.

    The beauty of SQLite is its small footprint (two small DLLs), SQL92 and ACID compliance and its speed (especially using :memory: databases). SQLite’s use of “manifest typing” is also a good match for Excel tables/ranges (columns may optionally be given a type, but each cell will store anything thrown at it).


  7. Jon Peltier Says:

    Actually I do save small amounts of data in an add-in’s worksheets, but it’s limited to a table containing the menu and command bar information and icons, and maybe small lists for populating listboxes. This information is not changed by the user or by the program, just by the developer as required during revisions. For most other stored information I use CSV or worksheets.

    I think hiding a newly created workbook has issues, and Dermot has enumerated many of them. An important thing is that showing the workbook, even if you then turn off screen updating, shows the user that something is happening. I will often drop a homegrown progress dialog on screen, and once in a while update the screen so not only the progress bar updates, but so does the user’s view of the developing workbook.

  8. Ken Puls Says:

    I’ll also throw my vote on the “Don’t touch the add-in” pile. I’m a huge fan of xla files, and actually tend to start with them in many cases rather than with wbs. I started by building wbs files almost exclusively but, as was only predictable, they became hard to update when there were hundreds floating around. Much of my work lately has been to try and consolidate the tools into a central point.

    My personal favourite way to do things where I need dynamic data is to store it in a database. (ADO to Access is generally the way I work.) I’ve also been known to store settings in the registry, but generally don’t use text files. (Nothing against them, I just think databases are more fun, I guess.) As far as constant data, yes, I’ll store this in the add-in sheets. As Jon said, tables for menus (not in 2007 anymore though) and tables for other things on occasions as well. The important parts are that this data is never changed during the execution of the add-in routines, only by the developer at design time.

    As far as creating new workbooks in a hidden state, I’d avoid that as well. Screenupdating=false definitely, but not completely hidden for the reasons that dermot mentioned.

    Ross, as a point of interest, I have used an add-in that did store dynamic data in its sheets and it never had an issue with being saved on the fly. As I say, I don’t do it myself, but it will work.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: