VBA, what it is, how it works and why it is still widely used

VBA, what it is, how it works and why it is still widely used

Speak about VBA (Visual Basic for Applications) in 2024 isn’t that anachronistic? For some it might seem so, but the reality of the facts gives us a scenario that still seems firmly rooted in the past.

In fact, just in the last few weeks, several companies have reached us who – despite having truly considerable dimensions – continue to make use of VBA in their projects and their respective workflows.

Even think that in an organization software engineers have chosen to use spreadsheets to control an infrastructure that has cost astronomical sums over the years. The company in question showed us a spreadsheet with at least 250 separate sheets, the operation of which is orchestrated through the use of macro made in VBA. Yes, precisely those contents that Microsoft has decided to block, at least officially for a few years now (obviously for files coming from the Internet, therefore from potentially unsafe sources).

What is VBA and what is it for

Many companies use the suite’s programs Microsoft Office in their everyday work routine. VBA is a programming language developed by Microsoft which is an integral part of Office and which can be used to customize and automate processes within these programs. It is a solution commonly used in the past (and incredibly still today in various contexts) to automatically carry out repetitive tasks and develop customized features within documents used in workflows.

When it comes to automationcompanies can now access a wide range of technologies, both on-premises than on the cloud. Why, then, do many companies continue to rely on VBA, whose official support Microsoft will terminate at June 2024?

Because VBA continues to be used in the company even though the language has been retired since June 2024

In most cases, when you try to go a little deeper, interested parties respond that their company’s IT department prevents them from using other more advanced solutions, so they fall back on VBA instead. Or, it should be noted that VBA is the only language known for automating routine.

Let’s take an evolved solution like Power BI, also from the Microsoft “stable”: it cannot be used for process automation, while VBA can. Users who use Power BI to access the various dataset they often generate CSV files, then stored, shared and processed even on the cloud. But what generates these CSV In many cases? Just VBA.

Many project managers say they are denied the possibility of installing high-level programming languages ​​such as Python, Ruby, Node e Rust, generally for security reasons. Thus, many users still remain closely tied to it today Powershell v3 eVBA.

Why is VBA so feared?

At the same time, although VBA is still widely used in many companies, because its usage that’s how it is feared? What are the reasons for this reputation?

First of all, as we mentioned previously, many users resort to VBA because they say they don’t have any better alternatives available. Many organizations still base their business processes on Excel and when automation of some kind, even minimal, is required, VBA is often the first choice.

Most VBA users are self-taught: this means that when they develop code and create macros, compliance with best programming practices is often lacking. The absence of structured training leads to code written unclearly and to the production of routine inefficient.

Examining the VBA source of some projects created in Excel and brought to our vision, we found ourselves faced with a series of “horrors” which were certainly not insignificant: cycles for demanding, continuous access to the contents of the cells (instead of using arrays), frequent and useless calls to methods Protect e Unprotect, use of redundant code, and much more. All practices that undermine performance and can cause other problems.

Il macro recorderso loved by users who are unfamiliar with the code, often contains non-optimized instructions and can lead to the introduction of bugs of various kinds.

The main benefits of VBA

Below we list some of them, in no particular order advantages of VBA. Some of them, for the reasons that we will examine later, could actually be considered as serious disadvantages, especially in contexts in which safety must be in first place:

  • Universal availability: VBA is present on every computer equipped with Microsoft Office and is also supported in alternative, commercial and free/open source suites.
  • File system access: Unlike OfficeJS/OfficeScript, VBA can access the local file system, making it easier to interact with files stored on the device.
  • API di Excel intuitive: APIs that can be called via VBA simplify the development of scripts and macros for automating spreadsheet operations.
  • Access to any registered library: VBA can reference any other libraries registered on your system. In this way it is possible to extend the automation functionalities through the use of external resources.
  • Using native Windows APIs: VBA can use Windows operating system APIs, allowing the automation of operations involving windows and other operating system features.
  • Interoperability with other languages: Being based on the concept of COM, VBA objects can also be used by other programming languages. COM, acronym for Component Object Modelis a framework developed by Microsoft to enable communication and collaboration between software and components within its operating system.
  • Machine code injection and execution: VBA has the ability to inject machine code into memory and execute it, allowing for advanced operations.

Precisely because of some of the characteristics of VBA and the possibility of using it directly via macros, Microsoft has gradually decided to set aside support for this language.

How to check for macros in Excel and Office documents

Both in Microsoft Excel that in the other components of the suite, to check for the possible presence of macros and therefore VBA code, simply click on the menu View then on the button Macro of the toolbar and finally up View macros.

VBA macro control in Excel

In applications such as LibreOffice e OpenOfficejust access the menu Tools, Macros, View Macros then refer to the contents of the column on the left.

Edit LibreOffice VBA code

To truly move away from VBA requires collaboration between business teams

We said that VBA is feared due to a combination of factors, including its own obsolete naturethe poor quality of existing projects, the lack of support and updates from Microsoft, the general lack of skills and knowledge (apart from particular cases) among users who use the language without prior in-depth training.

While VBA therefore plays an important role in many organizations in 2024, there is a growing awareness of the need to migrate to more modern technologiessupported and universally appreciated.

This crucial transition phase, however, requires action collaboration between company teams: there cannot be a “strategic vision” imposed by the managers of some departments to the detriment of others.

Maximum attention on documents containing macros coming from third parties

By default, Microsoft blocks macros from running in documents from remote hosts. The mechanism is activated automatically because all files downloaded from the Internet (for example from the Web or via email, in the form of attachments), contain the so-called Mark-of-the-Web (MotW) of Zone Identifier. It is a marker which distinguishes files not created locally.

Macros can contain code potentially very dangerous: it is therefore always best to avoid uploading them if the origin is external to your organization. Among other things, loading macros with the removal of the Mark-of-the-Web it is one of the trends that cybercriminals are looking at with greatest interest. Some…

Leave a Reply

Your email address will not be published. Required fields are marked *