Over the years, I have been asked this question many times: What is the difference between VB and VBA? I tried to answer in many ways, but, ultimately, I found this answer the easiest one for most people to understand quickly:

With VB, you make a television.

With VBA, you make the remote control for a television.

Let’s walk through some key differences between VB and VBA, from both technical and non-technical perspectives.

Basically the Same Language

Visual Basic for Applications was developed based on Visual Basic, and therefore they have the same language structure and share the same set of core vocabularies. (The classic version of Visual Basic is VB6.) They also share the same programming concept of objects, methods and properties.

A lot of code written in VB can be directly applied in VBA with no (or minor) modification.

Also, you can think of the languages (both VB and VBA) as Basic plus a “visual” layer which allows developers to create user friendly user forms and controls for users to interact with the program.

Standalone Application vs Run Within a Host – TV vs Remote

In Visual Basic, after writing the code, you can then compile it into an executable application. Normally, it is an application which you can run by double-clicking the file. (e.g. the file with an extension “.EXE”) You can think of such self-contained, standalone application as a television with all the features you want.

On the other hand, VBA macros cannot be compiled into stand-alone applications and must be run from within a host application (Word, Excel, etc.).

If you want to share or distribute your VBA macros, the other party must also have Microsoft Office installed. Most of the time when you’re programming in VBA, your macros are interacting with the host and the objects provided by the host.

For example, your macros work with the Excel application and control the objects provided by Excel, such as Worksheets, Cells, or pivot tables.

Therefore, Excel is like the TV, and your VBA project is like the remote control for manipulating the properties of that TV. (e.g. changing the channels, adjusting the volume). Your remote control won’t work within the TV. You can bring your remote to your neighbor’s house and control their TV if it’s the same brand.

VBA is Free and Does Not Require Additional Installation

If you have Microsoft Office, you can use VBA without additional cost or additional installation of the development environment (IDE).

To develop with Visual Basic, the development environment has to be installed in Windows. Also, Visual Basic is not free. Microsoft offers different types of licenses.

Compatibility

VB is Windows only; VBA supports both Windows and Apple Mac

The Visual Basic development environment is only available in Windows, and applications written in Visual Basic cannot only be run in Windows. You cannot create a Mac application with VB.

For VBA, because Microsoft Office supports both the Windows and Mac platforms, VBA macros have very high level of cross platform compatibility. Most VBA macros developed in Windows Office can be directly run in Office for Mac.

You might even be amazed by the level of compatibility. For example, a macro written to read/write information from/to the Windows registry can be run without modification on the Mac! Also, OLE automation can be used on the Mac. For example, you can use CreateObject in an Excel macro to control Word on the Mac.

Compatibility over time

Although VB6 is still being widely used nowadays, even by some Fortune 500 companies, the world is moving towards VB.NET for better Windows compatibility and future expandability. Due to the many differences between VB.NET and VB6, legacy application have to be redesigned.

For VBA, macros written back in 1997 can be run in Office 365 in 2020, and likely for another 10 years at least.  However, this does not mean there have been no technological improvements in VBA over the decades. Because VBA is hosted within the Microsoft Office suite, Microsoft Office applications have undergone multiple major upgrades over time. Microsoft has done a lot of work behind the scenes to achieve such seamlessly compatibility for VBA users.

Protection of Source Code

Because VB-written programs are compiled by the compiler into an application, the source code of VB applications are not accessible by the user. Developers can comfortably distribute compiled applications without being concerned about the disclosure of their source code.

On the contrary, VBA code (macros) are store inside Office files, such as Excel .xlsm files or Word .docm files. The VBA code cannot be converted (compiled) into other forms while being saved. Although you can password-protect your VBA project (in the VBA Editor),  such protection doesn’t encrypt the macros and technically the code is still retrievable (with some programming techniques).

Such a limitation in source code protection is a concern when planning, developing, and distributing a VBA project which contains sensitive information. For example, hard-coded database passwords, server paths, etc. In these cases, you are recommended to take alternative measures to strengthen the protection of such sensitive information.

End User Developed Application (EUDA) and IT Management

VBA developed projects (macros) are nowadays often referred to as EUDA (End User Developed Applications). Previously they were also commonly known as EUC (End User Computing) tools.

From the perspective of IT management, this is an important concept, as EUDA have to be controlled within an enterprise differently, compared to “standard” application development (e.g. VB).

Some key differences include:

Software development methodology	The normal software development methodology in place is for governing software development teams. VBA can be developed by any user (who is not professional IT staff), and they have weak awareness of the software development lifecycles and key controls such as version control, change control, testing processes, documentation, etc. It is important to ensure the sufficient controls are in place on such EUDA development within the firm, for example, via an in-house “EUDA standard.”
Software inventory	Software inventories of an enterprise do not contain the EUDA. But it is important to keep track of the key EUDA within a firm. Enterprises need to develop their own risk assessment methodology for EUDA and EUDA declaration and inventory management procedures.
Training	Awareness training is necessary for staff who are exposed to risks of EUDA (both development and usage).
Workstation security management	Security of macros is being defined and managed at the user level. (Cannot be defined by the VBA developer.) Enterprises have to develop the necessary security policies for VBA macros and roll them out to all workstation users.

Conclusion

We have discussed some key differences between VB and VBA. While there are some IT technical differences, it is equally important to be aware of some differences from the IT management and control perspectives. Although this topic seems to be an IT topic, it is also something fundamental which management within an enterprise should be aware of in order to properly control the risks in the IT environment which might have implications on other aspects within the enterprise.