XLA design 2

So in the last post we basically got to:

  1. Don’t do temp work in the XLA (even though it almost certainly will not get saved (even if we try according to Ross!))
  2. Do stuff in a temp wb that is not hidden and appears when screenupdating gets turned back on. (Does anyone else get it so that screenupdating doesn’t come back automatically sometimes?)

Now I’m not going to disagree with that but…

If things do go wrong the user is going to get left with an inconsistent (but visible) workbook sitting in their environment that they may not know what to do with.

If its in the XLA and it fails, it will do it quietly, or maybe with a polite warning. They may be able to rerun it and everthing work or they restart Excel and re-open the add-in and everything should be sweet (in theory!). That seems potentially a better option to me? Especially as you can now grab the restart to fix things up.

I’m thinking of some transitory problem like the network bouncing, or a resource temporarily locked.

The other issue I have is messing with the users Excel environment, I like the PED approach of dropping an xla in xlstart to fix things up after a crash, but must confess I have never done that in practice. Have any of you? Has anyone ever copied the .xlb and restored that on failure/recovery? Finding it/them is hard I know!

The other potential option I keep coming back to (thinking about) is automating another Excel instance, doing all the background stuff in there and then bring it back to their current environment if it succeeds. Of course if that goes bang they are going to end up with loads of invisible Excels dragging their system down. I’ve done this with VB/.net apps, and managing those zombie Excels can be a pain. Has anyone done this from an XLA? maybe its inappropriate if you are generally working on the active wb?

I’m really trying to come up with a rock solid add-in approach, that isn’t going to give the users any frights or suprises. Its not enough to be careful, add-ins do crash, and when they do, they usually take the whole Excel instance with them. Well actually thats not true, COM add-ins tend to fail on their own and don’t bring Excel down.

I’m beginning to think there is no clear winner approach. It seems there are pros and cons to both the ‘hide-it-till-its-ready’ approach and the ‘show-all-workings’ approach. Do you think target user ability might be a factor, as well as the add-ins job?

As a matter of interest, if you are not using the worksheets in your xla for anything much, why dont you use a COM add-in instead? is it deployment/tool access (VB6 or Dev Office), or other factors?



