Some of the most versatile tools available to users of Excel are macros. These are essentially recorded sequences of actions designed to automate repetitive tasks within the software applications they are running in.

Historically, these have been generated using Visual Basic for Applications (VBA) code, a programming language that Microsoft Office is moving away from, but is still widely used across various sectors – including accountancy – despite security flaws.

As a result of its broad use, generative AI tools, particularly Copilot, are quite good at generating VBA code that could be used to create macros very quickly.

James Wilkinson – Microsoft 365 Copilot Trainer and Adviser at Newcastle-based IQ IT, coaches organisations on how to make the most of the 365 suite. He explains that Copilot’s AI capabilities enable accountants to rustle up macros for tackling tricky and potentially time-consuming challenges on Excel.

“The possibilities of what you can create with Copilot-generated VBA are pretty much endless,” Wilkinson says. “The limitations are set by the user’s own needs.”

That said, he does have one caveat. “If you ask Copilot to write or modify VBA within a complex web of existing macros, it may not fully understand the dependencies in that web,” he says. “That could lead Copilot to generate something that breaks.”

With that rule of the road firmly in mind, let’s look at which sorts of finance tasks macros might be able to address.

Good uses of Copilot-generated macros

One recent use case that Wilkinson explored relates to an Excel invoice report that a client pulled out of Xero. “The report contained a series of descriptions for the relevant invoices,” he explains. “The aim was for the descriptions to be presented in a set format: a string of text, followed by a date.

“Now, because the descriptions had been filled in by multiple people, some of the dates were left out, and there were variations in the date format. For example, some would say 19 June, while others said June 19th, the abbreviated 19 JUN, or the numerical 19.06.25.”

To resolve the issue, Wilkinson used Copilot to write a VBA macro script that would sweep through the column of descriptions and harmonise the date format. Along the way, it would use information from other parts of the invoices to patch in any missing dates. The process made the descriptions completely uniform, giving the client greater clarity on when the invoices had landed.

Another macro he wrote was devised for a similar purpose – to fill in supplier names in another Excel report that contained a series of brief descriptions.

Wilkinson also suggests that macros could be used to standardise formatting in a 12-sheet profit and loss workbook, though this could also be completed with Power Query.  “You could write a macro that will loop through all 12 sheets in the workbook, map out all the variant headers and fully standardise the structure.”

Considerations when using Copilot

If you’re using Copilot to either generate VBA code or moderate existing lines of code, it should be noted that this code will need to be checked for errors, especially with something as potentially machine-breaking as VBA.

Take time to learn about how Copilot works and how you can use it effectively before you start applying it to any tasks. You will need to learn how to prompt effectively to improve the results that you get from the tool. Even then, its work will require checking. You can ask Copilot to clarify requirements before generating code, but again, be sure not to over-rely on it.

Wilkinson says that he treats Copilot as a collaborator, rather than offloading the task entirely to the tool. The process will take a bit of back and forth, he explains. “Not ‘Do this,’ but, ‘Here’s the challenge I’ve set – now, what do you need from me? What can I provide by way of further information that will help you to get to the finish line?’ And then it will come back to you with its information requests, and you can keep going through that iterative journey.”

Alternatively, you could upload the entire problem Excel document to Copilot and use it to interrogate the issue. Mindful of data security, Wilkinson strongly recommends using the subscription-based, rather than public, version of Copilot for this approach.

If you have a Microsoft 365 enterprise or business license, you will have access to the enterprise-secure version of Copilot Chat, which will allow you to upload to Copilot without risking the security of the data.

“Again,” Wilkinson says, “once you have uploaded the file, encourage Copilot to ask questions about the content that will help it deliver the right outcome.”

If you’re struggling to get the result that you want, Wilkinson suggests an ‘AI Tag Team’ approach to testing Copilot outputs: “If you’re getting an error,” he says, “you can upload the Excel document and the sample VBA script to ChatGPT and/or Claude, and ask them to troubleshoot the code.

