Today I launched my third 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 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
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 main benefits of User Forms as the user interface for your Excel applications, in my mind, would be:
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!
Join today to the Excel VBA Inner Circle with Mor Sagmon.
The first to enroll as founding members will enjoy exceptional, life-long benefits and prices.
Click the button below to learn more and join.