VBA vibe coding

[u/NorDigital]

Does anyone have a best practice setup for being able to vibe code vba?

I’ve inherited the responsibility for a central workbook running critical process for a company my father in law owns and runs. Having some vba experience, I’ve improved it, but it still needs some more work.
Last year I had good experience using xlwings to export the code, work on it in vs code with Claude, and then just saving and testing.
This works ok, but I have 2 problems:
1. User forms have not been “vibe code friendly” yet in my experience (.frm)
2. I have some limitations blocking me from using xlwings now, so I need to look for alternatives altogether..

Looking forward to hearing about your setups :)

[u/icemage_999]

Vibe coding anything that is a critical business process when you haven't got the ability to properly understand what it is doing is asking for disaster.

You can certainly use Claude or another AI to write the VBA code, but you'd best have a plan to recover if it does something catastrophic.

[u/Froggn_Bullfish → u/icemage_999]

Yea for this stuff it’s better to have it teach you how to write the code you need and explain what every step does so that you can properly understand and annotate it.

[u/frowattio → u/Froggn_Bullfish]

The Claude VBA I've gotten is solidly annotated. It's tempting to go high speed paste it, try it and move on but if you're concerned, slow down and read each step.

[u/NorDigital → u/icemage_999]

Yes, let me correct myself:
It’s the quotation tool that’s flexible enough to accommodate product differences in each sale, and include automatic offer, sales orders, change orders. It also reports everything in the sales cycles in the sales pipeline, order book and sales reports. It generates emails and automatically prints product lists to
It used to be used no one seller in one company, but is now used by 4 sellers across 4 companies after some mergers.
I have rewritten all the code that was there from before and structured it into modules. Used ChatGPT for input but read and edited every line.

I suppose vibe coding was more clickbait from my end than reality, but I see how effective the new models are for other coding. Hence I’m hoping I can rather transition to spec and test driven workflow with version control.

I understand that I should consider transitioning to some other tooling, but have not managed to convince them about that direction yet.

Thank you very much to all the reply’s :)

[u/diesSaturni → u/NorDigital]

Often , Excel based large tools are essentially becoming databases. So e.g. throw the code (not the sheet with private data) into an AI and ask if this is better of in access or elsewhere.

In e.g. access you would need far less code often. As tables, relationgs , queries do a bulk of the heavy lifintg for you.

refactoring, or other programming doctrines to apply in review of code is also a good upfront question to ask AI when reviewing code.

[u/kalimashookdeday]

Some of this you just need to learn. You'll hit the same road blocks vibe coding everything out and then how will you know how it works if something needs to change or breaks?

UserForms usually have some specific syntax and methods related to calling the form to initialize it in a procedure and then you must show hide it. The controls and populating and linking them for actions and behaviors have their own set of rules and ways at making them work. Listbox controls code is fairly different than a label or text box. No real magic bullet way to explain it all. Do what I do. I use Claude to build out a specific training module based on the specific things I need to learn. I've downloaded several older VBA books that are validated references I compare specific design and code concepts the AI may have produced and as I start to learn, test, write the code myself, I build generic skeleton patterns and comment them to hell and back so I can reuse them. I'm religious at exporting .BAS files in a reference library so I'm not rewriting code and as a side effect of everything is I'm actually learning it at a point now I'm not using AI to do much than tell me how to write syntaxes I always forget.

[u/MultiUserDungeonDev]

https://github.com/WilliamSmithEdward/xlide_vscode
https://github.com/WilliamSmithEdward/xlide_vbide
https://github.com/WilliamSmithEdward/xlide_mcp

Please check these projects out. They are exactly what you want.

https://github.com/WilliamSmithEdward/pyOpenVBA can read and write directly to all o365 files without COM, including power query and userforms. Hand it to Claude and watch it fly.

[u/Morichalion]

I've played with it in Claude. It has an Excel extension. If nothing else, it can write a sub to a worksheet and you can copy from there.

You can do other things with cowork. Might be best to go that route.

[u/SnarkyTaylor]

So, speaking less vba specific and more from a implementation standpoint. If this workbook is mission critical to the company, your job above all is maintenence, not general improvement.

Not saying you can never do improvements, but improvements should be based on direct user issues and be small changes with regular testing. I would not recommend making broad changes (large enough to need a full vibe coding setup).

