VisibleThread – Language Analysis Platform

Generate a full compliance matrix in Excel with macros

With VisibleThread you can generate a customized compliance matrix in minutes with macros.
4 min read

Kees Hendrickx

Shredding RFPs from VT Docs

Out of the box, VT Docs lets you shred any PDF or Word doc in seconds. You’re ready to develop a full compliance matrix, adding extra columns etc.

For instance, you might add a column indicating whether your company complies with the requirement, a column showing which teams need to review the requirement, a column indicating risk and so on. This gets you to a full compliance matrix.

However, did you know you can use embedded macros to generate more customized outputs? And short cut many of these manual steps.

This is where the real power of macros and VT Docs comes into play.

Why extend VT Docs shreds with macros?

VT Docs dramatically speeds up the shredding process to give you a great starting point for a Compliance Matrix. All that manual copy-pasting of requirements into spreadsheets, replaced by a single-click. Hours, if not days shortened to minutes

Shredding the RFP/SOW/PWS went from between 1-1.5 days to 1-2 hours

Fred Vlchek, Director of Business Development Operations, Pragmatics

Here’s an example of VT Docs shredding the 185 page, multi-billion-dollar CIO-SP4 RFP.

This took 2 minutes to generate in VT Docs.

However, we might now want to add new columns, for example:

  • Are we compliant (Y/N) 
  • Name of Technical Reviewer
  • Proposal Section 
  • Team that needs to review 
  • Level of Risk 

And maybe we want to further differentiate these columns with different font sizes or background color-coding for column headings. Maybe we want to add column filters, resize columns etc.

All those manual steps that you might do in Excel once you generate the initial output.

Wouldn’t it be great if we could automate those too. Well, you can, using excel macros.

Here is a flavor of the things that we see customers using macros for:

  • Adding specific columns and filters to the default shred output.
  • Adding color formatting to cells, rows, columns, and text. For example, bold any date formats.
  • Adding new sheets to the output with a separate layout. Pulling text from other cells or columns and placing that text in other parts of the output.

Bottom-line, automating these tasks with macros gets you closer to a finalized output. And frees you to concentrate on more value-add activities during the proposal process.

And you can go even further.

In our 2021 webinar with Leonardo DRS for example, they used Macros to tailor the standard outputs to drive better quality assurance coverage in their QA (Quality Assurance) process.

How? Using a marco, they automatically added requirements coverage tracking fields to the standard outputs, thereby streamlining how they generated a requirements traceability matrix.

If you didn’t have a chance to see this webinar, it’s an excellent example of the flexibility of macros.

Let’s briefly explain what macros are.

Here’s how Microsoft explain it.

“If you have repeated tasks in Microsoft Excel, you can create a macro to automate those tasks. A macro is an action or set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes.” 

So, macros automate sets of manual steps that you might normally do in Excel. And they save you valuable time when doing these things on a regular basis.

Macros in action, an example Compliance Matrix

In this section we’ll show you an example macro that one customer used. As some background, this customer is an equipment manufacturer in the communications space.

In their case, several teams need to review & sign off on RFP requirements. These teams include:

  • Pricing 
  • Compliance 
  • Engineering 
  • Operations 
  • Contracts 
  • Product Management 
  • Sales and 
  • Training 

Without macros, using the standard VT Docs outputs:

  • They would add the additional columns manually to associate responsibility to different teams and departments and
  • color-code the matrix columns in line with their preferred color-scheme.

This customer worked with the VisibleThread Customer Support (CS) team develop a macro to populate these columns automatically.

Let’s see how they now do it.

Step 1: When generating the output, there is an option “Include Macros to customize output”. Check that box. Here’s what it looks like.

 

 

Now hit “Generate Matrix” to get an output like the below.

 

 

Step 2: It’s identical to a normal output, except there is a security warning with an option to “Enable Content”

Hit “Enable Content”, and then click “VisibleThread Macros…”

NOTE: If you run into any issues OR don’t see this Enable Content option, see the later section in this blog post titled “Troubleshooting: Some recent changes to how Microsoft treat macros”

 

Step 3: Choose the macro you want to run, and click “Run Macro”. In our example below, we’re running the “ComplianceMatrixMacro” macro.

 

 

Step 4: The result is a full compliance matrix completed with all columns, highlighted cells, instructions, and filters.

Here’s how it looks.

 

Prior to adopting the macro approach, this proposal team were previously adding these manually every time. Using macros, they automated repetitive tasks that would have previously taken up time and resources.

As a final step, this customer then adds this document to a shared folder. This way all the relevant teams can go to their sections and see their responsibilities. 

How to configure VT Docs to use macros   

VT Docs provides an option to upload a “base” excel workbook that contains the desired macros.

Here is a screenshot showing where to set the macro enabled Excel Workbook.

And here’s a short 2-minute video showing how you can enable and use macros. 

How to create a macro in Microsoft Excel   

You can develop macros yourself or equally the VisibleThread CS team can help. In the example above our CS team helped this customer.

Watch a clip below from our VisibleThread Users Conference Virtual Sessions explaining how you can create a macro in Microsoft Excel to use in VT Docs:  

 

 

Troubleshooting: Some recent changes to how Microsoft treat macros

Microsoft have recently changed how they handle macro-enabled files. So, once you generate from VT Docs, you must change the file’s security properties.

Locate the generated file in your downloads folder. Open the properties dialog by right clicking the file. And under the General tab, check the “Unblock” option next to Security.

Now you’re good to go. Just open the excel file as normal.

Conclusion

You may be using VT Docs in your proposal process workflow already. But using macros takes it to the next level. And you can streamline your process even further.

Let’s summarize the key benefits.

  1. Efficiency: Using macros with VT Docs saves you time. Automating repeating tasks streamlines your process helping you create your compliance matrix faster. 
  2. Reduce Errors: Reduce errors by automating the tedious formatting tasks. Such as adding columns, colours to cells, rows and text. And copy and paste text from other parts of the output. 
  3. Process Improvement & Standardized Outputs: Macros are an excellent way to standardize how you use the VT Docs compliance matrix across your proposal teams, leading to improved higher quality processes. 

Contact us today to see how VT can help you improve your requirements process.