Accelerating Training Content Development with VBA

This blog post describes how you can use automation in training content development projects to deliver high quality content efficiently. Microsoft Office is one of the preferred software application suites for developing training content. Visual Basic for Applications or VBA is a powerful technology built into Microsoft Office products. Instructional Designers can use this technology to develop automation solutions that can speed up the training content development process and improve the quality of training deliverables.

1 Introduction

Developing training content is a rewarding, but an effort-intensive activity with several manual tasks. Training content development involves several days of researching content, authoring the content, and then hours of proofreading, formatting, and integrating the content. The content development process becomes more complex and challenging when there are multiple authors, multiple reviewers, multiple formats, and shorter time frames available to develop the content.

This blog post describes how you can leverage VBA automation to address these complexities and deliver high quality training content rapidly.

2 Challenges Faced in Training Content Development Projects

Microsoft Office is usually the starting point for most training content development projects. Word and PowerPoint are used most often, but Excel and Access are also used for ancillary purposes. 

Training deliverables can take the shape of e-learning, simulations, training presentations, participant guides, facilitator guides, job aids, terminology dictionaries, and so on. Although, templates and style guides are used to develop these deliverables, inconsistencies are inevitable due to the manual tasks involved in the development process. 

Fixing these inconsistencies and delivering good quality training material is one of the biggest challenges faced in the training content development process. Even a minor change that needs to be replicated across a long document will be a huge effort that can derail the project schedule. For example, manually changing the size of an icon that is used across a long document is a huge maintenance effort.

3 Automation—The Savior

Although, the training content development process is fraught with manual tasks, automation can help Instructional Designers to work smartly and overcome the seemingly insurmountable problems associated with the manual work. Microsoft Office is far more powerful than it is known to be. It has a very powerful, yet untapped technology running under the hood. This technology is called VBA or Visual Basic for Applications.

VBA is a programming language available in all Microsoft Office products. It can be used to automate several manual tasks performed in Office applications, such as Word, Excel, PowerPoint, Access, and Outlook. With basic knowledge of programming, Instructional Designers can easily write automation scripts or macros to automate the repetitive and manual tasks in Office applications. Moreover, simple macros can be recorded without having any knowledge of programming!

4 Benefits of Using VBA for Automation

The benefits of using VBA for automation are manifold:
  • VBA helps in easily exchanging data between Office applications, such as sending data from Word to Excel.
  • VBA is built into all Office applications. We need not invest in additional software development environment.
  • VBA is easy to learn and apply.
  • VBA helps Instructional Designers save several hours of development effort by automating manual tasks.
  • Automation also reduces the scope for human errors and improves the quality of deliverables.
  • VBA helps Instructional Designers to easily implement complex training requirements, thereby offering more value to clients and learners.

5 Real-Life Applications of VBA

This section showcases some real-world applications of the automation solutions developed using VBA.

5.1 Generating Audio Scripts

Audio is an important aspect of e-learning courses. The audio script is written in the e-learning storyboard, which is usually developed using Microsoft PowerPoint. After the script is finalized, it is transferred to an audio script document, which is usually in the Word format. The audio script document is then sent to a recording studio for recording the audio files.

Transferring audio script from the PowerPoint storyboard to the Word document is a manual activity. An Instructional Designer manually copies the text from the presentation and pastes it into the audio script document. I have used VBA to automate this activity. The automation solution automatically transfers audio script from the PowerPoint storyboard to the audio script document in the Word format.

Figure 1: Generating audio scripts

5.2 Reviewing Content

I have developed a review tool using the VBA technology. The audience for this tool is any content reviewer who wants to perform a detailed review of training content, generate a defect tracker, analyze the defects, and define training programs for Instructional Designers.

5.3 Generating Defect Logs

Instructional Designers use Word and PowerPoint to develop training content. After developing the content, it is put through rigorous rounds of reviews by various stakeholders to ensure the training material is of excellent quality. The stakeholders use the review features within Word and PowerPoint to share their feedback with Instructional Designers.

It is very important to track and address all the comments to get timely approval on the content from the stakeholders. In order to do so, Instructional Designers generally document all the comments in an Excel sheet known as the defect log. Transferring the comments from PowerPoint presentations or Word documents to the Excel sheet is a manual activity, which involves copying each comment and then pasting it into the defect log. I have used VBA to automatically transfer the comments to the defect log. In addition, one can assign severity, priority, and root cause of each defect based on predefined rules.