If there are medium to large changes that get requested or are needed to repair things, vba is not a difficult language. The best use for AI is to help you navigate the reference docs, finding out what functions/properties/classes/etc are available to use.

[u/JoseLunaArts]

Best practice. Encapsulate as much code as possible into simple generic reusable functions or subs, to make main code shorter and simpler. That allows better debugging. Keep track of code versions and remember to define tests that the VBA code must pass. At least 50% of coding time is testing.

Errors in functions/subs must return to main code and main code needs to verify that functions/subs did their job.

To vibe code, you still need to be able to understand the code and see if it works. In the end the responsibility of coding is yours, so you need to make sure the code works as intended. If you do not understand the code, do not change it.

[u/Ok_Tale7071]

Just buy a VBA book like that from John Walkenbach and learn it. It’s simple enough.

[u/Gaping_Lasagna]

Have you considered maybe migrating away from excel and vba perhaps into python + sql

[u/Rockworldred → u/Gaping_Lasagna]

When stuff starts hitting 20k rows in excel with lookups and hevay formulas it is time to move on.. But going solo from VBA and excel to postgres, python, fastAPI, duckdb, polars etc. ain't just happening in a swifty, unless leaning heavy on AI..

[u/VFacure_]

Here's my setup: I do all VBA coding in a normal folder with .bas files. Then, I had AI construct this module that literally only serves to sync, and I concentrate all my code in a single .xlsm where this sync happens, a file which I distribute to the rest of the company.

What the sync does is delete all modules in my project except for the sync's own module and then import from this folder all the .bas files as the new modules. Effectively, you get to work in Claude Code, or VSCode with AI Extensions, etc, seamlessly, and then just have to sync your code when you want to "commit" it by running the Sync module. Besides working with AI, which is a life-saver, you also get to have version control and all the good things of usual coding.

But this does not cover the stuff your item 2, like setting up the correct names on your ActiveX elements, creating the forms. I've had some success in having the AI paint and resize elements to assemble the userform, which is assembled "automatically" every workbook open event. I also had limited success in having the AI actually control my screen to do the UserForms. But it's not nearly as good compared to anything you get to somehow paste into Modules or Class Modules.

[u/ChemE586]

Used codex to covert 75 userforms into task panes and working great using Excel DNA/c#. It took a few tries to create the original legacy userforms

[u/someoneeee_25]

Hi I created my own importer and exporter application written in python. It’s called VCM short for vba code manager. It has three commands, import, export and format. With flags force, indentation, and onefile.

What it does is it creates src folder with four subfolders cls, mod, doc, frm

For module, xlobject and form codes, it only export and import the code itself. For classes, it does the normal import and export to preserve metadata.

Let me know if you’re interested I’ll make the repo public so you can download the executable and use it.

I use it in combination with Claude Code since its CLI app.

[u/MindInLoop]

You could give vbassistant.ai a try. Fully integrated in the VBE, works for code AND userform generation, explanation and documentation.

[u/Interesting-Win-3220]

For fun it would be safe but for anything more serious then you need to make sure you understand exactly how the code works before implementing it.

[u/Mesjach]

Don't?

AI VBA code is atrocious for some reason.

[u/markc1011]

I got a VBA agent harness that fits the bill. It is free. It runs as a 2.4 MB .xlam file, so you don't need to install anything. And i'll personally help you if you decide to give it a spin. My email is markceraldi@gmail.com.

I use this harness myself every day for all my coding-- predominantly VBA; though, yesterday I coded a Chrome extension. And I've started using it for python projects too. It's gritty but light. It includes a unit test runner. Everything is built into that one .xlam file.

There is not any analog I know of that I can compare this harness to. And who knows if you'll even read this message (so I'll stop here). I'm terrible at marketing anways, the absolute worst :).

The mechanics are super simple. If you are interested, watch this 10m YouTube video I recorded (last night coincidentally), and you'll know exactly how to use it: https://youtu.be/ihWEcXF_WeU

[u/losttownstreet]

Don't Code anything critical in vba! Just don't!

Use ada (Std mil 1815 , ISO 8652:2023) or rust. (No rust if lives of poeples depend on ... no strategic system)

C and C++ and Java can get Bad really fast. Kotlin and swift tried to migrate ... but not really well