As an Excel trainer, MVP, and tech enthusiast, I can tell you firsthand: a ton of VBA code is still out there. Enough that if you’re coming into certain sectors (hello, finance!), you’ll need to take VBA seriously as a skillset. Should you dive in and learn VBA end-to-end? Honestly, probably not… it’s in a slow decline and no longer being actively supported by Microsoft. But the reality is that many analysts still regularly encounter VBA.
Here’s the good news: this is exactly where generative AI shines. If you’re somewhat technical and already familiar with the business context and the problem you’re tackling, generative AI can give you that essential jump-start with VBA.
However, as of now, Copilot in Excel doesn’t quite offer the same effortless automation here that you might get with calculated columns or PivotTables. It occasionally falls short of fully automating VBA tasks.
That’s why, in this post, I’ll walk you through some straightforward workflows to effectively use Excel Copilot when working with VBA. You can follow along using the provided exercise file. Make sure you’ve uploaded it to your OneDrive so Copilot can access it.
We’ve got a workbook containing a small dataset on our sales staff, and we’d like to test-drive Excel Copilot to help automate some tasks and build handy VBA macros.
The worksheet has some mysterious blank rows that keep popping up…. unfortunately, a recurring headache. How about we create a quick macro to tidy this up instantly? Let’s fire up Copilot and see how it can help.
One quick reminder: although Copilot might seem limited to your current dataset, that’s not actually true. As a large language model, Copilot is trained on a massive amount of information and can tackle broader tasks too. Check out my earlier blog post on using Excel Copilot for tasks beyond spreadsheets:
I’ll ask Copilot if it can generate a macro for me to quickly remove all blank rows from the worksheet, and it’ll return something along these lines:
Unlike some of Excel’s other features, Copilot can’t execute and automate this macro on its own. We’ll have to manually add and run the macro ourselves.
It’s not all bad news, though. One nice thing is that Copilot can easily build upon and refine this macro based on further prompts. For instance, if we ask it to add comments explaining the code, it’ll do exactly that:
On the bright side, Copilot actually can “see” your worksheet data and directly reference it in macros, which is pretty cool! Let’s give it a shot. I’ll ask Copilot to create a macro that directly interacts with our data:
“Create a macro that generates a separate workbook for each distinct value in the region column, naming each workbook according to its corresponding region.”
Copilot will provide us with some code that we can then copy and paste into our VBA editor, and then see how well it works… or doesn’t.
When I paste this into the editor, I immediately notice some code highlighted in red. If you’ve spent any time working with code editors, you know seeing red usually isn’t good news. Between the potential formatting issues when copying and pasting and Excel Copilot’s limitations as a prompting environment, we’re bound to hit some bumps. So what’s our next move? How do we work around this?
This is exactly the kind of situation where using Copilot 365 is typically the better approach. Here, you’ll get a dedicated Gen AI prompt environment. It’s similar to ChatGPT, but with the added advantage of being able to securely upload, reference, and sandbox your files within your organization’s walls, eliminating the risk of data leaks.
If you’re new to Copilot 365, check out my post on how to use this tool as your personal Excel tutor:
To get started, head over to the Work mode in 365 Copilot and paste your prompt there. Sometimes you’ll get a solid answer even without referencing your dataset directly. But for greater precision and confidence, it’s best to refer explicitly to your actual data. You can easily do this by clicking the + icon to add content to your prompt:
After you’ve found and selected your file from your OneDrive folder, your prompt will look something like this:
Since 365 Copilot offers a more robust and general-purpose Gen AI interface, you’ll typically receive responses that are clearer, better structured, and easier to use. For instance, your VBA macro will be neatly presented in a highlighted code snippet, making it straightforward to copy directly to your clipboard and paste into the VBA editor. You’ll still need to manually add and run the macro yourself, though. (Don’t trust Copilot if it claims it automatically added it to your workbook! In my experience, that rarely ends well.)
So now, taking the code that 365 Copilot generated, let’s return to the VBA editor. This time, everything looks clean—no red errors—and the macro runs perfectly.
Even if you still encounter some errors, you’re in a much better place to troubleshoot here compared to Excel’s somewhat fragile Copilot interface.
If you think you’ll want to refer back to this workbook and continue building on your macros down the road, I’d recommend setting up a Copilot 365 Notebook. You can learn more about how to do that here:
If this seems like a one-off task and you’d prefer not to deal with the hassle of uploading the workbook into Copilot 365, you can always copy the macro generated in Excel Copilot and paste it directly into 365 Copilot. Doing this gives you the benefits of a more powerful, reliable environment and helps you skip some of the earlier steps.
Now, full disclosure: if you’re a seasoned VBA developer, you might roll your eyes a bit at the code quality here. Generative AI tends to be pretty verbose and redundant, and this code is no exception. If you’re aiming for something highly scalable and performant, you’ll likely need deeper VBA expertise.
While generative AI can assist with some of this, the examples here are mainly intended for quick, ad-hoc tasks rather than major development projects. But hey (sorry not sorry, purists) not everything needs to be production-grade to help people get their work done.
In this post, we’ve explored a few straightforward approaches to getting help with VBA macros using Copilot for Excel. While the process isn’t always seamless or intuitive, it’s still an invaluable resource, particularly if you’re stepping into VBA occasionally or for quick tasks. Plus, your data stays secure, since nothing you share with Copilot leaves your organization’s boundaries.
What questions do you have about using Copilot for VBA macros or Excel Copilot in general? Let me know in the comments.
Leave a Reply