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
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.
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.
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
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
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.
WANT TO LEARN PROGRAMMING WITH EXCEL VBA?
I created a complete 8-course program for you.
From programming to databases with Excel VBA to deliver business-grade solutions.
Take the first course today: Computer Programming with Excel VBA.
Click the button to see the complete program and start now!