What separates an Excel model from a professional business application? Besides good programming practices it’s the user experience * Excel VBA User Forms allow you to deliver mission-critical business applications that will make your customer WOW.

Today I launched my fifth course in the Computer Programming and Databases with Excel VBA and SQL program.  This course, titled Beyond Excel Boundaries with User Forms: Deliver a Professional User Experience, covers User Forms implementation as the user interface for Excel-based business applications.

This launch today made it an easy choice for my Blog post subject today 😊.

The inherent limitations of Excel Worksheets as a user interface

The origins of Excel are rooted in Lotus 1-2-3, the first widely-adopted electronic spreadsheet that boomed in the first years of personal computing.

The “magic” was in the real-time calculating grid of cells. No fancy pixel-perfect, charts or formatting capabilities were available.

Powerful as it were, in front of every model or application, there’s the user. The more sophisticated models and solutions possible to be realized with Excel – the more pivotal the user experience, UX for short, became.

With time, electronic spreadsheets, with Excel taking the lead, have evolved to be one of the most powerful ubiquitous software on the table of every person.

The rich formatting, layout options, multiple tabs, GUI controls and of course VBA, allowed for more and more types of solutions to be developed with Excel and serve the business community on its diversity.

From financial modeling, through engineering, business analytics, operations, manufacturing, Database-driven information systems and many more – it seems Excel can be shaped and carved to meet almost every need out there.

Still, while software user experience evolved to be a science and art, with the notion of event-driven user interfaces that adhere to a business flow and the human’s psychology, the good old grid of cells remains the fundamental canvas for crafting a user experience in Excel.

Don’t’ get me wrong. With skills, UX design understanding and creativity, one can do wonders with Excel Worksheets. However, reaching the level of user experience, pixel-perfect and flexibility overall we’re used to when working with classic Windows-based applications – is still very challenging, to the point it doesn’t make sense. Just to show you it can be done, I’m showing here a user interface for creating invoices I implemented over Worksheets. The data of course is stored in a table in another Worksheet.

Forcing a Worksheet to serve as a user interface

User Forms changes the game

As Excel became so popular and ingrained in the work of almost every profession, it yearned to evolve into a native Windows development tool, similar to its older brothers: VB and .NET.

VBA unleashed Excel out of its chains into the game of Windows development environment. VBA was featured as the language used in MS-Access, a tool designed for business information systems, employing the classic Database – logic – presentation architecture.

With VBA, ActiveX controls also evolved, in essence allowing a Windows-native user interfaces to be realized. What forms the presentation layer in MS-Access, found its way into Excel VBA environment, named: User Forms.

Taking it to the extreme, just to make the point, one can use Excel VBA with its User Forms to develop a pure Windows application, having nothing to do with the Excel object model at all! No Worksheets! Using the Windows ACE engine, you can connect to an external Database, with VBA functions you implement all the business logic required and you use User Forms to serve as the user interface of the application. It’s almost similar to the process one would develop a .NET application using Visual Studio.

Windows-native user interface designed with Excel VBA User Forms

The significant benefits of User Forms

The main benefits of User Forms as the user interface for your Excel applications, in my mind, would be:

1. Data integrity using a controlled user interface. There is a clear event that separate the data entry process from saving the form data to the underlying tables (either in Excel Worksheets or an external Database). That single event offers an opportunity to thoroughly validate the data before it is actually recorded in the database. Moreover, when data is saved in multiple tables with relationships between them, we can promise the full set of related data is saved in all tables as an atomic transaction.
2. Intuitive workflow by the user. A good arrangement of the controls on the form: their grouping, type, colors, tool-tips, tab order and other form design elements – meet the user thought process as he is expected to work with the form. It is like the form itself hints the user of what is expected from him throughout his experience with it. Can you think about a poor user that needs to update a wide table with 50 columns with dependencies, calculations and validations? Can you now imagine a single form where all 50 columns are arranged as per his thought process? Take a look at the above blue-background User Form – can you imagine the discouraging experience in updating all of that in a Worksheet table (or tables)?
3. Total flexibility for the developer. As with any Windows-native GUI design and implementation tool, you can orchestrate your program to meet any business requirement by choosing the right controls, their placement, the form design, the event handlers and the interaction with the Excel objects and capabilities.
4. Highly attractive for the customer. A picture is worth a thousand words. Show the customer an interface screen that immediately makes him feel at home – and you win the game instantly.

Taken together, the benefits of using User Forms in Excel applications are a huge game changer, for you as a developer and for the customer as the user.

I would say that it is only with User Forms, one can offer Excel as the platform for a mission critical business information system (I’m excluding pure models that are not typical information systems). The only missing component to develop and deliver a robust, scalable and high-performing application would be an external Database.

I suggest you invest in understanding user interface guidelines, design patterns, designing with the mind in mind, and of course – using Excel VBA User Forms to produce and deliver beautiful, intuitive to use, professional business applications!