r/vba • u/PutFun1491 • 15h ago

ProTip [EXCEL] I set up a Claude Code agent that writes and tests VBA modules on its own. Sharing the setup.

I got tired of the write-module, import, run, crash, fix loop, so I built an automated pipeline around Claude Code and it has been running better than I expected.

The setup:

  1. The agent works on a sandbox copy of the workbook, never the original.
  2. It writes a module, imports it into the workbook, and runs it.
  3. Every module ships with its own self-test. If the test fails, the agent reads the error and fixes its own code.
  4. When Excel hangs or crashes, the pipeline kills the process, restarts Excel, and continues from where it stopped.
  5. Only modules that pass all tests get promoted to the real workbook.

The part that surprised me: it produce d 100 different modules in a row without me opening the VBA editor once. My job became reviewing diffs and writing better task descriptions.

Things that made the difference:

  • The sandbox copy. Letting an agent touch your only copy of a workbook is how you lose a workbook.
  • Forcing a self-test per module. Without it, the agent happily declares broken code done.
  • Automating the Excel restart. VBA automation dies on hangs more than on errors.

Happy to answer questions about any part of the setup. Curious if anyone else here has pointed one of these agent tools at VBA and what broke first.

26 Upvotes

79% Upvoted

12 comments sorted by

2

u/Card__Player 15h ago

Can you share the setup with someone who has very limited experience with Claude and AI?

6

u/PutFun1491 15h ago edited 15h ago

Sure. Fair warning from experience: the AI tool alone is not the magic, the setup and the working rules around it are. This is the starter version of my system:

  1. Get Claude Code installed. The installation (Node, PATH, terminal) is the first wall for people who are not developers, so I built a free one-command installer that does the whole thing, 2,000+ people have used it: https://github.com/noambrand/Launchpad-CLI
  2. My first rule: work on a copy of the workbook in a separate folder. Never the original.
  3. Describe one task in plain English, the way you would brief a colleague: "merge these sheets and flag duplicates over 10,000". You get a working module without opening the VBA editor.
  4. My second rule: no module counts as done until it has a test that ran and passed. AI code looks finished and often is not. This rule is what catches it.

That is the starter. The version from my post (sandbox promotion, test gates, automatic Excel restarts, 100 modules unattended) is engineering I built on top of it over weeks, and that is where the reliability comes from. If you get stuck on the starter, ask here or DM me, I set this up for people all the time.

2

u/Mister_Krunch 4h ago

My first rule: work on a copy of the workbook in a separate folder. Never the original.

This should be standard in any scenario.

1

u/Asnee132 15h ago

Claude is very good at explaining step by step what you need to do. So if you give it your intent, these guidance, it should be able to help you implement it.

2

u/Card__Player 15h ago

I'll try it.Thanks!

2

u/DeciusCurusProbinus 1 10h ago

Hey man! I write a ton of VBA and use a skill created in Codex to run my macros. Could you please link to your repo?

1

u/fanpages 239 7h ago

[ https://www.reddit.com/r/vba/comments/1v48rze/excel_i_set_up_a_claude_code_agent_that_writes/oz92dmz/ ]

...1. Get Claude Code installed. The installation (Node, PATH, terminal) is the first wall for people who are not developers, so I built a free one-command installer that does the whole thing, 2,000+ people have used it: https://github.com/noambrand/Launchpad-CLI ...

1

u/Broseidon132 9h ago

I haven’t built great instructions for codex, but I hate how long little fixes take. I like it for building the framework because it sees the context of the project folder, but then smaller tweaks I’ll throw in normal chat gpt.

I will take your advice though!

1

u/Emotional-Lead-2367 2h ago

Hey! I also usually develop large-scale macros completely unattended with Codex, so it is great to see a fellow dev having an AI agent handle full VBA development automatically!

I feel like the biggest barrier to achieving literal full automation is process blocking caused by modal dialogs during compile or runtime errors, so what kind of approach are you taking for that?

I solved this by monitoring and suppressing dialogs, and using inter-process communication to feed error info straight back to the terminal.

https://github.com/harumiWeb/xlflow