Over the past 6-7 years, new applications have been released by Microsoft. Thanks to these, innovations have also appeared in Excel, some of which have leaked from the new applications, just think of Power Tools, or the functions that operate on dynamic array results that debuted in the 2019 version.
In connection with the above innovations, the question arises more and more often as to what point VBA (Visual Basic for Application) or, as many people refer to it, Excel Macros still has in 2020. Is it worth starting to learn it, or should we invest our energy in something else?
What is VBA?
VBA is a version of the Visual Basic programming language developed by Microsoft that is specifically adapted for Office applications. One of the biggest differences between VB and VBA is that the latter can basically only be used in a running application (e.g. Excel, Word, etc.), and does not function as a standalone application. However, it is important that as a programming language, or VBA developer, our possibilities are much broader – in fact, practically unlimited – compared to if we had to solve a problem only with ready-made functions.
To study or not to study?
VBA first appeared in Excel version 5.0, released in 1993, and while it may not be another 26 years away, we can confidently predict it will be around for another 10-15 years. Here's a quote to answer the question of why:
Excel is incredibly popular around the world, with entire companies, credit institutions, and mining companies operating solely under the control of macro jungles that thrive on complicated and almost magical worksheets.
The magic macros mentioned in the quote above need to be constantly maintained, improved, and developed. Moreover, new and emerging needs are constantly emerging, which – since companies' business operations have typically relied on Excel tables and macro codes – will need to be supplemented with new VBA codes.
Two other huge advantages of VBA over other popular programming languages are its extremely fast learning curve, which means that after just a few hours and a few lines, we can produce a useful program, and Macro recording, which allows us to see live what code our individual movements result in in Excel, and by rewriting these, we can further refine our solutions.
What VBA is still good for in 2020!
Automatic formatting
Constantly formatting our data and tables from all kinds of sources (internet, SAP, ERP) is often a boring and monotonous task. For VBA, however, these day-to-day repetitive tasks are the easiest problems to take on. But it falls into the same category if we want to get rid of the data formatting of the PivotTable value area once and for all.
UDF
The three-letter abbreviation comes from the initials of the words User Defined Function, meaning it is a function that we can write for our colleagues and they can use like Excel's built-in functions.
There is no need to implement new Excel functions, as UDF is also good for replacing complicated and complex functions with your own function.
Userform and validated data entry
If Excel is your primary data management tool, it's a good idea to make sure that data changes are made under controlled conditions. The form design feature provides a solution for this, and if you're requesting data via a form, you can also check its correctness in VBA, which is far more reliable than Excel's built-in data validation tool.
Custom business logics and calculations
I have come across it countless times, especially in companies operating in the financial sector, that their preliminary calculations and customer offers are made in different Excel tables. In such cases, not only is the problem that the calculations – which may fall into the category of trade secrets – can easily become public, but subsequent maintenance and modification also takes a lot of time. However, this problem can be easily remedied with the help of the Userform mentioned above and by implementing the business calculations in VBA code.
Send reports and access other applications
There is probably no company that has not had the need to occasionally or regularly send an Excel workbook (or part of it) via email. But it is not only possible to send emails with it, VBA also provides the opportunity to access and control almost all Microsoft applications with it - so we can also use it to access Word documents and PowerPoint presentations. The fact that AutoCAD, CorelDraw and LibreOffice also support VBA is just icing on the cake.
Monte Carlo simulation
The Monte Carlo method is a simulation method that uses computer technology to generate the final result of a given experiment, after which the resulting numerical characteristics are recorded and evaluated. The pseudo-random numbers, which are the values of the random variables in the experiments, are generated by a computer. The duo of Excel and VBA is perfectly suited for running and evaluating such simulations.
The above few examples are just a small sampling of the many possibilities of VBA, so I can confidently say that anyone will benefit from macro programming skills for a long time to come.
References
- https://www.thespreadsheetguru.com/blog/are-vba-macros-dead
- https://derrickesharry.blog.hu/2018/06/17/a_transzparencia_demona
- https://en.wikipedia.org/wiki/Microsoft_Excel
- https://en.wikipedia.org/wiki/Visual_Basic_for_Applications
- https://hu.wikipedia.org/wiki/Monte-Carlo-method