Visual Basic for applications (VBA) is a language that is widely used to automate tasks within Excel, however you may be wondering whether it is used in any other applications to automate processes.
VBA Is a proprietary programming language owned by Microsoft and is included in most applications within the Microsoft Office Suite and is used occasionally in other applications such as access, word and PowerPoint. However, Microsoft has granted VBA licenses for other applications some of which include;
- ArcGIS
- AutoCAD
- CorelDraw
- Kingsoft Office,
- LibreOffice
- Reflection
- SolidWorks
- WordPerfect
- System Architect
But as of the 1st of July 2007 Microsoft announced that no further licenses will be granted for external applications.
What Are The Limitations Of VBA Compared To Other Programming Languages?
The primary difference between VBA and many other languages is that it requires a host program in which to run the language which can be considered both a limitation and a Vantage depending upon how you look at it.
The advantage of it is that it means that if you are running a program such as Excel which most people have on their computer’s there is nothing specific required to be set up in order to use the language to automate activities within that file. However, when you compare this to something like python where for the language to run it requires an environment to be set up on the computer to allow the programs to run.
The other significant disadvantage is that It is not as easy to transfer code from one file to another because the VBA is stored with the file. As a result of this the other significant disadvantage is that VBA does not work well on web based applications because the code runs on the user side rather than the server side.
This is one of the key advantages that Google script and office script has over VBA which makes them far more suitable for files that are being shared.
Can VBA Be Used To Control Actions Outside The Host Program?
However, despite the fact that VBA needs to be held with a host program such as Excel or access it still has the advantage of being able to control actions outside of that host program which has the potential to improve the flexibility of the programming language significantly.
Examples of where this is most commonly used is in cases where data is being drawn directly from an access database using a combination of VBA and SQL to define the query required. Additionally, it is also common to program Excel to send emails out via Outlook.
However, all the programs mentioned thus far all have VBA included in the program themselves; however, this is not a requirement for VBA being able to control a program. For example VBA can also be easily used to email information out to recipients via a Gmail which does not support VBA by itself instead of using Outlook.
In addition to controlling other programs VBA is also capable of creating files in formats other than the host program and saving them in predetermined locations. Examples of this include saving Excel or word files as PDFs or CSV.
Is VBA Going To Continue To Be Supported?
Given that Microsoft is no longer handing out licenses for VBA you may be wondering whether it will continue to appear in future versions of Excel. Based on the evidence that we can see at this point time it seems likely that VBA will disappear at some point but it is more than likely that it will be at least 10 years away because the alternate language currently included in the online version of Excel, office scripts, has some serious limitations at this point in time.
The limitations of office script at this point and time is it is really designed to be something that can be used with recorded macros and currently does not have the capability to get user input which means that at present its capability is limited.
However, it does have an important feature which VBA is lacking which is the capability to be used in the web version of Excel which makes it far more suitable for cases where the file is shared between users. Additionally, the way that it records macros and creates the code is significantly superior to VBA and makes it relatively easy to use compared to VBA.
The key advantages are that office scripts documents the code as it is being written making it easier for the user to modify manually if need be. Additionally, any cells that are selected that are adjacent to each other are recorded as a single range which generally makes the code easier to read and manage.
The other key advantage of office script is that it is saved as a separate file in your OneDrive and can be readily shared with other users and can be applied to different files easily.
So there is no doubt that offer scripts has a brighter future than VBA at this point in time, however, until its functionality catches up with VBA it is unlikely to be replaced completely. So one would suspect that it will be at least 2 new versions of Excel into the future before there is serious consideration of leaving VBA out of the package completely which would suggest that you are looking at a time frame of 10 to 15 years before it becomes redundant at the earliest.
Relevant Articles
Does VBA Work On Mac? Are There Any Problems?
Is VBA A Programming Language?