Background

The Challenge

Many of us use Microsoft Excel spreadsheets as databases.  You might not think that what you are doing is managing a database, but any organised collection of data can be regarded as a database, and that is often what we use Excel for.

Sometimes it is just a mini database for recording personal activities.  Sometimes it is a business database to fill a need not met by the corporate systems. And there seem to be many organisations who still run a large part of their business on Excel.  You might well question the wisdom of doing this, but for a variety of reasons there is often perceived to be no real alternative. The question then is – what is the best way of going about it in Excel?

Standard Microsoft Excel, where you input data into cells on a grid, is not well suited for data entry and validation – both highly desirable components of a database application:

  • If there are more than about 10 columns on a sheet you have to make the trade-off between horizontal scrolling and making the columns narrow.
  • Data validations can be specified in Excel, but they can normally be bypassed easily.
  • It is just not user-friendly enough, for example when it comes to entering long text fields.
  • For record entry you really need a form based approach where the user can concentrate on one record at a time.  Ideally you also want the approach taken to rigidly enforce the business rules (data validations), so that a user of the spreadsheet cannot bypass the rules and you end up with sick data.
  • In a real world application, you will also want to enforce the sort of rules that would be fairly standard in a database application, but which are difficult to achieve with Microsoft Excel data validations, for example:
    • Unique identifiers for each record.  For example if you have a table containing employee details, you will almost certainly want to be sure that no two employees have the same employee number.  Achieving this with Microsoft Excel can be done but is trickier than it should be, especially if combining uniqueness with another constraint (e.g. that the value should also be numeric).
    • Robust cross references between different tables.  For example if you have a table of employee data and each employee record refers to a department table, you don’t want someone to be able to delete a department record while there are employee records pointing to it.  This gives rise to formula errors and corrupt data.

The Options

None of the above is new.  An internet search will provide you with a lot of references to creating database-like applications for Excel.  Typically these fall into one or more of the following categories:

  • Make use of the built-in Form function in Excel.  This has been around for a long time but does not seem to have been updated by Microsoft and is well hidden such that you have to customise the ribbon in order to find it.  It has a number of limitations.  For example, it only supports up to 32 columns in a table.  That may seem a lot, but if you do have 32 columns or more, the need to use a form is more acute than ever. The built in Form function also makes it quite difficult to navigate through records – it always starts back at the first record even if you were hoping to update a record near the end of the table.  And it shares most of the drawbacks in terms of data validation of just using Microsoft Excel directly.
  • Start programming (VBA code) or get the cheque book to have someone do the programming for you
  • Use a code generation product which will need to be re-done if you add columns to or remove columns from your table
  • Use Microsoft Forms.  This is really only of use for survey type of data entry applications.
  • Find a product that is specific to a particular line of business or a particular set of tables and applications
  • Use a database management product such as Power Apps which you can map on to Microsoft Excel as an underlying database.
  • Use an alternative spreadsheet to Microsoft Excel

Why Dexel Form

The built-in Excel Form function seemed the right general way to go – you get a data entry form with practically no investment of time or effort – but for the reasons outlined above the implementation is less than ideal. Dexel Form addresses those failings and adds a lot more functions to help you make your databases robust and reliable.

How Dexel Form works

Environment

  • written entirely in VBA (Visual Basic for Applications) on Office 365.  It does not use any specific Excel 365 features, so may well run on previous versions of Excel but this has not been tested.
  • does not use any Windows APIs and it runs fine on Excel on a Mac.
  • supports up to 1,000 columns in a table.  The number of rows is limited only by what Microsoft Excel supports. Dexel Form has been tested on tables of 1,000,000 rows.
  • works only on Excel data tables.
  • works only on desktop versions of Microsoft Excel.  It will not work on web versions of Excel.
  • does not work on shared workbooks.

Installation and Security

  • Dexel Form does not access the internet, except to provide a clickable link to dexelform.com from the About page
  • does not access your computer’s file system except that there is an optional install script to copy the addin to where Microsoft Excel expects to find it.
  • does not access your computer’s system registry.
  • the entire install consists of a single .XLAM file. There are no extra supporting DLL or executable files required.
  • you will need to allow Excel macros to run on your computer. If you cannot do this, or your organisation’s policies prohibit it, then you will not be able to use Dexel Form.

What does Dexel Form do to the contents of my spreadsheet

Nothing sneaky. Your spreadsheet remains a spreadsheet and you can stop using Dexel Form on it at any time. In general terms, Dexel Form:

  • adds some info to column headers in your tables
  • formats the data in your data tables based on the column properties that you define
  • uses a work sheet in the background to do all the hard work.
  • if you are using Dexel Sharing, then the original workbook will be marked as “hidden” to reduce screen flicker, and Dexel Form will open and close that workbook as required to perform record operations.

In more detail:

  • adds notes to the column headers in your table.  These record the column and table properties that Dexel interprets and applies.  You can see these pop up as you hover with the mouse over the column header.
  • formats columns to correspond to your selection in the column definitions, and applies this consistently through the table
  • generates lookup formulae for those columns that you customise to be lookups
  • applies formulae that you have defined consistently through the table
  • strips leading and trailing spaces and CrLf characters from data
  • creates a hidden worksheet in your spreadsheet called DexelFormulae which is used by Dexel Form to
    • allow you to define validation and default formulae
    • calculate validation formulae
    • evaluate work formulae such as calculating the number of active rows in a table
    • build up temporary tables such as the one needed to define column properties
  • if you have Dexel Form protection enabled, then Dexel Form will protect and unprotect your worksheets and workbook when needed
  • if you have auditing enabled, Dexel Form will create an audit worksheet and table in your workbook.
  • Dexel Form also uses custom document properties:
    • if you use password protection, Dexel Form will save that password in obfuscated form as a custom document property.  It needs this to be able to protect and unprotect password protected worksheets
    • a backup of your column definitions is saved as a set of custom document properties.  This is a fallback if a user deletes column definitions by toggling table headers off and on.
%d bloggers like this: