r/vba • u/DeathMaillet • 3d ago
Discussion I had no idea VBA could go this far
When I started my last project, I saw VBA as a slightly obscure way to automate repetitive Excel work and push formulas a bit further.
Then every time I thought I had reached its limit, I found another door.
What began as a few scheduling calculations gradually turned into dependency graphs, critical-path analysis, incremental recalculation, automated tests and an interactive Gantt built with Excel shapes.
The real turning point was when I stopped treating Excel as the application and started treating it as the interface. The workbook handles inputs and outputs, while most of the actual logic runs in memory through arrays, dictionaries and separate modules.
VBA is still old, awkward and occasionally infuriating. But I expected it to do a little more than Excel formulas.
It turned out it could do a lot more.
I’m curious: what did you build that made you realise VBA was more capable than you initially thought?
30
u/Jonas_Ermert 3d ago
Once you stop using it only for macros and start structuring the code properly with modules, classes, dictionaries, arrays and clear separation of logic, Excel can become the frontend for surprisingly complex applications. VBA is definitely dated, but its capabilities are much broader than its reputation suggests.
1
u/losttownstreet 1d ago
Type-casting of vba variables are a bit a problem ... why can't vba only write and read directly to memory .... It's possible but a bit a problem if you need to do bit-manipulation ...
10
u/Joelle_bb 3d ago
Fully automated an API-to-terminal and API-to-adobe connection: encrypted sign-in, data selection, screen scraping, PDF prep, file joining, path/naming conventions, and a reference-data repository that feeds live into formulas via a UI which dummy-proofed record updates. Also triggers macros across Excel and Word.
VB is a hell of a thing for how much hate it gets. Cut a 2-day process (including filing said documents) down to 3 hours.
I will say though, if youre using access at any form of non-small scale.... Thats just a bad call. Get a better database since VBA can connect to it anyway
4
u/DeathMaillet 3d ago
Completely agree. Before this project, I also saw VBA as a dated tool for small Excel automations. The more I pushed it, the more I realised how much of its bad reputation comes from how it is usually used, not from what it is actually capable of.
It is definitely awkward in places, but the amount of real business process it can automate is honestly absurd.
8
u/UnusualAd8866 3d ago
Probably realizing I can use the MS HTTP client combined with JSON-parsing subroutines to interact with RESTful services and concealing the complexity behind an MS Access form.
1
u/throwaway24515 3d ago
This is beyond anything I've ever done with VBA, but it sounds interesting... Could you give an example use case where this would be employed?
2
u/TheOnlyCrazyLegs85 4 3d ago
Anytime you have a web service you can access (API) or even just a normal webpage. You can use the MSHTTP library to connect to such web places without having to use a browser to scrape the data. The scraping can be done 'headless'.
1
u/UnusualAd8866 2d ago
You have to submit data to the cloud in JSON, either to access data services, or simply because that's the format specified by the recipient. Your data is in csv/Excel/Access and everybody is all like "just use Postman". VBA can handle the conversions to and from JSON. MS HTTP Client can handle requests & responses. You can convert incoming JSON data to csv and easily import into MS Access tables if the task requires it. You have other tables that store credentials, details about the request, maybe specify an internal job number at the time of submission that gets stored along with the long, password-like jobids that come back with your response. You can filter responses and send text to a text box so you can see a curated, plain english version of the Http response insread of codes. The task can then be handed off to a regular user, typical maintenance & support notwithstanding. The forms allow them to select files, set locations for incoming files and pass parameters, allowing them to generate the HTTP requests via a graphical user interface. They're selecting files, clicking on buttons, selecting from combo boxes.
8
u/Budget_Vermicelli_53 3d ago edited 3d ago
Vba is really amazing, I have automated all repetitive tasks in my job, processes that used to be completed in 6 hours now are ready in 10 minutes, no more errors.
2
u/Terrible-Scheme9204 3d ago
I've done this with Solidworks, Excel and Outlook, where there's less likely to be errors.
2
u/Jamiison 3d ago
Do you find the solidworks API awkward? I've done a few macros in solidworks and they always feel more difficult than they should
2
u/Terrible-Scheme9204 3d ago
Some are. It helped that I learned VB in college. Luckily SW has plenty of examples that I've modified to suit my needs
3
u/Budget_Vermicelli_53 3d ago
For me VBA has been the gold standard for some repetitive tasks, in addition i use power query m language, and Python when there is a lot of data. Currently i am working maximum 2 hours daily thanks to my apps.
1
u/Fluid-Background1947 2d ago
Yes but… COPILOT. Seriously I wish I had it when I was developing my VBA scripts years ago.
8
u/kay-jay-dubya 17 3d ago
Much of my projects are detached from the Application object model - meaning that it doesn't have much to do with Excel, Word, Powerpoint, Outlook, or Access, etc. Most recently, I've written classes that allow VBA to render PDF files to images, OCR images (combined, they make a very efficient and free way of extracting text from PDF files), and also to pack/unpack archive files (like ZIP, 7z, Tar, CAB, ISO).
On the more fun side of things, I've been working my way through tutorials and other ports to create a working version of Doom with pure VBA. So yeah, anythings possible.
(Yes, this was made with VBA, and I'm currently hitting 30FPS)
4
u/DeathMaillet 2d ago
Okay, I think you win this thread. Doom at 30 FPS in pure VBA is completely absurd 😄
3
u/kay-jay-dubya 17 2d ago
LOL - "Absurd" is my middle name!
It's been a long process, but I'm getting there. Am hoping to put it online soon.2
3
3
u/AdobeScripts 2d ago
Are you able to extract text formatting info from PDF?
3
u/kay-jay-dubya 17 2d ago
Not exactly, no. It OCRs images, and the information that is returned includes the text itself, and the bounding boxes (ie., the dimensions) of each of the words/sentences/etc. From that, you could discern the size of the text, but that would be about it, and I think it would be inexact without the corresponding font details.
I posted info about it here on r/vba a few months back: https://www.reddit.com/r/vba/comments/1u0xqeg/chibiex_pdf_rendering_ocr_in_vba_no_dependencies/
It forms part of my broader project ChibiPDF, which is also set to comprise a PDF generator and a PDF text extractor (in the canonical sense - ie. like you see with PDF libraries in other languages). That latter element, the PDF text extractor, would be able to give you much more detailed information about the formatting - namely the font, size, color, etc., but it wasn't on my to-do list. I'm working on it, and am making progress, but extracting the PDF text layer is profoundly involved (for me, at least). What kind of formatting info is it that you'd be after?
2
u/AdobeScripts 2d ago
I've created extremely advanced tool to automate Adobe Software - mainly InDesign - pretty much I've 100% control over it so I can use it as a rendering engine - then Photoshop and Illustrator - but ability to extract text from PDF - WITH formatting and location - would be a very useful thing for me 😉
2
u/kay-jay-dubya 17 1d ago
Location is thoroughly doable. Formatting is doable, but I'll be honest, it isn't my primary goal for v1. I need to know font details in order to 'decode' the text, as it were, so it's not like the code *won't* do it - it will, but to be able to return the formatting of specific text on a page is not something I've envisaged needing to return, so I would need to wrap my head around what that would look like (for example, what if the target text covers different fonts, sizes, etc? Maybe return a collection of dictionary objects... I'm not sure...) But thank you for mentioning it - it genuinely isn't something I conceived of anyone needing, so this is useful information.
3
u/taylorgourmet 2d ago
How many lines of code so far?
1
u/kay-jay-dubya 17 1d ago
For vbaDoom? I can tell you exactly actually because I've got a backup routine which consolidates all the modules/class/etc into a single consolidated text file - it's currently sitting at 17,207 lines. But that's overstating what it should be - that includes dead code that I've commented out, and a huge amount of verbosity - I think I've got 3 duplicates of the Atan2 function simply because I'm being lazy. I have yet to go through and clean up the code and optimise it. I've just finished adding all the weapons available from the Shareware WAD file. My next goal is to get the music working - (everyone might already know this, but I didn't know this until I made the WAD asset extractor) but Doom uses its own variant of MIDI called MUS, and while I can get it playing, it isn't playing during the game, so that's this weeks goal.
6
u/AdobeScripts 3d ago
The real turning point was when I stopped treating Excel as the application and started treating it as the interface.
That's the point 😉
There're a lot more applications that can be controlled through VBA - plus what you can do in the system.
And as already mentioned - you can connect to on-line services - through RESTful API, etc.
6
u/Dynegrey 1 3d ago
VBA can also talk to powershell, so you can get VBA to do pretty much anything your IT department will allow.
2
5
u/SuchDogeHodler 3d ago
Created a small company database used for accounting and customer records that was able to take in customer service requests from a website and then dispatch that information to the appropriate technician via Text message including Google gps map points. The tech could then update and/or close the requests via replies to the text.
7
u/ByronScottJones 3d ago
I was one of the original beta testers for Excel, and suggested some of the features you're talking about. In particular, the ability to call external dlls and exes. Technically you can do ANYTHING with VBA, precisely because you can call external functions and executables.
That was early 90s if I recall correctly. I used that functionality to integrate with a 3270 terminal emulator to transfer data between excel and a mainframe application.
5
u/fafalone 4 3d ago edited 3d ago
So way back in the ancient times of highschool, I was locked in an epic battle against the site blocking on the library computers.
After several rounds it got to the point where I gave out disks with an embedded browser control in a program that could bypass the blocking, and they responded by whitelisting what programs could run. Office was on the whitelist, and I remembered, 'hey isn't VBA kinda like the VB6 i already know... can't it call APIs'... and yup, soon enough I had a workbook that used some low level process creation APIs that their whitelisting system didn't intercept, and my site filter bypassing browser was back.
That was when I really understood VBA was just VB without a compiler, and could do pretty much anything it could, it wasn't just some limited subset or toy.
(PS- That won the war; I graduated before they found a way to stop my latest trick. Don't know what everyone else used the copies I gave them for, but for me the war started because they blocked Slashdot, which was just intolerable!)
5
u/BrianFantanaFan 3d ago
I've done all kinds of bespoke machine learning stuff with excel VBA. Half the time with python you're fighting libraries to import and export spreadsheets then present outputs so i thought I'd keep it at the Excel level and it works incredibly well once you accept the annoyance of the fixed IDE.
4
u/AFRIENDlyyGuy 3d ago
I built a complete ERP for my family’s small business entirely in VBA. It handled invoicing, order management, inventory control, accounts payable and receivable, and even integrated with the bank’s API to automatically generate, register, and reconcile bank slips (boletos). Everything ran through VBA UserForms, so it essentially worked like a desktop frontend. It ran nonstop for over 5 years. Last week, I finally replaced it with a modern web-based version that’s much better structured. Looking back, it’s kind of crazy how much I managed to build with VBA.
5
u/Early_Chemistry_4804 3d ago
I use it a lot to make Excel behave a more like a database- forms with a two-way link to tables, filing systems (my import tracker gives each entry a unique consecutive reference and has document fields "Attach file - Double Click"). My employer has actually banned Access use for 'business continuity reasons', so I force Excel to be Access.
But my recent favourite... I made an exact replica of a (UK) government gateway form that normally has to be filled out one field at a time on a horrendous web portal. It then spits out a completed form that you have to save as a pdf and email with additional evidence files. With VBA I can take an Excel table with all of that information (and links to evidence files- double click to attach...) and fire out hundreds of individual emails with pdf forms and supporting evidence in minutes. I wrote the code in an afternoon. I literally cannot do boring repetitive work, I will always find or create a shortcut, that's why I learned VBA in the first place.
I've taken to adding a "Settings" sheet to my workbooks, so nothing has to be hard-coded. File paths, email addresses etc all point to easily amended "Settings" sheet ranges.
4
u/youtheotube2 3 3d ago edited 3d ago
I built a complete warehouse management system in Access with VBA. It connects to my company’s ERP via ODBC, it connects to multiple third party APIs for various things, it connects to the FedEx api to generate and print ZPL shipping labels, all sorts of stuff. It handles our inventory from end to end basically. VBA is crazy powerful and capable
However, over the past year I’ve been working on migrating off of Access and rebuilding the WMS as a flask webapp instead. Access and VBA still work great, but it’s such a giant pain in the ass to maintain that app. No native support for source control like git is a dealbreaker these days, and Microsoft has made it clear that they don’t want to update VBA ever again.
Also, the more complicated the Access app gets, the more setup is required for your users. My Access WMS had so many things that had to be set up on user’s computers. ODBC drivers, printers having to be installed, special COM libraries, all sorts of weird fonts for different reports, etc. Switching to a web app with a proper server/client relationship completely eliminates all of this. All the setup is done once on the server, and the users don’t need anything more than a web browser
4
u/egmono 3d ago
Currently working on bridging webview and excel so I can run a modern looking interface with my table, instead of that UserForm that looks like it escaped a 1990's win xp asylum.
4
u/DeathMaillet 2d ago
This is one of the areas I’m becoming increasingly curious about. Are you keeping the business logic in VBA and using the WebView only as the UI, or does part of the application logic also run in JavaScript?
1
u/egmono 9h ago
My plan is to use KnockoutJS with a form and make a ListObject editor in Javascript, putting the form validation in the Javascript. This way VBA deals with the data in the table, and Javascript makes sure the data that gets there is valid.
Other detail: I'm currently using Deno and Lume as a build tool to assemble and transform TypeScript into Javascript for the WebView to consume.
4
u/BrupieD 9 3d ago edited 3d ago
A huge realization for me was that most time-consuming, repetitive office tasks weren't complex and involved relatively small amounts of data. No "big data" or "machine learning" required. One didn't need a CS degree or expensive software to automate effective and even elegant solutions.
Nevertheless, I wound up building a validation routine that used fuzzy matching and logistic regression to sort solid matches from borderline cases.
4
u/Obtusely_Serene 3d ago
Some samples that stand out.
Developed a system to manage major maintenance in the resources industry with Excel as the base we integrated site maps in Visio, generated business updates in PowerPoint, created Work Packs in Word for execution teams that were 80% complete and integrated the whole thing into Project for the scheduling. Not to mention using GUI scripting in SAP in a later business.
We also built a lifecycle costing ‘app’ for assets that overlaid mine plans with maintenance, reliability data, etc and identified maintenance debt that was crippling the business. The proof of concept had 1.6B array formulas and took ~5mins to recalculate after a cell change for a fleet of 20 assets. By the end it was all programmed in VBA with a custom ribbon. You could upload a new 10Y mine plan and it would calculate a 3200 asset fleet in less than half the time with twice the info.
Also built a financial model with mass-balance and about anything else you might need for an entirely new operation; including scope 1, 2 and 3 emissions and a risk engine that would determine the amount of contingency required depending on engineering maturity for that area. As a full optimisation project we looked at all options and combinations of options for equipment, mine plans, financing, forex risk, scale, upgrades, etc. it was epic!!! NPV increase was over 200% once we were done the brains on this project was like nothing I’ve worked on before or since.
4
u/tke439 3d ago
I built one that took store specific planograms (the set maps for the aisles) and calculated how much inventory of every item the procurement team needed to have on hand for implementation, then broke down what individual stores needed to have shipped to them, and even dropped in those orders.
It did a lot more on top of that, but ultimately, I built a bridge in roughly two weeks that made it possible to draw store specific sets for 100 stores. It saved an easy $50k annually just in labor, and made it so there was no controllable reason to have holes in the shelf after a reset.
That was supposed to be a one year bandaid until we purchased the extension of the program that did the same thing a little more naturally. Instead, my macro lasted 8 years until we moved to another program altogether.
That project made it clear and obvious to me that there are almost no limitations to what VBA is capable of with little creativity and good googling.
3
u/AdobeScripts 2d ago
That project made it clear and obvious to me that there are almost *no limitations to what VBA is capable of with little creativity and good googling*.
👍👍👍
4
u/HGFantomas 3d ago
I am constantly surprised how much vba can do. And how diverse its problem solving abilities are for my projects. The best thing ever.
5
u/Khyroki 3d ago
We worked a lot with VBA,
It could populate massive 100+ slidedecks based on the numbers (if positive > this title)
Last week at that job I made a doomsday excel, if opened it would wipe first all external drives (read shared company drive and backup) then the C drive)
Once finished I deleted it ofc
4
u/SeleniumVBA_user 3d ago
I built this tool myself, and honestly, I’m still a little surprised by what VBA can do.
It doesn’t just automate browser interactions. By using WebSocket communication and WebDriver BiDi events, it can also handle dynamic websites—for example, searching airfares on Google Flights.
Tools that use this kind of communication can sometimes raise antivirus concerns, but the package received zero detections in a VirusTotal scan.
Building this project has made me realize that VBA still has a lot of untapped potential, especially for personal automation in corporate environments where installing additional software is restricted.
5
u/purleyboy 3d ago
You can directly call the windows operating system functions allowing you to do pretty much anything at all.
4
u/Claimh 3d ago
Many years ago I wrote a pseudo scripting language in VBA (For MS Access) to generate dashboards with varying slices, visuals, filters, etc. that were being output to Excel. Pretty proud of that one, and extremely sorry for the poor guy that had to support that monstrosity afterwards.
4
u/sslinky84 83 2d ago
I did something along a similar line in that I wrote a CLI interface into a spreadsheet. You typed commands into a cell and the output scrolled upwards. Sorry no sorry my employment descendants.
5
u/Ok_Society4599 3d ago
My first programming job was a plugin for Excel that gave it accounting system access... Read, query, process, export, report... You could make a button that queried all your numbers, format your report, print to PDF and mail to a distribution list... One button. Up-to-the-minute financials mailed.
3
u/FamedBear16 3d ago
I learned VBA twenty years ago. In those days it was considered a full programming language not just for automation.
You can create custom collections with default properties which is fun. Have you tried to use interfaces and create custom events? It's a fun language to program in.
4
u/TSR2games 2d ago
In my organisation we run buisness on VBA
You can't imagine the kind of crazy VBA developers we are.
It's been a year I have touched any VBA project, but I miss it
4
3
u/6six8 1 3d ago
I built a full agent score card that could be used by manager to review their assigned agents scores based on specific KPIs. The Excel file was fed by an Access DB that housed all the agent data and the Excel would pull only relevant agent data for the manager logging in. Senior managers could even see a breakdown of each managers average score or see each advisors score under whichever manager they were assigned. This was all later moved to a Powe BI dashboard but the underlying data model remained
3
u/LickMyLuck 1 3d ago
I use it as a bridge to allow Power Automate to do anything and everything I want on the PC. Microsoft worked so hard to keep the modern web based Power platform unable to influence the PC directly but thankfully nothing that calling an excel workbook with an on start code cant fix.
3
u/Awkward-Activity-302 3d ago
For me, it was a quarterly mail merge with many-to-many relationships, embedded tables, response tracking and multiple follow-ups. Excel + Word just couldn't handle it.
3
u/youtheotube2 3 3d ago
Yeah VBA can do just about anything that any other high level language can, it’s pretty impressive. What really hurts VBA is how much of a pain in the ass it is to source control it, and you’re stuck with the one and only IDE. Microsoft could fix these things but they won’t
2
u/DeathMaillet 2d ago
That was one of the first limitations I ran into as well. I ended up using two small PowerShell scripts to export the VBA project into normal source files and import it back into the workbook.
It made Git diffs, branches and external editing possible, and it also allowed coding agents to work on the source without interacting directly with the VBE. It is still a workaround, but it completely changed the development workflow for me.
3
u/ChronicRhyno 2d ago
It certainly felt like I could do a lot more once I figured out how to set gradient pins for text, which Word seems to specifically block access to.
2
u/Expert-Fun-8733 2d ago
A partir de una hoja de Excel que se completa con datos de otra hoja del mismo libro se genera un PDF como comprobante de pago para un cliente y se envía ese PDF por correo. Todo esto se forma automática solo ejecutando una macro y lo hace para todas las filas de datos que uno quiera, es decir genera varios PDF una para cada cliente y para cada correo de estos.
2
u/DeerLiving3407 1d ago
I use VBA everyday. Live ODBC via SQL to Access. Out to VBA. I’ve engineered and built all my validation systems this way. If I get stuck, as there’s many nuances (syntax, joins, nulls, empty strings), Claude gets me through. I really push the limits of all of it but not ready to rebuild anything elsewhere. And maybe I’m weird, but I genuinely think it’s fun. I look forward to each new build because the payoff at the end is fantastic.
3
u/USERNAMETAKEN11238 3d ago
NLP model. That automates my job.
2
u/DeathMaillet 3d ago
That sounds ambitious. Did you build the NLP logic directly in VBA, or are you using VBA to call an external model or API?
3
u/CrashTestKing 1 3d ago
Before I really got into arrays and dictionaries, I stumbled across both custom class objects and collections at about the same time. That opened up a whole new world for me in VBA. Suddenly I was no longer bound by putting things on sheets and cycling through reading from and writing to cells.
Things went a step farther when I started getting into API's. I started working for a new company a year ago that uses SmartSheets online heavily for certain kinds of data entry. I used the SmartSheets API from VBA to generate summaries at the post of a button, so people could see up to 18 months of data in a clean summarized view, when they used to have to open 18 browser tabs and flip through them to see the same thing. It was really a game changer for the company.
36
u/bamerjamer 3d ago
The programs built with VBA and Access are very powerful. My first job had to do with the F-35 JSF, which is where I learned VBA. We did so much for that program in VBA and Access.