As businesses evolve and develop, the need to track different information for clients, projects or a myriad of other tasks emerge. Using VBA to dynamically create excel workbooks will save time, reduce risk and help consistency and quality.
Sick of reading? Watch the video toward the bottom of this post.
By using VBA to dynamically create Excel Workbooks subsequent tasks like reporting and data analytics become much easier. See Using Excel VBA to Consolidate Reporting for a worked example on just how easy reporting can become.
The Case for Excel Workbooks
Excel workbooks, given their flexibility and familiarity, are an easy, almost necessary option for many businesses.
Typically, members of a team create a range of workbooks. Maybe one file per month, per P&L, per Client, per Project or who knows. Files can be complex.
With more files and users the risk of issues grow.
Creation and management of Excel files becomes a full-time job. File issues cause all sorts of problems. Trying to generate consolidated reports or meaningful consistent data from multiple files can take enormous resources.
As problems multiply the justification for spending big on specialist software or software development becomes compelling. However, apart from money, the specialist software route also takes time and introduces other challenges. A good alternative, for the medium term, is an application built in VBA to dynamically create Excel workbooks.
The following example demonstrates the simplicity and benefits of an Excel VBA solution.
VBA to Dynamically Create Excel Workbooks - Worked Example
This worked example is based on a real-world project for an international media content business. It has been somewhat changed and simplified for this post.
The Situation:
- A company has several major clients, and multiple minor clients.
- Clients award the company packages of work for single or multiple locations.
- Each package/location combination results in a final deliverable but can include a draft and multiple trial versions.
- Draft, trial and final versions all have between 10 and 60 work steps depending on the options selected by the client.
- Each work step takes from 60 minutes to several days and some work steps can be concurrent.
- Work steps are completed internally or by different 3rd parties.
- A typical package of work asks for deliverables across 3 locations with a draft, 2 trials and final version for each. That's 3 x 4 versions x 40 steps or about 480 work steps.
- The timing, progress and status of work steps is managed by the company in Excel.
Sounds complicated.
The Typical (pre VBA) Solution
Most competent Excel users could put together a spreadsheet to manage the above challenge. That is, track all the work steps, actual dates versus plan dates, and so on. So far, so good.
Now lets say business picks up. Three big clients all award new packages, several other clients award small packages and those big clients award even more.
With each new work package workbooks are copied and modified or newly developed. The business ends up with many similar, complex workbooks. Not so good.
This is risky. Some files will work better than others. Some may have issues. Setting up a new workbook for a new work package becomes a challenge in itself.
The reporting challenge also grows. Determining how all work packages are progressing will need different staff to generate reports, or give briefings, or what ever other painful process gets developed. Determining statistics on a specific step across projects becomes unworkable.
The odds of a major client issue are high.
The Excel VBA Solution
VBA to dynamically create Excel Workbooks delivers consistent, high quality workbooks. Creation occurs with 1 click. The process also delivers much better underlying data quality, reporting flexibility and control.
So what does it look like.
Effectively the solution is an app that creates workbooks. That is, an Excel workbook with VBA code. VBA is an object oriented programming language, so once you go past basic 'macros' it becomes very powerful, especially within its native office environment.
My preferred approach is to make life easy for users and reduce the likelihood of errors. To achieve this I typically capture all new project details with a form using drop downs and check boxes. Supporting sheets contain a shell that makes full use of Excel functions and functionality to manage all possible work steps and related reporting. The sophisticated VBA code automatically builds each new workbook using the existing shell and project selections. All new workbooks have the same structure, logic and naming.
Video Demonstration
Watch this short video demonstration of an example 'Project Setup App' to see how it works.
The following images highlight the major steps:
An Example Shell
The form to capture details:
An example completed form
The Resulting Dynamically Created Workbook.
With each new work package the business can now run the project setup app and have a new tailored workbook in seconds. Each new workbook will look, feel and work the same way.
The benefits and scalability of this approach are significant. These include consistent data, greater ability to share work and consolidated reporting.
This looks great. Keen to learn more.