I learned VBA in 2023
2023 was a year of lots of learning... ... and one of them was learning VBA (Visual Basic for Applications).
I recently changed jobs and found myself as a Data Analyst! Changing jobs brought me new air and new perspectives, and in a new area, new seas to explore.
Of the many things I would like to share about 2023, one of them is to tell you what I learned with VBA in the 21st century. I’ll say that I don’t have complete mastery, but I had a lot of fun with it in 2023!
Disclaimer: the original text is in Brazilian-Portuguese. Some images don’t have translation (yet), and if this text needs any clarification, please, tell me!
So, first what is this VBA? And why does learning VBA seem controversial?
The famous VBA is a programming language included in Microsoft Office with the aim of automating processes and flows within these programs, or even interacting with the world beyond Office (we’ll talk about this later!). Within our context here, it was possible to create solutions for Microsoft Excel with spreadsheet automation, create front-end screens (interface that the user can interact with), data processing and so on.
It was created in 1993 so in year 2023 we celebrate 30 years of VBA! Hooray!
And although other programming languages remain relevant (Javascript is now 28 years old), the discussion about VBA being obsolete or not has been going on for a while. Within Office there are already different solutions using Add-ins and Python for Excel is coming. When I recently read the text 5 reasons to learn VBA programming in 2022 (English), I decided that I needed to write about the subject to contribute to the narrative.
Let’s go: list of 10 things I learned from VBA!
1. Programming (and in dark mode) Kinda obvious, but true!
Despite already being used to programming logic and studying web technologies, due to the new work being suitable for development in VBA, it was a motivation to be able to contribute and finish projects that are not just exercises or studies.
VBA is written as a procedural language through subroutines and functions, and also supports object orientation, when accessing objects, properties and methods of Office elements itself, but we can also create our own classes. In 2023, I’m not yet used to creating class modules, but creating different modules and accessing them from others in a parameterized way already brings a feeling of great freedom.
The editor screen is not user-friendly and does not have a dark mode:
The standard VBA screen with the Courier New font and a theme that makes your eyes hurt after 15 minutes of programming.
I dealt with this by changing the font and (super limited) colors that VBA already provides. But it is possible to directly access the VBA DLL with third-party programs that allow even greater freedom. But I’m already super satisfied with my new screen:
Using the Consolas font and a dark background has improved a lot! And with this touch of white and gray in the letters, it looks really fancy. And even accessing the DLL would make it even more stylized, but I ended up getting used to it that way.
A new great love: Dictionaries
Besides the fact that “man, programming in VBA is weird”, it has some peculiarities. And one of them worth mentioning is the way that deals with arrays. By default, we can create a String() like this to store an array, however, it will require changing the size of this array whenever it changes.
Some very kind soul thought of including Dictionary in VBA. To start using it, simply activate the Microsoft Scripting Runtime Reference. The Dictionary allows you to create an array of N lines and two columns — Key and Item, both of which can be accessed. Each Key is unique, and the Item can contain anything: a String, a Number, a Function, or even another Dictionary.
Furthermore, the Dictionary has some really cool methods like .Exists, which allows you to check if such a Key exists within that dictionary.
Aluno = Student
Notas = Grades
A simple demonstration of adding data to the Dictionary. We do not need to declare how many lines the dictionary will have, but Keys and Items are mandatory. This allows other approaches, such as performing a “from to” or for the Item to be a characteristic of the Key as in the case above.A verificação imediata é o console do VBA.
2. Single responsibility principle
In my first steps in VBA, my mentor (and also my favorite expert) already taught me how to program with the single responsibility philosophy. At first, it gave the impression that we were creating many very short Subs (subroutines), sometimes with a single line, or three, but that it was one of the best practices with a direct impact on the code and its long-term maintenance.
Furthermore, with this practice, it seemed to me that it made it easier to refactor the code. If the sub is doing more than one functionality, it’s a sign that I can improve the code. Or the opposite: if you have two subs/functions doing the same thing but only changing one detail, why not parameterize them and make them just one?
Another great advantage is when correcting or improving a specific feature of the code, we can usually edit only the specific functionality. This way we can go directly to the sub/function that performs a certain task and do what needs to be done, normally without impacting other tasks.
3. Clean code and naming conventions
When we write code, we want the other programmer to be able to understand what we are doing, and that is why we can insert comments, which are texts that do not impact the code, but are observations or explanations about some functionality.
I understand that the main philosophy of clean code is to eliminate the use of comments. In other words, the names of variables, functions and subroutines speak for themselves what they are doing and you don’t necessarily need to analyze them line by line if you just want to know the step in the code.
In the previous example, it is very clear that one sub is responsible for loading student data, and another for displaying it, right? I don’t need to write comments to say that “this Sub loads student data”. The name itself explains it.
I don’t follow the VBA community yet, but unlike other communities like Python, which has its PEP 8, I hear that everyone has their own style of writing and organization, and that’s why we adopted a convention for the sector.
Also notice that Subs start with s, and Functions with f. We use camelCase writing, which was a change in 2023 to adapt to the behavior of another part of VBA.
Additionally, we declare variables when they are used instead of making a list at the top:
4. Therefore, work as a team
These two previous learnings make teamwork very viable: we can read other people’s codes extremely easily and have very similar ways of writing and approaching. Furthermore, through conventions we can improve another author’s code without it looking like it was the work of two people. Now, we can “sign as one sector.”
These advantages become even more evident when we visit some old solutions that don’t have any of this: we spend more time trying to understand what each part of the code does than actually making the necessary improvements.
5. Validate everything possible
We have to guarantee that the code will work as we want, and to do that, we need to validate everything possible! From entering the data, checking the types… And this gets even crazier when the user is not the programmer, but someone from the front end!
In the solution that we evaluated from the students’ notes, there are possible points where the code could be buggy or not in accordance with the business rules:
- What ensures that the student name field is not blank?
- What if the note wasn’t a number, but perhaps a String (Text)?
- What if the grade entered was below 0? Or above 10?
It is possible to create validations for each type of unforeseen event, which are now no longer unforeseen because we are predicting it, and in this way raise appropriate errors so that the user knows what went wrong. We can create using some conditions and creating custom error texts.
6. Think about scalability and reuse
What if...?
It’s safe to say that all code has the potential to become something improved in the future. Whether by refactoring, which is improving the code that already exists; or to add new functionalities. It’s not uncommon for us to deliver a request and a few weeks later receive a “this is really good, but can we do ‘that’ too?”
And therein lies the danger. If we don’t write our code so that it is flexible and scalable, we would practically have to rewrite the code, or even, in a hurry, do very strange work (not to say “some awkward workaround”) and make its maintenance even more delicate.
In other words, applying the good practices mentioned so far allows us to make specific changes only to the necessary details or just include new Subs within the Main Sub. It is worth highlighting that writing the code itself (logic) is also very important.
"I could well use the same solution from another project here..."
What about when we already have a previously implemented solution and could we reuse the same logic in a new solution?
Would it be possible to reuse the code if we copypaste and adjust just a few sections? If so, wonderful! For me, it was one of my greatest achievements.
At the beginning of my learning, I was engaged in a universal “data importer” solution, but I only managed to use it once or twice for super specific solutions, perhaps still out of attachment, and then it fell into disuse.
Months later, I managed to develop a better importer (I was very excited to see it working) and, using some of the Factory Method pattern, I was able to reuse it in a different solution that had the same import logic. As the code was already very flexible, I only needed to change some nomenclatures (a sign of possible improvement in the code!) and configure new report patterns to be imported, however, the import code remained practically intact, and allowed me to add new features.
7. Work together with other Office solutions
Over the rainbow...
I recently did a little data importer project, as I mentioned in the previous learning. This tool not only imports data into Excel, but also exports data to Access!
In other words, it is possible to manipulate inter-programs using VBA, either using native libraries (references) or using third-party libraries.
I was also able to create a very simple but very interesting project, related to charging defaulters via WhatsApp. How does it work:
- Data importer that the company’s system exports in .xls with a list of customers with some data such as name, invoice due date, outstanding amount, cell phone number;
- Carry out the appropriate processing of the generated file;
- Generate a list of defaulters according to the criteria defined by the organization. (Validation!);
- Create a column in the list with the WhatsApp Web (desktop) link that leads to the contact and the message with the customer’s data, so that the user can only send the ready-made message!
Pretty cool huh? I will talk more about this tool in a future moment!
VBA is one of the three musketeers
Excel also has two other great and powerful tools: Power Query and Power Pivot.
In a super summary, Power Query is related to data processing while Power Pivot is related to creating indicators with the loaded data.
Although VBA can also perform data processing, such as importing an Excel file and writing a series of subroutines for the appropriate processing to be carried out, we have to evaluate when to do this through VBA and when to do this through Power Query. For me, the main reason I prefer to use Power Query whenever possible is because of the practicality and intuitiveness it offers. Furthermore, it is more friendly to beginners and I am sure that my future mentees (if the universe allows) will be able to enter the sector in a more didactic way through Power Query and then explore VBA.
While VBA has (perhaps precarious) conditions in dealing with Data Models, Power Pivot (DAX) already does the job in a very targeted way.
These two Excel tools are widely explored in data intelligence communities and typically targeted at Power BI, however, they are super applicable and relevant to Excel.
But what if we want to generate automated reports from data loaded into Power Query and indicators created in DAX expressions in Power Pivot? VBA could make this possible!
In other words, if we understand VBA, Power Query and Power Pivot, we can say that at least our character is no longer in the first stages of the game. We can risk saying that this is Advanced Excel. However, it is a subject for another text that deserves much more than a few paragraphs.
8. Ribbons (buttons)
Executing VBA code is not restricted to the editor interface alone. We can call codes or macros using shapes, or even more elegantly, creating a menu in Office itself.
Office Ribbons allows us to create a custom menu and each button calls a specific sub of the button. This way, the user does not need to access the editor and have to guess which code to run first. In fact, the button can determine the behavior of the code, when loading some data that specifies the type of the button.
To edit Excel Ribbons, it is through the customUI.xml file which is located within the file. To do this, you can use an editor such as Office RibbonX Editor, or open the file as a .zip and create/edit the XML.
And this gives the solutions a much more professional look, doesn’t it? In ribbons, we can create drop-down menus, checkboxes, other separations and more.
9. UserForms in VBA
No less important, but another very interesting feature of VBA, is that it is possible to create front-end screens, that is, a graphical interface so that the user can interact with a certain part of the code, or just see a “Please wait” screen while the code runs. This is very useful when creating systems.
I confess that a very big challenge, especially for us designers, is overcoming the default look of VBA. It is possible to create a very simple login screen with a very… 2000ishy look, but it is also possible to upload our own images and create a modern look. As you saw in my “please wait” screen, it is no longer fully standard VBA but still far from being a modern screen.
The trick is also to be able to carry out these visual modifications in an optimized way, not just about code or configuration, but also about time. In many cases, functionality may be much more important than a beautiful screen. Therefore, it is important to understand the project priorities before investing too much time in it.
Anyway, there are groups and serious professionals who create beautiful userforms that don’t even give rise to thinking that it’s VBA, and that makes me want to do more things!
10. Beyond Programming: Documentation
In addition to just developing tools with VBA, it is important that these tools are well written according to everything we have seen so far. But also, that they are explained outside the tool.
Each organization has its own way of documenting these processes and flows. An interesting approach would be to create a document (something like Word or Google Docs) with:
- Explanation of the purpose of the tool and the conditions for using it;
- Validations carried out;
- Points of attention in the code (sometimes it was not possible to write in the best possible way, so an extra explanation is deserved);
- And also its procedure, if it is a tool for continuous use (e.g. monthly data processing), so that someone who is only interested in running the program can carry out the process correctly even without understanding programming.
And furthermore, if the tool is part of a larger project, how about including it in a flowchart? This way, other colleagues will be able to understand what part of the project the tool fits into, and what the dependencies of that project are and where it leads, so that the path and characteristics of each element are clear:
11. There is still much more to explore! The next steps to become the king of vba! (just kidding)
And what else to explore?
I end my year 2023 with a very interesting notion in procedural programming, with a hint of object-oriented programming. In all the solutions worked on so far, I believe that I managed to deal well with the approaches and always consulted my mentor to seek best practices.
There are even more advanced and crazy solutions written in VBA that I want to be able to understand, especially to understand class modules better.
Furthermore, learning Python is a priority for 2024. I believe it will be a very interesting study for this area of automation and data analysis. Data for the next chapters!
Final thoughts
When writing this text, I realized the importance of recording progress in this learning. And in some way, it is reference material and can help other people find themselves in some way.
I would like to reinforce my gratitude to the Data Intelligence team that I am part of, they are friends who seek excellence and transparency in everything they do, values that have a huge impact on me. I don’t know what my 2023 would be without this new family.
Happy New Year!