Workbook or App level?

I’ve released a few free utilities aimed at making working with big workbooks easier.

I did an index one that lets you add an index sheet with a few navigation tricks to make hiding and unhiding multiple sheets easier.

And I just did a print one to let you group sheets into various lists and then print them out with a single double click instead of having to visit each one and print it.

I’ve done them as worksheets with code behind that people can just copy into their own workbooks, refresh and use. I chose worksheets + code because much of the info would be specific to that individual workbook. This also keeps the one file solution thing going. And it keeps the info on worksheets where I personally like to work with it.

The down side of course is that you end up with potentially hundreds of copies of the same code, one copy in each wb.

Another alternative that I have used at clients is to have an application level addin, so just one copy per user. Each workbook could then contain just the data part and call out to the add-in for the code side of things. This strikes me as a better approach mainly from an updatability POV. But also commercially, not distributing all the code behind the worksheets gives more opportunity for better IP protection. The big downside is that now everyone who wants to use the index or print functionality needs a copy of the add-in. I.e the one file solution is no more. This might be an advantage from a commercial POV I guess.

Obviously I know the answer is going to be ‘it depends’ but, which do you prefer as a developer, and as a user?

For my own personal use I like the simplicity of code behind a worksheet, until I’ve got loads of copies and then find an error! I am thinking that for users who may have 20 or 30 workbooks they use regularly, keeping the main code in an add-in and just having simple call-out stubs in the code behind the worksheet will probably be best.

What do you think?




6 Responses to “Workbook or App level?”

  1. Dick Kusleika Says:

    In a previous comment you said “Double click – good catch,…”. I’m an app level guy, for just this reason. If you want to fix/enhance any little thing, you’re too late because I’ve already copied the sheet into 20 workbooks. Now I have to copy it again? Which ones did I copy it into?

    So here’s the ‘it depends’. If the code only relates to one workbook, the code can live in that workbook – there’s still good reasons to separate it, but it can. For the print application, it really needs to be at the app level because it applies to many workbooks, but not an particular workbooks.

  2. Ross Says:

    Yes it’s about the right method for the right task.

    I would just raise the point that central code (as in an addin) is not always best, code behind lets you make a few specific changes to each work book should you need to, and your code does not have to be as general and therefore can be faster to write. – Just some pros fro workbook level.

  3. Marcus Says:

    Generally speaking I tend to be in the App level camp but that’s probably reflective of the work I tend to do.

    As in previous posts, it typically comes down to fit for purpose.

    In this example though, it’s easy to see how one copy becomes dozens and then hundreds. It’s a reasonably generic utility which could be applied to any workbook – hence my preference for app level.

    Cheers – Marcus

  4. Jon Peltier Says:

    I prefer the global approach. It makes changing the code so much easier. Otherwise you risk losing your data when you update workbook code.

    In some cases, I’ll have a global add-in in concert with workbooks (templates usually) that have specific code. It’s complicated, but still honors the separation of code and data to a greater extent. Actually, it’s more along the lines of modular programming, and the add-in is like a global module.

  5. Dennis Wallentin Says:

    Whenever a solution become ‘generic’ it should be classified as a candidate for application level solutions. The “one file”-approach has in my opinion become obosolete.

    Kind regards,

  6. Biggus Dickus Says:

    The Add-In is the best way to go if there are going to be multiple copies of the file in a network scenario. Just launch Excel and lanch the Add-In from a URL.

    On the other hand, lately I have been creating single files with code to prevent multiple legit copies (single version of the truth) and in that case code in file is just fine – preferred actually,


Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: