VBA Automation – A Case Study

VBA is the programming language, which is used for writing macros in Microsoft Excel and all other Microsoft Office applications. Learning VBA code allows users to automate tasks, from simple ones in Excel such as entering and editing cell values, to interacting between other apps.

VBA is used within Microsoft Office apps to perform actions. Writing VBA code is predominantly done with the purpose of automating tasks in Office. VBA is effective when it comes to repetitive solutions for formatting and correction issues. As an example, changing the styling of paragraphs in Microsoft Word documents, or reformatting tables that were pasted from Microsoft Excel into Word or Outlook. If there is a change that you have to manually make more than a few times, by automating process using VBA this could ultimately save you time, effort and stress.

VBA is most commonly used to automate Excel processes (read more about Excel automation here) but as previously mentioned, all Microsoft Office apps support VBA. The ‘objects’ the app contains can be accessed by Visual Basic. These objects can be manipulated by invoking ‘methods’ on the object, or by altering the object’s ‘properties’.

Here, we have detailed a case study of a client that we worked with, that required an automation solution. While this case study doesn’t purely focus on Excel VBA specifically, it’s important to understand how VBA works on a basic level to make sense of this particular XL Intelligence case study. This particular case involved XL Intelligence developing an automation solution for a client using VBA in Microsoft Word.

You can read about an Excel automation case study here.

The Client

The client is a UK-based energy, sustainability and utilities solutions company. They offer standalone services as well as outsourced and integrated energy solutions. This particular client is a part of a larger facilities management conglomerate. XL Intelligence provided a broad range of consultancy services to this client, dealing directly with their marketing manager.

The Problem

The B2B sales team within this organisation create standard proposals for their customers, i.e. companies seeking a variety of energy solution products. Before XL Intelligence were hired as consultants, the adopted practice was use Microsoft Word for each proposal, and manually add the products they were after. With no automated process to speak of, this client simply copied and pasted products from previous proposals, for each new one.

The introductory copy on each proposal would depend on whether the customer in question was new or existing, which was also copied and pasted. Proposals themselves could contain up to 10 different products the client sold. Each product contained specific optional extras that the client’s customers could opt for. In some cases, there could be as many as 16 optional extras PER product. Not to mention the fact that ONE product could span as many as 15 pages. It’s fair to say that this process became very difficult to manage effectively.

As this was process was purely manual, errors cropped up very frequently. There was no mechanism in place to automatically replace customer names, which meant that they were often incorrectly referenced throughout the proposals. There were formatting and numbering errors across many proposals, many of which also contained irrelevant products that were not required for that particular customer.

The company required an intuitive solution which would automate processes in Word.

The Solution

XL Intelligence created a data entry form which customers could fill in. The information required on the data form was simple and non-intrusive, such as organisation name, address and contact details, and whether the customer was new or existing. You can see from the images that the Products tab contained straightforward methods to generate proposals by selecting products and optional extras from drop-down lists and check boxes.

Once the required information was collected via this form, all that was required was for the user to click Submit and the proposal was automatically produced.

This automation solution allowed for a seamless and efficient process of creating proposals in Word from this energy company. No longer did they have to spend time manually editing customer names or copying and pasting different products and extras from other Word documents. The XL Intelligence consultants had identified particular objects, methods and properties within Microsoft programs to generate reports using VBA in the company’s desired app. Because the solution was developed within Word directly, this meant that the development costs were significantly reduced.