XY Spreadsheet Solutions

Call Now:
0771 247 6588

Agile Software Development

Agile Development

In software development 'Agile Development' means the use of formalised practices and a culture that focuses on:

  1. Collaboration between people
  2. Iterative and incremental deliveries
  3. Evolutionary development
  4. A flexible approach to change
  5. Continuous improvement
It is fast becoming the recognised standard for those who are serious about delivering high-quality software and in a manner that adds the most business value.

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.

Agile Software Development in Excel

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.

Test Driven Development in Excel

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

Continuous Improvement

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.

Macro Safeguarding

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)

Release Script

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:

  • Apply the correct visibility setting to all sheets (i.e. visible, hidden or 'very hidden' for non-user sheets)
  • Apply the correct cell protection to all sheets (and with the correct password applied)
  • Make the workbook appear 'new', with any test data removed from data entry fields and the scroll position returned to the top left
  • Refresh any reference data from client databases
  • Update the release notes and the version number
  • Alert if the VBA project code has not been password protected

Release Notes

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

Development Tools Add-in

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