“So, if you get stuck on one assistant, you can cross-reference your documents and macros with other AI tools and ask them to sense check the material. With input from multiple assistants, you can be quite confident about the final result.”

James Wilkinson – Microsoft 365 Copilot Trainer and Adviser at Newcastle-based IQ IT, coaches organisations on how to make the most of the 365 suite. He explains that Copilot’s AI capabilities enable accountants to rustle up macros for tackling tricky and potentially time-consuming challenges on Excel.

“The possibilities of what you can create with Copilot-generated VBA are pretty much endless,” Wilkinson says. “The limitations are set by the user’s own needs.”

That said, he does have one caveat. “If you ask Copilot to write or modify VBA within a complex web of existing macros, it may not fully understand the dependencies in that web,” he says. “That could lead Copilot to generate something that breaks.”

With that rule of the road firmly in mind, let’s look at which sorts of finance tasks macros might be able to address.

Good uses of Copilot-generated macros

One recent use case that Wilkinson explored relates to an Excel invoice report that a client pulled out of Xero. “The report contained a series of descriptions for the relevant invoices,” he explains. “The aim was for the descriptions to be presented in a set format: a string of text, followed by a date.

“Now, because the descriptions had been filled in by multiple people, some of the dates were left out, and there were variations in the date format. For example, some would say 19 June, while others said June 19th, the abbreviated 19 JUN, or the numerical 19.06.25.”

To resolve the issue, Wilkinson used Copilot to write a VBA macro script that would sweep through the column of descriptions and harmonise the date format. Along the way, it would use information from other parts of the invoices to patch in any missing dates. The process made the descriptions completely uniform, giving the client greater clarity on when the invoices had landed.

Another macro he wrote was devised for a similar purpose – to fill in supplier names in another Excel report that contained a series of brief descriptions.

Wilkinson also suggests that macros could be used to standardise formatting in a 12-sheet profit and loss workbook, though this could also be completed with Power Query.  “You could write a macro that will loop through all 12 sheets in the workbook, map out all the variant headers and fully standardise the structure.”

Considerations when using Copilot

If you’re using Copilot to either generate VBA code or moderate existing lines of code, it should be noted that this code will need to be checked for errors, especially with something as potentially machine-breaking as VBA.

Take time to learn about how Copilot works and how you can use it effectively before you start applying it to any tasks. You will need to learn how to prompt effectively to improve the results that you get from the tool. Even then, its work will require checking. You can ask Copilot to clarify requirements before generating code, but again, be sure not to over-rely on it.

Wilkinson says that he treats Copilot as a collaborator, rather than offloading the task entirely to the tool. The process will take a bit of back and forth, he explains. “Not ‘Do this,’ but, ‘Here’s the challenge I’ve set – now, what do you need from me? What can I provide by way of further information that will help you to get to the finish line?’ And then it will come back to you with its information requests, and you can keep going through that iterative journey.”

Alternatively, you could upload the entire problem Excel document to Copilot and use it to interrogate the issue. Mindful of data security, Wilkinson strongly recommends using the subscription-based, rather than public, version of Copilot for this approach.

If you have a Microsoft 365 enterprise or business license, you will have access to the enterprise-secure version of Copilot Chat, which will allow you to upload to Copilot without risking the security of the data.

“Again,” Wilkinson says, “once you have uploaded the file, encourage Copilot to ask questions about the content that will help it deliver the right outcome.”

If you’re struggling to get the result that you want, Wilkinson suggests an ‘AI Tag Team’ approach to testing Copilot outputs: “If you’re getting an error,” he says, “you can upload the Excel document and the sample VBA script to ChatGPT and/or Claude, and ask them to troubleshoot the code.

“So, if you get stuck on one assistant, you can cross-reference your documents and macros with other AI tools and ask them to sense check the material. With input from multiple assistants, you can be quite confident about the final result.”