Figure 2: Generating defect logs

5.4 Aligning and Resizing Tables and Graphics

Tables are often used by Instructional Designers in Word documents to format content and present information effectively to trainees. Similarly, graphics in the form of icons, infographics, and screenshots are used to present information in an interesting and intuitive way.

Since these tables and graphics are added manually, inconsistencies in size, position, and alignment are inevitable. Moreover, correcting these inconsistencies manually can be a herculean and imperfect task. This is another opportunity where I have leveraged the power of VBA and automated the formatting of tables and graphics. I have developed an automation script that can scan a document for any inconsistencies in tables and graphics and fix them based on the specifications provided in the code.

Figure 3: Aligning and resizing tables and graphics

5.5 Making Training Content Accessible

Clients often ask Instructional Designers to make the training content accessible to people with disabilities. The following are some of the techniques used to make content accessible:
  • Add alternative text to graphics
  • Add alternative text to tables
  • Specify the header row in tables
  • Add null value to decorative graphics
Training material can run into several hundred pages of content. It would be a slow and laborious task to go through each graphic and table in a document to manually apply these techniques. However, I have developed VBA automation scripts that can apply these techniques to a document within a matter of seconds.

Figure 4: Making training content accessible

5.6 Filling PDF Forms

Practice exercises are an essential component of training programs for large enterprise resource planning (ERP) software implementation projects. The trigger point for most of the use cases in practice exercises are the application forms filled and submitted by clients for requesting services.

In order to simulate real life scenarios, we provide filled forms to the trainees so that they can practice the use cases. The number of forms to be provided can easily run into hundreds due to the large number of trainees. The forms are usually in the PDF format and contain several fields to be filled with data.

It would be an enormous effort to manually fill data in each PDF form. I have automated this task using VBA. I can generate large number of XML files using VBA scripts and import the files into PDF forms to easily fill the fields within the forms. An Excel file acts as the source file for generating the XML files.

Figure 5: Filling PDF forms

5.7 Filling Word Forms

Some PDF forms do not allow us to save data in them. In such cases, I have converted the PDF forms to Word forms and then used VBA scripts to automatically fill data in the fields. In this case, XML files are not generated. I directly transfer data from the Excel files containing training data to the forms in the Word format.

Figure 6: Filling Word forms

5.8 Formatting Text Fields

When PDF forms are converted into Word forms, the text fields, radio buttons, and check boxes in the forms are lost in the process. We have to add these controls manually in the Word forms. The manual activity again leads to inconsistencies in the size, position, and font settings of the text fields. I have developed a VBA script that can batch format the text fields for consistency in their formatting.

Figure 7: Formatting text fields

5.9 Renaming Files

After the forms containing training data are developed, we need to rename all these files because each file is for a specific trainee. The naming convention used for the forms is <course acronym>_<session number><exercise number><form number>_<first name>_<last name>.DOCM. For example, AR_S2E10F3_John_Doe.DOCM. Manually renaming each form using this naming convention will be a time consuming and laborious activity. So, I developed a VBA script to batch rename the forms.

Figure 8: Renaming files

5.10 Generating Role-Based Word Documents

Instructional Designers develop role-based training in many projects. Role-based training can result in multiple training documents containing similar content to address the instructional needs of different roles. Creating role-based documents manually is a strenuous and lengthy activity. I have developed a VBA script to simplify and expedite this activity. Content for multiple roles is developed in a single master document. The VBA script generates multiple role-based documents from the single master document.

Figure 9: Generating role-based Word documents

6 Conclusion 

As you saw, the VBA technology that can improve the effectiveness and efficiency of Instructional Designers in developing high quality training content.

VBA helps Instructional Designers develop automation solutions to eliminate the slow and tiring manual tasks involved in training content development projects. This technology is available in all Microsoft Office products, which is one of the commonly used software application suites for developing training content. The efficiency gains achieved through automation ultimately helps Instructional Designers to deliver more value to the clients and trainees.

If you have applied VBA for other use cases, please leave a description about them in the comments section below.