Features March/April 2024

Accelerating VBA Word Macro Development with AI

By Katherine Munro | Member

A ChatGPT-4 Assisted Journey.

In an era where time and efficiency are paramount, leveraging technology can make a significant difference in productivity. This case study explores a unique collaboration I had with ChatGPT-4, an artificial intelligence (AI) model, in developing Visual Basic for Applications (VBA) macros for Microsoft® Word documents. The success of this project offers valuable insights into how AI can be utilized for real-time coding assistance.

The Challenge

MadCap Flare is an excellent tool for creating technical documentation, outputting content to a range of formats including HTML5, PDF, and Word. However, Flare’s Word output has some shortcomings, including inability to include background images. Such images in headers and footers are essential for corporate branding of documents. Other issues include some inconsistent style application, layout of content in table cells, and alignment in a document’s table of contents.

Although fixing these issues manually post-build from MadCap Flare would of course be possible, the process would be so laborious and tedious at scale, we had previously decided that our team would not provide content in Word format. This decision disappointed some consultants in our company who had valid use cases for customizing official product documentation for training purposes or specific customer implementations.

A macro is a series of commands that can be grouped together as a single command to accomplish a task automatically. Automating repetitive tasks leads to not only efficient use of time and resources, but also consistency of application and minimization of errors. However, writing Word macros requires familiarity with VBA scripting and the Microsoft Word object model. I had some experience writing simple Word macros but could not have written macros for these Word issues without significant research or help from an experienced VBA developer.

Solution Development with ChatGPT-4

This case study focuses on the development of the first macro for adding corporate branding images to headers and footers of the document, with the requirement that images and positioning would be different for the title page, back cover, and content pages.

I started with context and goal setting with a simplified scope.

ChatGPT responded with a simple example of VBA code, followed by notes about editing the paths to the images and how to use the InchesToPoints function.

I tested this macro and gave feedback, leading to the first refinement.

That suggestion worked to make the images visible on the first page. However, the footer image was positioned relative to the left and right page margins instead of the left and right edges of the page.

After a couple of failed attempts to make progress, I provided this feedback from testing.

With each attempt, ChatGPT not only provided a full version of the VBA code, but also included an explanation of how the code should work. Nevertheless, this called for persistence, as I still did not have a solution.

Several attempts were required to solve the footer position, and ChatGPT made some code errors, which became evident when compiling the script in Word. ChatGPT was quick to address this feedback too.

At this point, I returned to the best solution so far and experimented using different values.

However, remember that I started with a simplified scope. Now was the time to introduce more complexity.

Further iterations ensued, ultimately leading to this full solution.

Based on the success of development of this first macro, I took a similar approach for macros to solve the other formatting and layout issues.

Reflection

The following principles were key to the success of this collaboration.

Clear Communication: Both the problem and feedback were clearly communicated, enabling precise adjustments. Open communication and collaboration with ChatGPT were essential in understanding the problem and iterating toward the solution.

Collaborative Iteration: The iterative process allowed for testing and refinement, leading to tailored solutions. We were both able to adapt and explore different methods to achieve the desired outcome. Shared success was achieved, with contributions from both ChatGPT and me.

Attention to Detail: The origin for positioning the footer image was discovered to be different from the initial assumption, leading to adjustments in the code. A deep understanding of Word’s behavior, including positioning and aspect ratios, and attention to specific details ensured a macro that met the exact requirements.

Experimentation: The iterative process involved experimentation with different positioning techniques to achieve the desired result. My willingness to experiment and provide feedback was instrumental in navigating challenges and achieving precise results.

Persistence: The iterative process continued until the exact requirements were met, reflecting a commitment to precision and quality. Persistence in troubleshooting and adaptation to changing requirements allowed for the successful completion of the macros.

Results

The final suite of macros addressed the unique requirements of transforming the Word output from MadCap Flare into a deliverable suitable for immediate use or customization. Collaborating with ChatGPT led to significant time and cost savings, turning a potentially multi-day development effort into just a few hours of interactive work.

The innovation and efficiency demonstrated in this project were met with acclaim by team members and managers, showcasing the power of AI-driven collaboration. The results of this project will enable other team members to accelerate the transformation of existing product documentation in MadCap Flare, given that the team can now support quality Word outputs.

Conclusion

Communication, iteration, attention to detail, experimentation, and persistence were key elements of this success. By embracing these principles, I was able to compress potentially days of development effort into just a few hours, leveraging ChatGPT as a tool to achieve impressive results.

This case study not only highlights the successful development of specific macros but also illustrates a broader application of AI in accelerating and enhancing the software development process. ChatGPT can act as a virtual development partner, capable of understanding complex requirements, providing solutions, and adapting to feedback. The case study stands as a testament to the power of collaboration, adaptability, and innovation in working with AI tools in the digital age.


Katherine Munro is the Principal Technical Writer at Datamine, where she leads and mentors a team of technical writers, documents software for the mining industry, and is embedded in two Agile software development teams. Katherine is also an active contributor to Datamine’s Generative AI community of practice.

Outside her role at Datamine, Katherine teaches German at the Institute of Modern Languages (at The University of Queensland in St Lucia, Australia), sings with Canticum Chamber Choir, volunteers with Thread Together Queensland, develops websites, and is a member of a community garden in Brisbane, Australia. katherine.munro@dataminesoftware.com