Excel VBA (Visual Basic for Applications) is the programming platform provided by Microsoft for Excel spreadsheets.
As described in its name, it allows programming in Visual Basic language, adding functions for managing directly the information contained in Excel spreadsheets.
Excel VBA can be used with every Excel license, so it is available for all users. The only requirement that we must consider is that we only can add code to Excel file types that enable it (extension “.xlsm). By default, Excel files are created with the extension “.xlsx”, which does not allow saving vba code.
WHAT IS A MACRO?
The initial purpose of enhancing the Excel spreadsheets with some programming was to allow performing automatically repetitive actions. This functionality is available by using Macros directly in the Excel spreadsheet.
In this way, and to make it easy for users, Excel allows recording a Macro just performing these repetitive actions that later we want Excel to do automatically. This way of creating a Macro does not require coding, and it is advantageous in many cases. It is fast to implement for simple requirements and does not require coding skills.
But in the background, what a Macro recording does is to generate the associated Visual Basic coding that later won will be executed whenever we run that Macro.
This integration of the Macros with the Visual Basic platform give users two main advantages:
- On one side, if we are unsure how to code an action that we can do directly in the Excel spreadsheet, we can record it and then check the code that has been generated.
- We could even edit it to customize it for our needs.
- On the other, we can create procedures in Visual Basic that, later on, we assign to Macros, so they can be run directly from the Spreadsheet without creating any additional interface.
WHAT ARE THE ADVANTAGES OF USING VBA?
Visual Basic is a high-level programming language that does not allow as many applications as other programming languages (such as Python, C++, …). But whenever we want to enhance the capabilities of our Excel spreadsheets, due to its close integration with them, VBA is a great option.
We normally think of Excel spreadsheets as just a tool for making calculations, but thinking in terms of application development, we are doing many more things:
- Whenever we enter data, we use it as an interface.
- Think on the effort it would require to create an entry and edit interface with the capabilities of an Excel spreadsheet.
- Whenever we save an Excel spreadsheet, we use it as a database.
- Other database applications can add additional functions for managing data. But for simple storage, Excel provides a fast and easy way of organizing it.
- Whenever we report the results in tables or charts, we use it as a reporting tool.
- Not only it allows to use different predefined chart types, but it gives flexibility on how to arrange and edit it for data analysis.
WHAT CAN WE DO WITH VBA?
Excel VBA is designed mainly for enhancing Excel spreadsheets. So as a starting point, every action we perform manually in an Excel file, can be coded and performed by a VBA program.
But apart from that, it adds many more possibilities:
- It allows performing more complex calculations using constants and variables and built-in formulas.
- Through the use of procedures, we can implement iterative calculations.
- It allows managing different Excel files.
- In cases where we want to spread or use information from different Excel files, we can manage them from the code (for instance creating, changing or deleting other Excel files).
- It allows creating additional interface objects.
- We can easily create and configure forms, buttons, check-boxes, … and assign them to code, creating an application interface.
- It allows interacting with external databases
- And so on…
WHY IS IT SO RELEVANT TO QUANTITATIVE FINANCE?
Excel allows managing data immediately with an excellent interface built for this purpose. As in Quantitative Finance, we need to analyze financial data, it is a good fit.
Adding programming capability to our Excel files will allow us to develop models quickly. For some of them, it might be enough to use them from Excel. For others, due to the amount of data or performance restrictions, we might need to use other programming languages or applications.
In any way, learning Excel VBA, at least the main functions, will be worth the time spent. Quickly testing ideas or building small applications for very specific tasks can be very profitable.
Excel VBA can be used as a personal tool, the same as most of us use Excel Files, but also as a development platform for creating applications for other users. In both cases, we must consider the advantages and disadvantages of using it for its design. Having a flexible interface as a spreadsheet allows us to quickly enter or edit existing data, but this can also be an issue when this data is used in the programs. So it is crucial to define which data can be changed and restrict the edition of the one that must not.
Recent Comments