Excel forms

Marcus picked up the sense that I wasn’t overly impressed with Excel (Office) forms, and suggested I post about why that is. So no massive gems of insight or anything but:

  1. Excel forms have a lot of useful events missing compared to VB. I really struggle to get the level of control I want because of this.
  2. Excel forms don’t have control arrays which I find useful
  3. General lack of control about look and feel.

And thats pretty much it, those are the reasons I prefer to use VB6 forms (well C# .net are my favourite actually – I like that its easy to make them semi transparent, and controls can be anchored for really simple resizing).

However the bigger issue is that I tend to use a worksheet rather than a form. I find worksheets very powerful, so it annoys me when I get some crappy list box interface that is so feature poor. I tend to use the forms controls too, not the activex ones.

If I use Excel for a system then I use as much of it as I can and I try to leverage the users skills in the product too. A good example of that is the index thing here, (the workbookstructure index sheet). I could have done that as a forms thing, and it would probably have looked more ‘professional’, but I did it in a worksheet so users can sort, edit replace, group etc easily using skills they already have. Of course it also saves me writing and testing code to do all that stuff.

If I do something that is mainly forms based I would probably do it in C# or VB6 as an exe, and automate Excel in the background.

I’m not convinced either is better or worse. It could be that I am just lazy and/or poor at user interface design, but I like to dress it up as using the product as much as possible. I also tend to use workbooks with code behind rather than add-ins in a corporate setting. Mainly because its easier, even though I have menu generators etc, and it makes it easy for the user to control the opening and closing. I tend to use add-ins if I’m doing something to the activesheet/wb, but little utilities etc tend to be plain workbooks.

Whats your preference (Forms engine, UI, add-in v wb) and why?



7 Responses to “Excel forms”

  1. Ross Says:

    I don’t mind Excel forms, I wish MS had added a few more powerful controls, but you can get most of the controls and control you need via APIs albeit with a lot of extra code.

    There was a similar talk over at DDOE. I find forms an easier place to build/construct complex input and control UIs, but I think reporting etc would have to mainly be done in wks. I developed a big app using only wks UI and it was very hard work controlling the “screens” – it work ok in the end, but there was a hell of a lot of code need just to control the display. So for big apps I would use Office forms or automate just using Excel as the engine behind a VB/.net UI – knid of like using Excel as a big com component really?

    I know a hell of a lot of people who swear by deploying most of their work as Addin. I tend to think of addin’s as things that add functionality to a product, not so much a app/functional worksheet. I tend to code behind, with worksheet level stuff and core code in the same file – its just the way I tend to do things.

  2. Jon Peltier Says:

    I also prefer forms for a lot of tasks that could be done with a worksheet. I find it easier to control and validate user input when using a form. However, reporting is best done via worksheets. Especially dynamic reports in which the user can adjust the output through sorting or filtering. If I need controls on the sheet I usually stick with the Forms menu versions.

    That said, there are times when the worksheet is the best way to gather input, a form is the best means of reporting, and ActiveX controls work just fine on the worksheet. I try to follow the rule of “what works best” for a given situation.

  3. Biggus Dickus Says:

    ” I tend to use the forms controls too, not the activex ones.”

    Same here!!! Actually I’ve had corrupted files because of the ActiveX controls. I remember getting bugged by someone because I was using the native controls rather than the cool new ActiveX ones. Big deal…. I use what I can rely on.

    “If I do something that is mainly forms based I would probably do it in C# or VB6 as an exe, and automate Excel in the background.”

    I use Access ;-) Forms usually means data capture and I’d rather use a database for catching that – even if the analysis is in Excel in the end.


  4. Dennis Wallentin Says:


    I prefer the worksheet when it’s not necessary to implement code based solutions.

    In view of what .NET offer the UI we get via Excel’s forms is at best poor. I use VB6 created COM add-ins instead of the built-in forms Excel offer.

    It’s not the best UI but it’s on the other hand not the most bad UI so on the average I think it’s great (expecially when we use third part ActiveX components).

    Kind regards,

  5. Marcus Says:

    The perspective I’m probably looking at this from is a preference over MS Forms over a worksheet for a GUI.

    “don’t have control arrays”
    Yes I miss these too but work around this limitation by making forms as table driven as possible.
    Validation, for example is table driven. The table containing the validation rules can be in a hidden worksheet or in a central RDBMS table (where possible).
    For controls where I would ordinarily using a control array I name the controls identically expect with an index number suffix (e.g. optReport01, optReport02 etc.) I then use the Controls statement to loop through the controls in question. Unfortunately this approach still requires each control to have it’s own event handler. Here I call a single function passing the current control index.

    Doesn’t have User Defined Control
    I miss having these too. And while I could roll-a-control in VB6 for an Excel solution I’m aiming for a zero installation footprint for corporate environment. Registering ocx’s would fail in many environments in which I work. Bummer.

    “General lack of control about look and feel.”
    I’ve gotta disagree on this point, Simon. Most GUI development is smoke and mirrors and you can get some function and attractive GUIs using MS Forms. I’ve sent you some screen shots as examples – I couldn’t find any way to publish them on the blog.

    Also, unless it’s a stand-alone template I rarely use Excel to store data but do use worksheets heavily for reporting. Even when developing Access databases, I tend to use Excel and/or Word for reporting output.

    Being windowless; yes there is a loss of granularity in control. However there is usually a reasonable workaround. There are many code examples to resize controls on MS Forms for example.

    Transportability between MSO apps is beneficial. I’ve migrated Wizard style solutions between Excel and Word quite quickly.

    “Whats your preference (Forms engine, UI, add-in v wb) and why?”
    I don’t think there’s a perfect solution as each choice is conditional.
    I’ll choose MS Forms over a wks GUI when deployment is restricted (as in most of my corporate environments). I’ll also try to make the solution an add-in, storing the data centrally in a RDMS rather than having it template based. I also feel that wks are a poor second cousin to MS Forms in Wizard style solutions.

    I’ll choose VB6 of MS Forms when there is deployment flexibility. And unfortunately, while I’ve had a tinker, I’ve yet to use .Net Win Forms in anger (action).

    For me one of the driving reasons is user experience. Most users have no idea what’s happening under the bonnet and can only judge the style and duco as to the quality of the vehicle. I try to spend a bit of effort on usability and making the GUI attractive and feel that forms provide more control and options in this area that wks.

    Thanks for the post, Simon – interesting as usual.

    All the best – Marcus

  6. Jon Peltier Says:

    Marcus –

    Good analysis. Bottom line: there’s no single “best practice”. It depends on what the objectives are and what the user environment allows. And I agree that userforms can be used to create a decent UI.

    Regarding the lack of control arrays, you can easily enough set up an event class module that responds to button clicks for the controls you would have preferred in a control array. In fact, this is the only way I can think of to handle clicks on a control added on the fly. It’s a tiny bit more work, but adds a lot of flexibility.

  7. sam Says:

    I use forms provided it improves the user exceprience of entering data.
    I would not use a form to enter data in sheet….. I would instead ask the user to enter data directly in to the sheet. I can validate the contents either through data validation or through event code..

    On the other had lets say you want the user the specify a path in a cell where the an output file needs to be stored….in this case instead of asking the user to enter a long path like c:\Documents and setting\blah….blah etc… I would insted provide a Browse for Folder Form….


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 )

Google photo

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