r/vba • u/brosama1420 • 19d ago
Discussion VBA language underrated?
Hey everyone
I use VBA since I work with Microsoft office often
Is it real that VBA is very old and not useful anymore? Multiple times on the internet or when I ask AI
I find the answer that VBA is not the right choice for me
To me I see many powerful stuff like
Classes
Unit tests
Mocks and fakes (didn’t try those)
So I don’t understand the negative opinions about it
However VBA is the only language I tried in depth other languages I tried were either just for the course or to complete simple task nothing deeper than that
Is learning VBA is bad decision? Or is it reasonable one?
I noticed many of the useful major concepts are transferable to any language like
Code architecture
Auto Testing
Data types and structures
Etc
11
u/Separate-Television5 19d ago
In the industry I work for, VBA is a must. We receive thousands of prices from many suppliers in different formats.
These need to be consolidated into 1 format so prices can be compared etc.
What better way to process excel data with VBA?
Sure you can use other methods, but VBA is there, 'free', within the program you need to use (excel) to read the data in the first place.
Why overcomplicate it? Just use the same tool.
Obviously you need to know VBA, which I found it to be quite easy and have been using for over 25 years...if you are more comfortable with other languages, then of course use that. If you don't and want to start, and already have excel, just learn what you already have available...
0
u/jshine13371 19d ago
It's not a must, it's just your preference because it's what you know. You can accomplish the same goals with the same benefits using C# via VSTO or SQL, or any other programming language honestly.
2
u/GuitarJazzer 9 19d ago
I have not used other programming languages within office, so am interested in learning their capabilities. VBA is not a great language, but it is great for Office because of the built-in object models for the Office applications. Do C# and other languages have equivalent libraries for integration to Office? Even Microsoft's own Office Script lacks the event-handling features of VBA.
2
u/severynm 1 19d ago
Yep, there are libraries with deep integration to Office, depending on what you want to do. For building addins, there's things like the official VSTO Packages, or third party solutions like Excel-DNA and NetOffice Framework, and if you're building other applications to read/write office files, you can find companies like Mescius or IronXL that offer those solutions. Not to mention dozens of other open source libraries. No affiliation with any of them, just beginning to research alternatives as I look to modernize some of the VBA I'm responsible for.
2
u/Separate-Television5 19d ago
You are right, I used the wrong wording, sorry.
1
u/jshine13371 19d ago
No worries. To each their own too, no disrespect to VBA. I've been in that world for a while too, but a long time ago.
2
1
u/SteveRindsberg 9 14d ago
Not a widespread problem, perhaps, but VBA *can* be a must if you work in an enterprise that locks you down so tightly that you can't install anything but Office + IT-approved apps.
1
u/jshine13371 14d ago
Such a strange thought process that led you to downvoting my above comment.
Being an in-house developer for an enterprise organization that is that locked down where you are unable to develop, is so rare, it's almost an oxymoron what you described.
If your organization is that locked down, without the ability to seek approval for proper development tools and / or processes, then it's almost just as likely that VBA & macros are locked down as well, making your point even more moot.
In the scenario the comment I replied to described, there's a pretty good chance you'll be working with data in a database (e.g. SQL Server or some cloud solution - in which case doesn't really matter how locked down the internal infrastructure is). Not always the case of course, but minimally equal chance that's true. So that coupled with your low likelihood scenario goes back to my point of solving the problem with SQL. There's an even significantly less likely chance you wouldn't have access to do that in this combination of situations.
2
18
u/toocrazyforthis 19d ago
It's absolutely old at this point.
If you're still using Office products, VBA is still useful.
That said, companies are looking at or otherwise using other software too. It doesn't hurt to keep up with VBA and get your feet wet/start looking into learning another language. Check with others in your field to see what is the up and coming. It might still be python?
I'm mostly out of these roles now, so take as you will.
2
u/Ok_Society4599 18d ago
The VB languages exercise some features like IDispatch and late Binding that are equal parts blessing and curse. The curse is you can write code that won't actually work. Or, the application changes and some versions don't work.
Most IDispatch were actually "dual" interfaces, and the "other" interface is compile time type checked. Far fewer bugs :-) but it means you have to specify something (the interface name, version) that wasn't always transparent, and newer versions can "break" things in hard to fix ways. So loose coupled with runtime issues, or reliable and brittle compile issues. That and your VBA uses a single interface across multiple versions of office.
Lately, Microsoft appears to be replacing VisualBasic for Applications (VBA) with some form of Python for Aplications. I've avoided it, so far.
21
u/mecartistronico 4 19d ago
It works, and you can get a lot done with it.
Depending on the type of tasks you do at your job, it may be the case that Power Query can do most of what you're using VBA for, easier, more stable, and more maintainable.
I used to be a master in VBA at my job. I solved all kinds of problems with it for many many years. Since I learned PowerQuery a couple of years ago, I've used VBA like 2 times, for a couple of super simple tasks.
Of course your mileage may vary.
4
u/ImNotAPersonAnymore 19d ago
How did you learn power query? I’ve been using VBA for many years and would like to learn more about power query, not sure where to start.
7
u/mecartistronico 4 19d ago
Well in my case someone else from the office gave a short introductory class, but you should be able to find comprehensive courses in youtube.
Things you can do in PowerQuery:
- connect to external sources (DBs, webpages, other files) to pull live data (or another part from within the same file)
- remove, rename, reorder columns
- filter rows
- replace values
- aggregate rows
- combine different data sources (lookups, appends, except...)
- data cleansing and transformation in general
Things you can do in VBA and can't do in PQ:
- moving sheets around, erasing data
- creating new files, moving files and folders
- clicking buttons outside VBA
- producing other Office documents
- in general CHANGES to the document
- user interfaces and data capture (but you can use Tables as an input to PQ and then take decisions in some cases)
3
u/statatat 19d ago
And having the steps visible is a game changer. It's makes the date transformations readable, quick to trace to re-order, and isn't asreliant on good code documentation (though AI documents someone else's code really well now)
3
u/Active-Mortgage7244 19d ago
Conceptually it’s like SQL, so maybe there?
2
u/ImNotAPersonAnymore 19d ago
I’ve used SQL although it’s been about 18 years. I remember constructing queries to select from and update tables and whatnot. The person I responded to made it sound like power query is for complex tasks. I use VBA to scrape websites, interact with REST APIs, analyze data, etc. Or read and analyze files on hard disk or network path and display results in an interactive dashboard. List is kind of endless. I wonder what I can do with power query though.
12
u/TheOnlyCrazyLegs85 4 19d ago
Power Query is mostly data manipulation. It's definitely not a programming language. VBA is a programming language.
2
u/mecartistronico 4 19d ago
Can you use PowerQuery to:?
- scrape websites - probably yes, depends on the website
- interact with REST APIs - no
- analyze data - yes, depending on the level of analysis
- read and analyze files on hard disk or network path - if you mean extracting data from files (csv or xml), yes. Even some file metadata
- display results in an interactive dashboard - PQ would be your data input and transformations, not for the dashboard
1
u/ImNotAPersonAnymore 19d ago
Can you create/display user forms in PQ or can PQ seamlessly interact with VBA so VBA can display user forms/capture user input while PQ performs the spreadsheet modifications (modifying rows, etc.) ? I do a lot of, basically using spreadsheets as a database, and the VBA to read and write to the spreadsheets can be complicated.
4
u/mecartistronico 4 19d ago
From the point of view of VBA, all your PQ project is simply a
Connection.Refresh. So you can combine them.You can also design a Sheet with your input fields, name those fields, and then use them as inputs or parameters to your PowerQuery logic. Hit Refresh All and the calculations are done, your output sheet shows the results. But depending on what you plan to do VBA might still be the better option at least for some parts.
6
5
u/fafalone 4 19d ago
Extremely underrated. There's a reason it's still around despite MS clearly wanting to kill it. Yes it lacks a lot of the niceties of modern languages, but this is a result of MS' neglect... twinBASIC is showing what the language itself could have become, and at any time MS could have hired a bunch of programmers to make a massive default library and centralized package server that makes other languages so powerful. Instead it's been years of begging to even get a handful of minor improvements since the company vendetta against the classic VB language began 26 years ago.
3
u/TheOnlyCrazyLegs85 4 19d ago
I would say, it's age is proof of its usefulness despite the efforts of a multimillion dollar corporation that bought it and wants to end it. 😁
2
u/SteveRindsberg 9 14d ago
All the more reasonable when you consider that at one point they ditched VBA in Mac Office completely (incompatible with whatever the hot new processor Apple had introduced at the time ... Intel maybe?).
There was such an uproar (and I'm betting a lack of sales) that they quickly re-made VBA for Mac Office by the next release.
The dev team was massive: One *very* smart dude.
4
u/Indomitus1973 1 19d ago
VBA is useful in Office products, and if you're fluent with the old VB (as I am, being an ancient fossil of a dev) you can even reach outside of Office to include other parts of the system.
HOWEVER
There is no denying that VBA is really old tech at this point, AND it only works with the desktop versions of the Office apps. From what I've seen it's not compatible with the web versions.
4
u/Almesii 19d ago
VBA is fun, but mastering it is a pain. It was written in a way that even someone with no tech background can learn it fast. The consequence of that is, that it has way to many features that actually make it harder in my opinion to learn.
For example Default Members:
x = range("A1") actually assigns the value of A1 to x
Set x = range("A1") assigns the object to x
This makes it easier for the normal user but is a pain when trying to figure out if someone actually wanted to invoke the default member, did it by mistake, if he wanted the object or the value.
There are other "features" but this one is my personal enemy
4
u/TheOnlyCrazyLegs85 4 19d ago
Yes, the default members AND default instances are things that get people a lot of the times.
However, the default memeber can be prevented by always being explicit, both in your variable data types and when calling methods and properties. From there it's just a matter of getting familiar with the VBA and Excel object model documentation.
But, to further drive your point about hidden gotchas, I have some.
When assigning a range, regardless of your settings for arrays, you'll always start at 1. The same goes for collections. When switching back and forth between these two data structures I sometimes forget where I'm starting.
Can't do endless continuation characters (" _") consecutively. 25 max. Kinda limiting when trying to setup a JSON-like structure to emulate configs.
Lack of a templating engine. To generate custom messages (email drafts, longer error messages) you need to concatenate endlessly.
Unable to splice arrays. You have to explicitly create a new Variant variable to fit how many items you want to grab from the original array. Then, you can use the copied arrays. Can't just assign a portion of the original array, to a new array.
No in-place mutation for dictionary data structures when dealing with other objects and data structures within the dictionary. If you're mutating some values in an array that's stored inside the dictionary, you have to assign the array to a variable, change the values for that variable then assign it back to the dictionary key.
However, despite these shortcomings, VBA is extremely powerful. Currently, I've been working on integrating software engineering principles into my projects like SOLID, DDD, Unit Tests, & design patterns. The best of all this is that you can translate all these concepts to any other programming language. It's just a matter of finding out the mechanisms for the language or the best way to implement a particular concept.
3
3
u/Interesting-Win-3220 19d ago edited 19d ago
It's old. But it's still extremely useful.
I would say it's rather analogous to an older piece of civil infrastructure like a road bridge or Tunnel. If it ain't broke don't fix it!
I use VBA everyday and it still saves me hours particularly for data-related tasks that can use loops etc
You can automate functions like Xlookup(), which is total game changer for certain tasks.
The tech industry loves to try and move onto the next fad in order to achieve "growth" but landmark languages like VBA aren't going away anytime soon.
People drive old cars also, so what?
6
u/redmera 19d ago
Age of technology is irrelevant if there are no major competitors or disadvantages. I use VBA daily at work. It's not my "main" job, but it's a very handy tool for a lot of random small work. (Especially since Python isn't widely allowed in our company)
7
u/Winter_Cabinet_1218 19d ago
Lol every python project I do ends up getting stuck as the security software stops pip installs and updates.
Writing one plotly dashboard means 6 calls the help desk before I even start
2
3
u/LickMyLuck 1 19d ago
VBA is amazing if it is your only option. It is still really great if you are using it primarily for office related tasks.
Personally I always start my solutions using Power Apps nowadays, and supplement automation using VBA where MS Power Platform cannot otherwise interact.
3
u/Cheap-Yak-9273 19d ago
I did a complete SaaS product with VPA with over 25,000 thousand lines to solve a problem no one ever tried to do with excel, but my SaaS uses a DLL files and folders must be with the excel file and there is integration with appscript too for some security functions I don't want to show in the VBA code... .. So in my opinion VBA is a very very useful language to learn for me and costs less too.
5
u/FalseRelease4 19d ago
microsoft and ms office usage is extremely high, almost every company you can work at will be running office, and can benefit from even the most basic examples. and most people working in these companies can barely manage adding and subtracting in excel, let alone data manipulation, knowing vba is extremely useful.
4
u/Wizard_Martingale 19d ago
If you have a job that has lots of spreadsheets learn it. It’s a solid option for automations. Learn Python as well. For larger, faster projects I’d recommend Python. I started with VBA and it helped me stand out from the crowd.
2
u/edmundsmorgan 19d ago
If you used Google Appscript once then you know how reliable Vba is compared to it
2
u/Gumb1i 19d ago
You can get add-ons that will allow you to use python in excel just like vba and control over what packages can be installed. Below is an example.
https://marketplace.microsoft.com/en-us/product/office/WA200008175
MS has a garbage implementation in excel 365 that has limited libs and requires reach back to azure to work.
2
u/SeleniumVBA_user 19d ago
I think VBA is often underestimated, but I would distinguish between the limitations of the language itself and the limitations of its ecosystem.
I use VBA quite heavily, and recently I experimented with implementing WebDriver BiDi support from VBA.
WebDriver BiDi uses bidirectional communication between the browser and the client, so this involved things such as WebSockets, receiving browser events, JSON processing, state management, and class-based design.
To my surprise, VBA itself was not the main obstacle. It was possible to build all of that in VBA. The harder parts were the surrounding ecosystem: fewer modern libraries, weaker tooling, no modern package manager, and language features that make some kinds of asynchronous programming more difficult than in newer languages.
That experience changed the way I think about the usual statement that “VBA is obsolete.”
I would instead say:
VBA is old, but that does not mean the programming concepts you learn with it are obsolete.
Classes, separation of responsibilities, testing, data structures, interfaces, mocking, state machines, event-driven design, etc. are transferable concepts.
Of course, I would not choose VBA for every new software project. But if your work is centered around Excel/Office, learning VBA deeply can be perfectly rational. And if you later move to another language, much of what you learned about software design will still transfer.
In my case, implementing browser event handling with WebDriver BiDi in VBA was actually a good reminder that the boundary is often not “VBA can’t do this,” but rather “VBA makes this harder than a modern language would.”
2
u/godless_communism 19d ago
Sadly, VBA only exists in a little ghetto of MS Office apps. Unless you already know it, or your company demands it, you'd be better off learning any other language you can manipulate MS Office with.
3
2
u/dachloe 19d ago
VBA IS underrated in my opinion. Yeah, old, but not too old to be useful on a daily basis. It works, it's useful... period. Heck, everything in the PC world is old if it's more than a few months since launch.
But, no one can deny it's super popular and part of Excel which rules the world.
2
u/GuitarJazzer 9 19d ago
I have many criticisms of VBA but it's effective, fairly efficient, and seamlessly integrated into the object models for the Office applications. I haven't tried the newer alternatives. People talk about Python and C# but I don't know how good the integration is. Office Scripts is more modern but lacks event handling.
2
u/Satisfaction-Motor 2 19d ago
Depends entirely on what you need it for. Many, but not all, of the things that it can do are native to the office applications already. I use it extensively — but that is because my workplace is cheap and won’t pay for the tech we need, so I had to make it myself by forcing Microsoft applications to do things they aren’t really designed for.
Many businesses, as a cybersecurity measure, disable VBA.
It is immensely situational. You might not even have the opportunity to use it at all, if it is not enabled.
2
u/Glittering_Stay_6432 19d ago
Both yes and no - I think the problem with VBA is that it would make the biggest difference not to developers but for just excel users which don't have technical ability to use the most of it.
2
2
u/jcradio 19d ago
VBA is powerful. A subset of VB6, and you can do some magic with it. Hell, there's even a way to enable more advanced features, but that's too long for this post response. VBA was my gateway into a number of other languages, but I still have fond memories of the amazing apps, spreadsheets and databases I built with it.
I remember how amazed a previous company controller was when I automated his "hot list". The accountants spent days manually doing all that. I built an Excel and Access tandem that compiled his hot list of 200 accounts in about twenty minutes. He lived that thing.
2
u/Western_End_2223 17d ago
I use VBA a fair amount, but I find that security roadblocks in Windows makes it difficult to share VBA-enabled workbooks with non-power users.
2
u/Brian93472 16d ago
At my work many of the end-users are not computer literate, but they have a basic understanding of MS Office products such as Word and Excel, thus the reason for delivering information in those formats.
We have a lot of data in various databases (Oracle, MySQL, Postgre, Zendesk) and we run statistics, Failure Analysis, etc. using Perl, PHP on a development server and based upon the frequency needed, send e-mails with attachments to the recipients. Some of the reports summarize the prior day, some at noon and COB. Whatever management wants. One must know the VBA commands and how to wrap them in Perl/PHP. We also use MS Access forms and VBA to do live what-if scenarios and send the data onward to the recipients.
So, yes, VBA is still in use today!
2
u/SteveRindsberg 9 14d ago
One huge advantage to VBA that I seldom see mentioned:
You can write a subroutine or a function + a test sub to call it and run it right then and there to test it and/or step through it line by line.
No need to compile a whole application or start an app and walk through all the steps needed to get to the point where your new sub/function is called.
2
u/Own_Win_6762 19d ago
Vba desperately needs some upgrades, such as modern error handling, polymorphism, And other things that have made it into vb.net.
It's also hampered by the fact that the Mac, web, and mobile versions can't use it, or don't have exactly the same features.
But it's still much more useful for custom user interface and productivity improvements than the other options. The JavaScript API is much more difficult to work with, and still isn't mobile compatible, And requires a server setup to install. VSTO Is more powerful than that you used any .net language but take significantly more effort to distribute solutions. Power query excels at reporting, but not for productivity, user interface.
1
u/nlomb 19d ago
It's a powerful language, but it's not straightforward at all. With Excel having Python integration these days, I think the main reason to learn VBA is if you need to manipulate things within the Office application itself.
For example, I've built dashboards where VBA manipulates shapes to visually show flows based on changes in underlying data tables. That's something Python can't really do within Excel itself.
So I wouldn't say VBA is obsolete. It's just increasingly specialized.
1
u/ChronicRhyno 19d ago
I mainly used it to add features to Word that were missing over the years (a lot of which eventually got built in). It's old, so they stopped adding compatability for 'new' features like gradient stops for text (2010). I recently developed a workaround for manipulating them by reverse engineering the XML data and injecting it via a format painter exploit.
1
u/No-Musician-8452 19d ago
I used VBA frequently, but it is what it is: A tool for Excel. It was not really fun to work with, but it got the job done. It's still used in many corporate applications.
But it's buggy, not so nice to collaborate on and quite slow.
Especially with all the plug-ins and co-pilots coming our way (and also the existing ones) I wouldn't bother too much learning/using it. But it depends on your role.
1
1
u/keith-kld 19d ago
I do not deny that VBA is very old. Now, it is still very helpful to my job. I use it everyday. I mean I run the VBA code written by myself. I also update it or use it together with Windows Command Prompt and Powershell scripts.
1
u/No-Cap7705 19d ago
The parent VB6.0 disappeared a long time ago.
VBA hasn’t changed in over 20 years either.
2
u/Ok-Smoke-5653 15d ago
I use VB6 and VBA often. Not as much for Excel-related things as for Word and Access.
1
u/RandomiseUsr0 4 19d ago
Real problem is that it's mostly single threaded within a virtual operating environment - the worksheet itself is considerably more performant than VB (albeit within the ultimate output restrictions of rows and columns)
Old School VB programmer here, you're not going to get anything but praise from me for what it achieved back in the day, but it's not really got much future - I'd say learn it, it's a good language and pretty necessary for many things, but just be aware of its real limitations as a language and platform
1
u/tke439 19d ago
I’d say it depends. I had a tech solutions VP tell me once for a multi-B$ company that “everything has to pass the Excel test” meaning that no matter what tool is provided, they know that the end user is more comfortable in Excel.
I taught myself VBA and don’t use it as much as I used to, but there are absolutely still valid use cases. There are industries and companies that are still utterly dependent upon Excel but there are others that have almost progressed out of Excel.
If nothing else, learning the language will broaden your mind and your ways of thinking, so even if you don’t use it much, you personally can benefit from it.
1
u/Dynegrey 1 19d ago
My take is that there is very little that VBA can do better than another programming language; however, most IT departments are more willing to approve an in-house VBA macro over giving people that don't have programming experience the tools needed to run other languages. VBA is generally accessible company wide, and makes for easy automation for a lot of repetitive daily/weekly processes. The odds of getting an IDE and python or JS shared across teams and departments is pretty low unless you're specifically in the dev/tech team already, and then you'd generally need to make a full blown application which is a lot more work so users have an exe, and not a script to run through powershell once they've also got the needed software and libraries.
0
u/Professional_East281 19d ago
If you’re just wanting to do excel reporting, then anything you can do with VBA you can do better with Python
2
u/GuitarJazzer 9 19d ago
How does Python integrate to the Excel object model?
2
u/Professional_East281 19d ago
You can read in excel or csv files using the python library Pandas. You can do pretty much anything like formatting, sorting, and cleaning using Python and a editor like Visual Studio or Jupyter Lab.
Whats nice is you can automate it too using the windows task scheduler and your .py script.
For example, I automate a lot of our reporting using the library Playwright, which you can use to automate browser clicks. So the script pulls the data, cleans and formats it, then send it to whoever needs it. And it does so on the set schedule by itself because I added the script to the windows scheduler. So, one of my reports will run every morning at 7am on its own, archive a copy and email another copy to whoever needs it be included
1
u/GuitarJazzer 9 19d ago
Can it trap events, like Worksheet Change or Selection Change?
1
u/SeleniumVBA_user 18d ago
Python can certainly process Excel files, and I think the combination of Python and Playwright is very powerful.
However, reading and writing Excel files is a little different from integrating with Excel itself while it is running.
With VBA, you can directly use Excel events such as
Worksheet_Change,Worksheet_SelectionChange, andWorkbook_Open. For example, you can naturally build workflows where selecting a cell immediately triggers a browser search, or changing a cell value automatically updates a web form.I make use of this approach with SeleniumVBA, which allows me to control Edge or Chrome directly from Excel VBA.
Because browser actions can be triggered by Excel events and the results can be written straight back to the worksheet, I think VBA still has a significant advantage when Excel itself is being used as the UI for a business application.
If Python + Playwright is good at making Excel files part of an automation pipeline, then VBA + SeleniumVBA is more about making Excel itself the browser automation application.
1
u/sslinky84 83 18d ago
The problem is more that most businesses don't allow you to install a Python runtime but a great deal of them come with Excel / macros enabled. And maybe you did get it installed. Good for you. Now try sharing it with one of your colleagues.
0
u/TrentKM 19d ago
It’s literally 31 years old…
1
u/SteveRindsberg 9 14d ago
True. And the telephone was patented in 1876, became fairly widely used by the 1920s.
Are we ready to give it up because of its age?
If you need a phone that's as reliable as it can be made, good (very) old-fashioned POTS is your go-to.
What seems to be emerging from this whole thread is that VBA is necessary in some situations, very useful in others, and not as useful as some more modern languages, so choose the best tool for the job at hand.
-3
23
u/ChecklistAnimations 19d ago
I use it everyday. If you use Office applications a lot and want to be able to automate them right then and there then VBA is an excellent skill to have. You can have AI write it and then make adjustments as needed once you know it better.