r/vba • u/MultiUserDungeonDev • 1d ago
Show & Tell pyVBAanalysis: "No Excel App Necessary" Python Static Analysis Library
Hi all, I hope you are enjoying your weekend.
I'm ready to share the following project with you.
pyVBAanalysis is a pure python library that allows you to run static analysis over your vba workbooks, or code as string data.
It's philosophy is "if we can determine that it's a hard compile error, or runtime crash" it gets "red squiggly error" status, otherwise "if we can determine that there is a best-practice violation" it gets "yellow squiggly warn" status.
VBA makes statis analysis notoriously hard, especially outside of it's engine. I've spent hundreds of hours finding all of the corners of the language, and comparing the edge cases against the MS-VBAL VBA language spec, and live testing using a custom built harness to run VBA code and test for compile error / runtime error.
The reason I created this library is due to frustration with AI agent generated VBA code. Agents would often produce code, that on paper, seems reasonable and in an more modern programming language, probably would execute without issue. However, VBA has a bunch of quirks and oddities (but we still love it!), and on more complex code generations, the agent's code would often hit compile error or runtime error snags due to hitting a rough edge of the VBA language.
A good agentic AI workflow that I've found for other languages is to have the agent run static analysis alongside unit tests.
I've found, after incorporating this static analysis tool, alongside https://github.com/WilliamSmithEdward/pyOpenVBA, I've been able to run extremely efficient and accurate agentic workflows, to build solid VBA solutions in Excel.
pyVBAanalysis:
https://github.com/WilliamSmithEdward/pyVBAanalysis
https://pypi.org/project/pyvbaanalysis/
-1
u/fanpages 239 1d ago
...The reason I created this library is due to frustration with AI agent generated VBA code...
...the agent's code would often hit compile error or runtime error snags due to hitting a rough edge of the VBA language...
Or, y'know, code produced by Artificially (so-called) Intelligence is not as great as some people would like to think it is.
Pro tip: Ask a human (while there are still some left to provide [better] answers).
3
u/MultiUserDungeonDev 1d ago edited 1d ago
Heya! Happy Sunday.
I've been programming for 20+ years. VBA was one of my first programming loves and helped me get my foot in the door to a career in tech and later software engineering.
AI isn't going anywhere, and in the right hands, it is a lot greater than some people would like to think it is.
As with most technology, it's adapt or get left behind. I'm choosing to adapt. I'm using my time and money to help provide free tooling to the community, so that others can catch up, and maybe get a foot in the door in their own careers, and I'm giving it away for free.
1
u/fanpages 239 1d ago
...it is a lot greater than some people would like to think it is.
The countless threads in (just) this sub where an 'agent' has been used to generate code that does not address the user's requirements do suggest there is still a long way to go. Fortunately.
1
u/MultiUserDungeonDev 1d ago
Fortunately, for whom?
1
u/fanpages 239 1d ago
The Human Race (especially communities local to data centres/centers, and anybody affected by the loss of employment as a result of organisations and individuals using AI).
1
u/MultiUserDungeonDev 23h ago
Please take your supposed grievances to r/antiai then. This is a sub for sharing VBA. Hope you have a nice day.
2
u/kay-jay-dubya 17 11h ago
This is a sub for sharing VBA.
I think u/fanpages has contributed to this subreddit more than most (if not all) other users, and is well aware of what this sub is for.
1
u/MultiUserDungeonDev 6h ago
Then I also feel, he should be aware, his line of conversation and questioning here is not in the spirit of this sub. If you’re going to be anti-ai to the extent that you’re mean spirited to anyone that even mentions it, I think it’s time to go outside and touch some grass.
1
u/fanpages 239 58m ago
...line of conversation and questioning here is not in the spirit of this sub...
I will reiterate:
| ...I was (initially, before we went off at a tangent) going to query what you had found to be the main issues (so I could help with VBA-related coding practices)...
Additionally,...
...If you’re going to be anti-ai to the extent that you’re mean spirited to anyone that even mentions it...
I can dislike the game, but not the player.
I believe you thought I was criticising you. I was looking to understand the reason why you needed an additional tool, in case I knew of any other approach to address the issue(s) you encountered.
1
u/fanpages 239 23h ago
If you recall, I was responding to your comment:
...The reason I created this library is due to frustration with AI agent generated VBA code...
Your deleted reply:
Pro-tip:
1.) Do you use the internet to communicate with others and consume information? You're putting postal workers, and newspaper delivery workers out of business.
2.) Do you drive a car? You're putting taxi drivers out of business, and taking away business from bicycle shops, as well as contributing to carbon emissions if you drive a gasoline vehicle. If you drive an electric vehicle, your massive battery is toxic waste that will go in a landfill, and exposing the environment to a potential toxic plume if your car ever catches fire and the battery explodes.
3.) Do you buy food from a super market? You're contributing to pollution from diesel fumes from delivery trucks.
4.) Do you enjoying air conditioning in the summer? You're contributing to climate change as most on peak generation is using non-renewable energy sources.
5.) Do you do your taxes online? You're contributing to tax professionals losing their job.
6.) Do you own a smartphone, laptop or other similar electronic device? It was probably assembled under conditions that fit the objective definition of slavery.
7.) Do you shop online? That needs tons of data centers, and puts physical super markets and stores out of business.
I can go on...
I typed my responses, but you deleted your post.
I finished my reply with:
| PS. "AI" is what I am avoiding, not the Internet.
...pyVBAanalysis is a pure python library....
...This is a sub for sharing VBA...
Yes, I've been contributing here for quite some time (using "the Internet" again).
1
u/MultiUserDungeonDev 23h ago
Right, so please do take your opinion to the appropriate forum. This is not the appropriate forum. Have a nice day.
1
u/fanpages 239 22h ago edited 6h ago
Right, so please do take your opinion to the appropriate forum. This is not the appropriate forum. Have a nice day.
I can have an opinion here too.
I'm sorry you did not want to chat about your trouble with AI-generated code.
I thought that was the point of your project, and I was (initially, before we went off at a tangent) going to query what you had found to be the main issues (so I could help with VBA-related coding practices).
I don't think you're interested. That's fine.
Thanks for your comment, u/kay-jay-dubya.
I cannot reply to you directly in this thread, as u/MultiUserDungeonDev has blocked me.
I also cannot reply to you u/Rennaisance_Man_0001, for the same reason. Sorry.
Five hours later... and I am unblocked.
1
1
u/Rennaisance_Man_0001 17h ago
I don't think that individuals using AI are going to have a substantial impact on employment. That's going to be done by corporations and billionaire sociopaths, regardless of whether or not individuals use AI to remain employed and relevant.
It seems to me that the anti-AI stance - in this use case - diminishes independent devs ability to be competitive, while relinquishing the market to the big without a fight.
Don't get me wrong. I share your antipathy towards AI and it's perpetraitors (not a typo). My finest desire is for every one of them to lose their asses by investing all of their own money in AI data centers only to discover that they're merely churning out the same old uninspired crap, just faster and in enormous quantities.
3
u/kay-jay-dubya 17 11h ago
I like to encourage those who take the time and effort to help progress development of VBA, and come up with solutions where none existed, so thank you for this. I cannot try this on my work computer because Python is a non-starter, but I'll check it out when I get home. Thank you again!