In software development 'Agile Development' means the use of formalised practices and a culture that focuses on:
A certified ScrumMaster, I have experience in grassroots Agile development through to project managing multi-million dollar Agile projects, and I'm passionate about its value in achieving success.
Spreadsheet-based software projects tend to be single-developer and relatively small-scale/fast turnaround. With the right approach, the first 2 of the above items are therefore quite straightforward to incorporate.
What is often missing in Excel projects however is code quality, especially in the long term. This is what tends to get in the way of achieving points 3 and 4 above.
In Agile projects, the development of automated tests is what facilitates long-term code quality, and should always be embedded in the development process. Essentially, as a new feature is developed, test code is written that simulates real-life use by applying sample inputs and checking that the resulting output or behaviour is as expected. By then running the full test suite regularly during the development cycle, the developer can have confidence that no previously developed features have inadvertently been broken.
Additionally, as the system evolves over time, old code can be refactored/simplified/removed confidently knowing that any issues will be highlighted by the failure of one or more tests.
For Excel/VBA development the Rubberduck add-in provides the capability to do this. It defines how to code up test modules and can then run them all as a test suite within the VBA development environment. Any failures are reported with an explanation of what went wrong.
The Rubberduck Test Explorer window showing 1 failure following an otherwise clean run
On a typical Agile project, continuous improvement would be achieved by reflecting on each completed development cycle, and feeding back any lessons learned into how future ones are conducted. In Excel projects however, where again individual projects tend to be smaller and so less likely to run to many back-to-back iterations, conducting the review at the end of each fully delivered project (for the benefit of future ones) tends to make more sense.
Over the course of completing many projects, I have continually enhanced (1) the template workbook from which all of my projects begin, and (2) the many home-grown feature libraries that I use (through re-use) to deliver rich functionality quickly and cheaply. To offer a flavour, many of the following things would be considered standard on a mainstream software system, but on a lowly Excel/VBA project would often be overlooked. For a project containing any level of coding however (and some of my projects have run to several thousand lines of code), a level of software maturity really should be insisted upon.
In any of my projects that include VBA macro code (which is generally the case), the 'About' sheet shown below is displayed upon entry (and with all others hidden and non-unhideable) if the file is being opened for the first time or if macros are turned off. It provides a link to a custom form that displays the changes in the current version (release notes), and another to continue. As the continue link itself runs a macro that displays the other sheets, the user cannot proceed unless macros are turned on. This is intended behaviour as there can often be some hidden macro code in the main sheets that makes updates without the user explicitly pushing a button. If a user edits such a workbook with macros turned off, it can therefore fall into an inconsistent state. In this way, this entry mechanism not only looks professional but it also protects against this eventuality. If required, it can also function as a login screen, validating the entered credentials before granting access to the main sheets.
The standard XY About/Login sheet that greets the user when macros are turned off
(click to expand)
Moving from a development to a production (live user) setting in a mainstream software development project is usually accompanied by a defined process that is followed in order to do such things as deploy new code on to production servers, make database changes and update the system configuration. Agile teams will generally try to automate as much of this as possible as a matter of priority in order to minimise human error during this important procedure. With Microsoft Excel developers commonly not having a professional software engineering background however, finding this level of discipline in Excel/VBA development is unfortunately rare. I have however found that with a central 'publish' macro in each workbook, I can:
As alluded to above, a list of the changes made to each new version of a project are stored in a 'very hidden' sheet that the user cannot unhide. A custom form is then used to view these from the About screen that is shown upon entry.
Viewing the release notes of the current version
In the interests of development efficiency, a custom add-in provides me with quick access to these and other home-grown development tools directly from the Excel ribbon.
Custom XY development tools
