A story about a customer request and the delivered Excel VBA solution

A rare glimpse of the “behind the scenes” work on an Excel project. From the raw customer request through the business requirements analysis report up to the delivered solution. Impressions from the first Excel VBA Inner Circle workshop.

The first Excel VBA Inner Circle monthly workshop took place two days ago.

The monthly workshop is a deep dive into some aspect of Excel VBA and SQL programming and career. I expose advanced VBA programming, code, user experience considerations, data design and processes planning, and any professional aspect having to do with Excel VBA and SQL.

In the workshop, I shared my “inner thinking” about a customer request for a solution, how I analyze it and come up with a complete solution.

I took the time to go into details about the customer’s story and pain, I presented and explained his original Excel file he used before coming to me, the business requirements analysis document that I prepared before starting working on it and of course, the delivered solution.

Along the way, I candidly shared my thought process, considerations for doing this or the other and explained some of my VBA code used to implement the solution.

This was a unique opportunity to witness the “works behind the scenes” and be exposed to documents and actual code as being done in a real customer case.

While the full recording of the workshop is available only to the Excel VBA Inner Circle members, I will share with you some of the highlights here.

The presented business need

The customer is selling enterprise software (such as CRM, ERP). He approached me with a need to effectively track the presales process of all opportunities, on a quarterly basis.

He presented a simple, 3-tab Excel Workbook he uses to work with currently, basically 3 separate tables, no formulas, no connection, just an elegant replacement for a pen and paper.

I quickly realized that behind the rudimentary Excel tables he could come up with, lies a major business need that puts the business at a high risk for not being supported properly.

A presales process in this industry may take 2 months up to a year, and even beyond. During the process, several team members are pulled into the effort, to drive the deal to a close.

It involves technical experts in different fields, business development, sales, politics, organizational change management projections and other challenges. Dozens of meetings and live demos are scheduled.

As you can understand, the company invests heavily in an opportunity they may eventually lose, with many parts at play.

Orchestrating this expensive and complex process becomes a critical business effort, and that’s what I identified missing for this customer at that point.

Analyzing the business requirements

One of the key messages I delivered in the workshop to the participating members, was that when I am presented with an Excel file used by the customer, I never see it as being part of the solution.

Instead of thinking where this Excel file can be improved, I am focusing on understanding the business process and requirements, using the presented Excel file merely as a mean to better explain to me what is needed. It is just a helping accessory in my education process.

I know the customer needs a complete software solution designed from the ground-up. I don’t let his limitations of realizing any software tool, as being expressed in his Excel file, limit my professional understanding and thinking.

At that stage, I’d go into a detailed analysis of the business requirements, delivering a Business Requirements Analysis report to the customer, he needs to agree upon. A short snippet of this document is presented here:

This is the “contract” between us. He knows what to expect, I know what to deliver and how much it would cost.

I highlighted another important message to the workshop participants: the analysis phase is fully paid for. This is not a pre-job effort on my behalf. It may take anywhere from 5 to 30 and more hours and the report is of a very high value for the customer, regardless of who will be developing the solution based on that document.

For this project, given its key business purpose, I realized two key user interfaces needs to be planned, designed and implemented:

1. A quarter view of all running opportunities.
2. A single opportunity workbench offering a 360° view and interaction controls with a selected opportunity.

These two main work centers are the key in streamlining the orchestrated and coordinated process of bringing opportunities to a successful closure.

The Excel VBA Solution Delivered

I first presented my standard tables approach. From a usability point of view, it’s the same for this project, in which all of its data in maintained in the Excel file itself, and for projects that have an external database (such as MySQL) for their persistence layer.

I typically offer both filtering selectors (in orange) for some columns and a free search box (blue). This allows for quick location of the desired record.

Action buttons apply to selected table-rows (for updating a record, for example).

The Add/Update form typically looks like this:

This form has validations built in with an error/warning message bar at the bottom, served by a Class Module object. I explain Class Modules and demonstrate with VBA code how to implement them in a series of Blog posts about Object Oriented Programming with Excel VBA, starting here.

The below is the Opportunity Workbench, carefully planned to show ALL aspects of a single opportunity in a glance.

Note the buttons that allow opening the update forms for the opportunity, challenges, milestones and tasks – instantly from here, without having to navigate to the respective tab.

As a milestone may be associated with a challenge, and a task may be associated with a milestone, selecting a challenge instantly filters to show only its milestones. Selecting a milestone instantly filters to show only its associated tasks.

The rendering of the milestones is done by refreshing a shadow-Worksheet with the visible milestones’ IDs in the respective cells using an SQL query, and based on these values I create the visible title and description as a hover-on comment in the visible cells on the workbench Worksheet. This is all done with VBA code, of course. In the following function you can see how I construct the SQL statement and call for its results to populate an array that will later be thrown to the shadow-Worksheet:

Function FillOppMilestonesArray(ByVal lngOppNumber As Long, lngChallenge As Long, ByRef arrMilestones() As Variant) As Integer

    Dim strSQL As String


strSQL = "SELECT MIL.[Mile ID], MIL.[Description], MIL.[Challenge ID],
MIL.[Challenge Description], MIL.[Week Planned], MIL.[Week Target],
MIL.[Comments]"

    strSQL = strSQL & " FROM " & GetDBFrom("Opp Milestones", "OpportunitiesMilestonesTable") & " AS MIL"

    strSQL = strSQL & " WHERE MIL.[Opp Number]=" & lngOppNumber

    If (lngChallenge > 0) Then

        strSQL = strSQL & " AND MIL.[Challenge ID]=" & lngChallenge

    End If

    FillOppMilestonesArray = LoadSQLQueryIntoArray(strSQL, arrMilestones)

End Function

The tasks time frame bars are conditional formatting on values calculated using formulas in hidden helper-columns.

Fetching the tasks’ ID’s into a hidden helper-column is also done using formulas, this time an array formula. This goes in cell R35 and below:

{=INDEX(OpportunitiesTasksTable[Task
ID],SMALL(IF(OpportunitiesTasksTable[Opp
Number]=Selected_Opportunity_Number,ROW(OpportunitiesTasksTable[Opp
Number])-ROW(OpportunitiesTasksTable[#Headers])),ROWS(INDIRECT("Q$35:Q"
& ROW()))))}

Finally, the big-picture, quarterly view, is planned to show all running opportunities on a chart (with another overlay-ed chart for the orange “Today” line). The chart data is prepared with a SQL statement fetching all running opportunities that are displayed in the table below the chart.

Here, too, I’m not only using this as a presentation dashboard, but rather allowing the selection of a task and jumping to its workbench directly.

Hidden helper-columns to the right of the visible opportunities table also calculate all data required for drawing the chart.

As a bonus, I offered a selector on top for the user to see how the previous quarter or the next quarter looks like.

Final Words

I hope this gives you some understanding of the thought process I’m going through for every project, and possibly some additional ideas for the implementation.

The key to remember is that I am conceptualizing the solution from scratch to best serve the business value expected (and beyond).

For this, I always keep in mind the business goal statement I carefully crafted, as shown in the business requirements analysis report:

The main purpose of the required product is to streamline the presales process, resolving obstacles efficiently and driving the opportunity to a timely closing, i.e., before the current quarter ends, while providing a visual workbench for day-to-day sales execution activities.

Please share this post with your friends and colleagues – they really want this just as you did.