12 Responses to “XLA design 2”

  1. Ken Puls Says:

    Hi Simon,

    I don’t use VB6 as I have no need, really. I have always been able to deploy the solutions I’ve built using only good old VBA and native Excel functionality. Maybe that shows my limitations as a developer, or maybe it shows that I know the app well. Arguments could be made on either side for that. LOL!

    I certainly wouldn’t be farming out a new invisible Excel instance to do the work. That seems like much unnecessary overhead. You’re dead on with the invisible Excel instances consuming memory, without the user ever knowing.

    In the scenario that you mention about restarting Excel and starting fresh and new, why risk putting the data in the add-in and giving it any extra excuse to cause corruption? Not saying it is likely, but the more you modify the file, the likelihood must increase. At least a temp workbook is just that — temporary — so it really doesn’t matter in the grand scheme of things.

    With regards to leaving an open workbook to scare the pants off your users… I assume that you use a global error handler. Couldn’t you just set up a test in the error handler to see if your temp workbook was still open and close it if you felt it necessary?

  2. Dennis Wallentin Says:

    Simon et al,

    I have some basic rules I always try to follow:

    1. Never mess with the end users’s enviroment
    2. Never mess with the end users’s enviroment
    3. Never mess with the end users’s enviroment

    Which explain why I don’t support the approach of “dictating” solutions in Excel.

    1. Never try to hide certain commands in Excel
    2. Never try to hide certain commands in Excel
    3. Never try to hide certain commands in Excel

    When I worked with a worldwide Swedish Corporate I learned alot where the following tries to summarize it:

    1. Use plain textfiles to store settings etc.
    2. Never write/read to Windows registry
    3. If plain XLAs are in use never write to them.

    = It may violate the security policy

    One of the main advantages to work with .NET and VSTO is the possibilities to handle settings etc with the app.config XML file – It’s really a superb solution!

    The next version of my commercial add-in SQL Tester will be a 100 % .NET managed COM add-in.

    However, I’ve been using XLAs to load/unload managed Add-ins and in view of the positive outcome I can see that it exist advantages to use that approach as well.

    Kind regards,

  3. Simon Says:

    All good points – xlas sure are an easier deployment story than COM add-ins. We are spoilt in a way, in Excel/VBA dev because that whole thing is just so pain free. One of the resons why VSTO is such a shock to the system!

    Dennis, I totally agree. I’m not a fan of dictator apps – I think a forms based exe with grid control is more appropriate if Excel has too many features for your app.

  4. Dennis Wallentin Says:


    As for the forms based exe I’ve done some powerful solutions including the OWC package, especially the Spreadsheet component.

    Kind regards,

  5. dermot Says:

    I agree with all of the above – I have always found that I can do everything I need with just Excel, and I NEVER mess with the user’s environment.

    If you are worried about leaving a mess if the XLA crashes in the middle, you could try this – when you create the new workbook from the XLA, first copy across a sheet which has an error message – eg “If you are seeing this, something has gone wrong. Try again or contact xxxxx”. If all goes to plan, you delete this sheet at the very end. If you have turned off screen updating, the user won’t see it unless the system crashes.

    I’ve used this approach to tell users who refused permission for macros that they need to re-open and allow macros, by simply saving the workbook on the page with the message, and my first VBA command is to hide that sheet.

  6. dermot Says:

    one more thing I forgot to mention – how are you going to debug an invisible instance crash over the phone/email? Far better to have everything on screen, especially if you keep a running log on the top sheet (which is deleted at the end before the screen updates), so if it crashes in the middle, the user can tell you exactly where it got to?

  7. Jon Peltier Says:

    Dennis –

    Words to live by.

    “1. Never mess with the end users’s enviroment
    2. Never try to hide certain commands in Excel”

    I dislike making dictator apps. I hate when other apps mess up my Excel workspace, so I don’t want to do that to other people. I’ve done a couple things in VB6 or on VBA user forms rather than disable part of Excel’s worksheet UI.

    “1. Use plain textfiles to store settings etc.
    2. Never write/read to Windows registry
    3. If plain XLAs are in use never write to them.”

    Sometimes a worksheet is more convenient than a text file, so I use a mixture of both. I know how to use the registry, but only mess with it in my setup files that install add-ins. A text file can go with a user to another computer. How are you going to transport the registry?

    Dermot –

    I use the macro notification sheet very frequently. Simple and effective. It requires a Before_Save routine to hide the real sheets and show the notice in the saved version.

    I often have a debug log file where I dump information during execution. Typically the files are dated, debuglog_yymmdd.txt, and the first couple of debug lines are who the user is and what Office and Windows versions are in use. Users generally have no idea what versions they are running, and they only say useful things like “Run Time Error” or sometimes “Run Time Error 1004”. It’s much easier to say, email me the debug log files.

  8. Simon Says:

    All good points.
    And if you use MZ tools you can log the failing line number for really simple debugging. I spose you could manage line numbers manually, but I’d rather not.

  9. sam Says:

    Has anyone ever copied the .xlb and restored that on failure/recovery? Finding it/them is hard I know!

    Does anyone know the structure of an XLB file….
    I have seen absolutely no documention on this file type anywhere…

    For example is it possible to manipulate this file…. what kind of a file is it…
    a very very hidden file….???

    How does it store the information about the custom changes….
    Why does it sometimes suffer from a file size bloat ???


  10. Dennis Wallentin Says:


    >>How are you going to transport the registry?

    1. Locate the wanted key in the registry
    2. Right click on it and choose the command Export from the menu
    3. Save the regfile
    4. Port it to the new computer
    5. Double click on the reg file to write its data to the registry

    This shows how vulnerably a computer actually is…

    >>It requires a Before_Save routine

    Nice one – thanks :)

    Kind regards,

  11. Jon Peltier Says:

    Dennis –

    >>How are you going to transport the registry?

    That was a rhetorical question. I know how to do it, you know how to do it, but the unwashed masses will only come to ruin trying to follow such simple steps. (As you implied with your observation about computer vulnerability.)

  12. Marcus Says:

    If I may add to Dennis’ pearls of wisdom:
    > Never try to second guess the end users’s environment
    This includes screen resolution or (worse) expecting certain DLLs/OCXs to be installed.

    Anyone else been burnt with the Calendar control? I now use a 100% VBA based calendar in my XLA’s.

    > Never try to hide certain commands in Excel
    Yup. Dennis you’re singing to me now.

    > Use plain textfiles to store settings.
    Well, now I may be a little out of tune. I’ll either use CSV, XML or MDB (via DAO).

    Dennis: +1 for the OWC spreadsheet control. I’ve used this successfully in several projects where a flexible grid control was needed. Sorely under-rated.

    Simon: +1 MZ Tools. The line numbering function is a major benefit. But I also like inserting function headers and error handling – saves me having to copy and paste from my text file template.

    “Has anyone ever copied the .xlb”
    Always create temporary toolbars/menus that are destroyed when Excel closes.

    Regards – Marcus

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: