The Full Dev to QA to Prod Cycle with Excel VBA Projects

When developing, delivering and maintaining software solutions, you need to manage the Dev-QA-Prod cycle. This is no different when developing an Excel based application * Here’s how I do it

The Basics of Development Lifecycle

After you finish developing a scoped application, you need to pass it over to QA – Quality Assurance. The QA folks are abusing the software and document their exceptional findings: logical errors, software errors, user experience issues, environment conflicts, and the like. Often, issues classified as important will bounce back to development for fixing. Then QA again.

When a satisfactory application is confirmed – it is passed over for productization.

Productization is different for products and for projects (I explain the difference between a product and a project in this Blog post).

For our purposes, let’s focus on a project delivered to a specific customer.

In large deployments, you may also find a Staging phase, before deploying into production, but that’s beyond the scope of our discussion. I have never staged a solution built on Excel.

The complete cycle is maintained across the different versions, or releases, as the application progresses with time and updates.

Different phases in the Cycle dictate different focus and behavior

Development

The focus of the development phase is to write code as per the specifications of the solution blueprint, or business requirements analysis (more on that – in another time).

The developer usually runs some form of a unit testing, even before QA, confirming each separate functional unit of the application performs its task properly.

The developer is interested in viewing all errors with as much technical data as possible, in order to apply good fixes.

Quality Assurance

QA may sound like a quick, easy task; however, it is a whole wide world in itself, a true profession.

Remember that QA is one step from the end client, therefore all needs to orchestrate together (integration tests), be prepared for high load in different running environments (load testing, performance testing, environment testing), meet the required functionality and be free of “bugs”.

Tests can be manual or automatic applying tests scripts that simulate different users in different scenarios.

In QA, we typically want to reflect the customer environment as much as possible, so the application needs to be “tight” – with all protection, licensing, logging, security, support control, help access and other supporting features in place.

Production

The production release is a tight application, reflecting the complete set of tests run by QA as possible, closed and ready for first run by the customer.

First run means the application runs initial tasks upon first run, such as: creating the Database schema and populating initial data; software license registration; Welcome/guide “wizard”, and the like.

Before closing an application for production, it needs to be cleaned of all data, its code may need to be protected, customer or license codes needs to be administered and the “Production” flag needs to be turned on (more on that below).

My Dev-to-Prod Process

The methodology I developed for my Excel VBA projects, is given here.

Keep in mind that I’m doing all phases myself, so I must discipline myself to secure each phase for its intended purpose.

Under the project’s folder, I’d create a QA and Prod (or Deliveries) folders.

I start the first version with the v.0.01 suffix, e.g.:
Customers Management v0.01.xlsm.

As I finish the development to the full scope of the current release, I copy the file to the QA folder and start challenging brutally 😊.

I will simulate a production application as well, with all protections, locks and initial processes in place, including creating the DB schema from scratch (I always create all of the DB schema automatically upon first run – no need for manual intervention with the DB beyond installation and user privileges settings on customer site).

I document any issues identified and check each one as I clear it back in Dev.

I never implement any code changes in the QA file – ONLY on the dev file! I can go back and forth several times before I’m ready to move the file to Prod. When in Prod, after cleanup, locking and all other preparations, I will copy the file as it is to be delivered, and run it once again as the customer would, in first run. This file copy will be deleted as it only serves for this last QA operation.

The Production Flag

To make things easier for me when switching between Dev and Prod mode (for testing and debugging files already serving the customer), I implement a “Production Flag”.

The production flag is a hidden and locked cell in the Settings Worksheet that I set to True or False.

In my GeneralControl VBA Module, I have a Boolean function that returns True of False based on the production flag, as follows:

Function InProduction() As Boolean

    InProduction = ThisWorkbook.Names("In_Production_Flag").RefersToRange.Value

End Function

Since I don’t want any error handling during development (except when testing), all my error handling calls are set only if I’m in Prod state, as follows:

If InProduction Then On Error Goto FailedSub

I also don’t want to hide any helper Worksheets during RestoreState when in Development, therefore I’d call the HideFlaggedSheets only when in Production mode, as follows:

If InProduction Then

        Call HideFlaggedSheets

End If

Other times I may utilize the InProduction function include: Automatic data export/backup upon closing the application, first run initial tasks, licensing verification (if applicable), Logging/debugging, and the like.

By the way, when you download my 105 Excel VBA Functions Library, you will find the InProduction and RestoreState functions. In the matching Udemy course I offer I explain in detail each and every line of code in those 105 functions, along with use-cases and other implementation considerations.