Microsoft recently announced that they are deprecating VB Script, and it's made the VBA developer community a little uneasy, but not for the reason you might think. They are not, contrary to popular belief, the same thing. In fact, they are two separate tools with distinct purposes.
VB Script is a scripting language, often used to automate administrative tasks on Windows. For example, I've written VB scripts to clean up old files from specific folders, and scheduled those scripts to run nightly. If you've used PowerShell, this is a similar, but older, technology.
VBA stands for Visual Basic for Applications, and its used in the Microsoft suite of products, most commonly in Microsoft Access and Excel. Its often used to automate tasks in those applications. For example, I might have VBA code in Excel that reformats a worksheet.
The confusion, of course, comes from the similarity in names. In both cases, the "VB" stands for Visual Basic, a programming language that both are based on. That means that the syntax for both is roughly the same, so the difference comes down to the environment that they run in: Windows for VB Script and Excel/Access for VBA.
So, why is the VBA developer community worried about the discontinuation of VB Script, then, if they are two separate things?
Because there's a tool called Microsoft Scripting Runtime that's often used by VBA developers, and it's part of VB Script. It's a handy dll that allows your VBA program to interact with the operating system. For example, you can use the File System Object (which is part of the Microsoft Scripting Runtime) to create folders and check to see if a file exists from your Access or Excel applications. Here's one example, taken from the Microsoft website:
See that reference to "Scripting.FileSystemObject"? That's where VBA is using VB Script. Many VBA developers use the FileSystemObject for a variety of purposes, including me. It's scattered liberally through my VBA code.
Which brings us to the question: What happens to the Microsoft Scripting Runtime when VB Script is retired? Will code like the above sample stop working?
That question doesn't seem to have been thoroughly answered yet, but it's too soon to start worrying. For now, Microsoft is only deprecating the product, not eliminating it. Deprecated items can live a long life in the Microsoft world - often many years. It'll be a while before we need to solve this problem.
Knowing the VBA community, a host of alternative solutions will crop up in the coming months and years. By the time VB Script is truly gone, we'll have plenty of good alternatives. In fact, I suspect that most functionality that's in the Microsoft Scripting Runtime is already available elsewhere. We're in the habit of using it because its powerful and easy, but we usually have choices.
If you're a light user of VBA, rest assured that most of your VBA code will be unaffected by this change. Unless you're doing things like creating folders or reading text files, your code will continue to work. Even if you're doing those things, there's no need to change your code right now. I'm going to continue using the Microsoft Scripting Library for the foreseeable future, and you should too. As the saying goes, we'll cross that bridge when we come to it. We're not there yet.
I'll continue to follow this issue as it progresses and will post information about alternatives to the Microsoft Scripting Library as I come across them. Subscribe using the form below to be notified when those posts go live.
Reach out with any questions you have about VBA or VB Script.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.