An ode to VBA: Tips and hints for Microsoft's old yet still useful programming language
- Visual Basic for Applications (VBA) is a programming language that comes standard with most Microsoft Office products like Excel, Word, and Access.
- VBA lets may develop macros to automate repetitive word processing and data processing tasks and generate custom forms, graphs, and reports.
- It is incredibly powerful and lets you effectively make these products dance to your tune.
Microsoft made Visual Basic for Applications (VBA) in 1993, and it comes with all Microsoft Office products. Whether it be Access, Excel, PowerPoint, Publisher, Word, or Visio, VBA is used to build programs for the Windows operating system.
VBA allows users to personalize MS Office host applications beyond what is generally possible and is an excellent feature for individuals or companies looking to get the most out of their MS Office suite.
But how do you do that? Let's find out.
What is a VBA used for?
Visual Basic for Applications (VBA) is an example of an event-driven tool that lets you tell the computer to do a specific action or a series of actions. If you don't know, "event-driven" means that a program is mainly controlled by user events or other similar input. Event-driven programming is a fundamental idea in application development and different types of programming. A program that responds to events is also called an event-driven application.
It has led to the creation of resources like event handlers. But, we digress.
One of the most common uses of VBA is the creation of macros (or macroinstructions) by typing commands into an editing module (or having an Office product "Record" one). Macros let users more easily complete repetitive processes by crunching data, copying and pasting data, creating charts, formatting documents and sheets, and a near-infinite number of other processes. You can also change toolbars, menus, dialogue boxes, and forms with VBA. All fantastic stuff, but there is more to VBA than that too.
Most importantly, VBA can be used to make user-defined functions (UDFs), and/or get access to Windows application programming interfaces (APIs), and automate specific computer procedures and calculations.
The former would include a set of instructions developed by a programmer to help administration staff create invoices or reports and, if needed, add them as attachments to an email. Sharing data between Office products (as described above) would also require some use of VBA's integrated API functions, of course.
One of the beauties of VBA, with some slight differences in terms used, is that VBA can be used to make MS Office applications effectively "talk" to one another by sharing data or triggering events within one another. This "quasi-API" ability lets you share data between applications, such as MS Access and an SQL database. This can include an online hosted SQL database on the same network or, for more advanced users.
The sky is the limit when it comes to VBA if you are willing to take the time to learn it.
For specific sectors, how you use VBA will vary widely.
In finance, for example, since this discipline is fundamentally about manipulating massive volumes of data, VBA is a potent tool to help lighten the load.
If you already work in this sector, VBA is probably operating within at least some of the apps you use daily, whether or not you are aware of it. One key giveaway is the amount of VBA-based positions constantly being advertised.
But, we digress.
In finance and accounting, VBA can let you perform various tasks, including but not limited to the following:
- Writing macros. Accountants, commercial bankers, investment bankers, research analysts, salespeople, traders, portfolio managers, clerks, and administrators can use macros to quickly look at and change large amounts of data.
- Updating data. Excel's VBA programming language can create and manage complicated trading, pricing, and risk management models. It can also predict sales and earnings and figure out financial ratios.
- Data analysis. VBA can be used to run pre-determined analyses of data sets too. This could be working out various company account analyses as a potential stock investment, running a complex set of statistical equations, or anything else you can think of.
- Organizing data. VBA can also make lists of customer names or other content, create invoices, forms, and charts, evaluate scientific data, and control how data is shown for budgets and projections.
- Create user forms. VBA can be used to communicate with users. For example, you might need the user's first and last name to fill out a form. VBA be used to ask the user in a way that makes it easier for them to give this information.
- Ad hoc tasks. VBA can copy and paste values and change the style of all the cells on a worksheet in one go. Or anything else you need to automate.
VBA can also operate in non-Microsoft environments via its native COM interface technology, which allows commands to communicate across computer boundaries. Many companies have included VBA in their proprietary and commercial programs, including AutoCAD, ArcGIS, CATIA, Corel, raw, and SolidWorks.
To a certain extent, you can also use VBA to build your own "proprietary" software tools for any service you can think of. If you want to automate your internal processes or provide a project management suite for clients, VBA is an excellent low-cost choice.
Put simply, it is great. It is also fun to learn, honestly.
Is VBA hard to learn?
VBA can be daunting when you first learn about it but rest assured, there are plenty of free resources to get you up to speed. It is not that tricky once you get to grips with the basics.
It is considered a beginner-friendly language, and VBA coders typically do not require prior coding knowledge to master it. Moreover, the VBA community is now very well established, with many highly experienced users more than happy to share their wealth of knowledge.
VBA has also often been seen as a "gateway" programing language that gives users an excellent grounding in "coding theory" that can be applied in more complex languages like C or C++.
Numerous online forums also supply VBA code examples, allowing you to copy and paste them. Prime examples include Stack Overflow, VBA Express Forum, Excel Forum, etc.
But (and word to the wise), while these are excellent resources, use caution while utilizing code written by others.
They can include some "spammy" or even potentially dangerous bits of code that could be malicious. This is especially true if you are unfamiliar with the code's source, author, or logic.
It is always best to attempt to design your code first, then look for ways to refine or correct it once you understand the fundamentals and syntax of functions.
How do you install VBA in Excel?
In short, you don't. It comes as standard with all Microsoft Office products like Excel, Access, and Word.
Now all you need to do is fork out the cash to get yourself a copy of a version of Microsoft Office.
How do I open the VBA panel in Excel?
You'll need to open the Visual Basic Editor (VBE) to start writing or editing VBA. You can do this by either clicking the option to open it through the menus at the top of each software package or the keyboard shortcut Alt-F11. From there, you can access the wonderful world that is Visual Basic (VBA).
Your current file (Word, Excel Workbook, etc.) will continue running, but a new VBE window should pop up. When you first open it, it should open in front of any existing open files.
Like any window in the Windows Operating System, you can minimize, manually shrink or stretch, or pin them side by side. This is useful when testing your code.
Once it is open, the current projects you have open (i.e., the files) will be displayed in the upper left corner of the VBA window. You can usually identify them by their saved file name.
Automating repetitive tasks is incredibly valuable. All Office suite applications use the same programming languages and can incorporate VBA code to enhance their functionality. Due to the repetitive nature of spreadsheets, data analytics, and data organization, VBA fits Excel more naturally than other Office suite tools.
When you open the VBE graphical user interface (GUI), the pane displays the properties of the selected project in the bottom left corner. A list of properties is generated as distinct projects or workbooks. These characteristics are listed alphabetically by default but can also be sorted by category.
A new window displays in the upper-left corner when a project is double-clicked. There is no content in this section, although there are two dropdown menus labeled "(General)" and "(Specific)" (Declarations). This is the window where the VBA code can be entered directly.
Finally, the toolbar has numerous functional buttons and tools. The yellow below are the toggles for a run, break, and reset for the VBA code. The run button executes the code, the break button pauses the code's activity, and the reset button halts the code's execution and returns the process to the beginning of the code.
This might all sound a little complex, but the best way to get to grips with it is just to get your hands dirty and start coding. It'll soon all become second nature to you.
Is VBA an outdated coding language?
Many organizations still use VBA worldwide and will continue to do so as long as Microsoft Office maintains its large market share.
When local components, files, and procedures outside of Office documents are involved, Microsoft Office automation is the preferred method. This will not change within the foreseeable future.
That said, modern programming languages such as Python, C#, and R can be utilized instead of VBA. Moreover, new tools, such as Power Query, may be able to execute tasks that were previously possible only with VBA.
What are some tips to improve your VBA skills and code?
Whether you are a VBA veteran or a complete novice, the following hints and tips will prove invaluable. Every experienced VBA programmer with have their box of tricks when it comes to using VBA, but these are some of the main points we believe most will agree are "must know."
1. Failing to plan is planning to fail
Like any coding project, you must sit down and think hard about your goal. What will data inputs (and types) be processed? What outcomes or functions does the code need to perform? How will any results of calculations made by a piece of VBA code be returned to the user?
These are some of the questions you need to address before opening VBE.
If you are trying to automate a process, try to identify the key steps taking place and see where you can automate it. Let's say, for example, you want to generate a letter from an Excel spreadsheet. You know you need to have a way of getting an address for the recipient and their name and title. You'll also want a return address, date, signature, and content for it as well.
Presuming you'll be using Microsoft Word, you'll also need to figure out how to tell Excel where to put any data and information on the Word document. Will you compile everything as one long chunk of formatted text and then simply dump it into a new blank Word document?
Or will you need fields or reference anchors in a Word template to populate?
Get your ducks in a row before doing anything else.
2. Use the recorder if you are clueless about where to begin
If you have no idea where to start, there is usually a way to "record" a Macro of you completing tasks like exporting sheets, copying and pasting data, etc. This is a handy tool, but it is not foolproof.
Often, especially with Excel, the code generated will be unwieldy and chocka-block with irrelevant bits of code, like you randomly click on a cell before canceling the recording. However, it gives you some excellent insight into the "mind" of VBA.
Macros recorded in this way are also a great teaching tool for a novice. You'll see how the code is structured and its syntax and get to grips with some basic logic. However, Macros recorded in this manner are not the most efficient and often bloat.
Often, it is a far superior idea to write out your code. But, if you don't know where to start, record away.
3. The Immediate Window in VBE is a great tool
Often overlooked, the Intermediate Window is an invaluable tool when creating code in VBA. You can type almost any VBA statement in this window and get results quickly.
In the above example, we've created a simple message box. When you press enter, the Word document should display, and a message box popup will appear with the text written between the speech marks within the brackets. Pretty neat.
If you want to use the Intermediate Box for yourself, open VBE, and simply use the shortcut ALT+G (Excel and Office) and get testing.
4. Know the limits of VBA
It is important to note that although VBA is great, it does have "limitations." These are mainly to do with its age, but there are some inherent issues with it.
These include, but are not limited to:
- Memory leaking is a serious issue. If you are unaware, a memory leak is a gradual decrease in system performance over time because of the fragmentation of a computer's RAM. This is caused by poorly designed or programmed applications that don't free up memory segments when they are no longer needed.
- It does have relatively poor in-built error handling (more on that later).
- It lacks true inheritance. In object-oriented programming (OOP), inheritance is the process of basing an object or class upon another object (prototype-based inheritance) or class (class-based inheritance), retaining a similar implementation.
- It is not ideal for web development, but not impossible.
- It is not suited for building stuff outside a Windows environment.
- It can be very slow when compared to other programming languages. This is especially true when performance is degraded over time, thanks to its memory leak.
- It tends to have many DLL calls. (The dynamic link library (DLL) is a collection of small programs that larger programs can load when needed to complete specific tasks.) This can consume a ton of memory.
- There are also a series of physical limitations (like maximum string lengths, etc.) which is not unique to VBA.
4. On Error Resume Next is your friend, but use it sparingly
Nothing is worse than errors in your code crashing the whole process for no apparent reason. This could be because you haven't factored in zero or null values or the free text field contains some special characters (more on that later).
Whatever the case, minor issues you have overlooked can result in your VBA code simply sputtering and stalling.
One way to deal with this (if you are confident there are no severe bugs with your code) you can use the "On Error Resume Next" statement in VBA.
In Visual Basic, a statement is a complete set of instructions. It can have keywords, operators, variables, constants, and expressions.
This statement tells the computer to "ignore" errors thrown by the code and move on to the following line.
This keeps our program running but does not solve the underlying problem. This function is excellent when you are fleshing out your code and just want to test it from time to time.
But, before you deploy your project, you must first find the root cause of the problem.
5. Using Debug.Print and developing error catchers are better though
Like any programing language or code, having a robust way to handle errors is essential. While you can use the function mentioned above to get you out of some common hangups, it is only plaster for a broken arm.
In other words, it doesn't solve the underlying problem with your code.
This is where a well-developed error-handling section in your code comes into its own. This can be a standalone object stored in a module, or you can create error handlers ad hoc per object.
Related but separate is a standard VBA method called Debug.Print. When you run your code, this method will display the output in the immediate window instead of "properly" running in your file.
Error handling can be a dark art, but with experience, it will come as second nature to you. Get used to building them from the off, and you'll become a VBA veteran in no time at all.
An in-depth look into them is out of the scope of this piece, but there are plenty of good resources on them.
6. Make heavy use of comments
You've seen a few of these in the image for point 4 above, but you should check your code with comments whenever possible. In VBA, you simply add a single quote mark (') as the first character in a line of code. Unlike other programming languages, you don't need to close the comment; a line break is all you need.
You'll need to start each line with another quotation mark for multiple-line comments.
There are often many programmers working in a team on the same project, with each person working on a section of the program. So, well-commented functions and logic help other programmers understand the code better. Comments also help others understand the reasoning behind how to solve any problem.
If you are on your own and looking to change the code in the future, comments can also help you remember the logic you used while writing that code. Lazy programmers who don't comment on their code properly sometimes forget what they did and spend much more time figuring out what went wrong. That wastes a LOT of time.
When and where you comment can be a matter of personal choice, but it is standard to have an introduction to each object you create. When working in teams, you will likely want to add team member names, dates, etc., for bits added or changed and why.
You can also use comments to "comment out" or disable lines of code when bug finding.
7. Make sure you use clear and relevant names for variables and functions
Users will save a lot of time and be able to understand your code better if you give your VBA variables and functions names that are easy to understand. People who try to read your VBA code will have a lot of trouble understanding what words like "test1" or "first integer" for variables or functions are referring to.
It is also helpful for you later down the line when you attempt to reference them or debug your code later. Spend extra time at the start to make the code read, understand, and digest as quickly as possible.
8. Break your code into as many chunks as possible
No problem, no matter how large, is that hard if you can break it down into more easily manageable chunks? Death (of the problem) by a thousand cuts, if you like.
No matter how complicated your work problem or situation is, it's likely made up of several smaller steps or processes. So cut things up into smaller pieces.
This way of putting code together is called modularization.
Modularization has several benefits. The first is that you reuse the bits you make in other processes. This is, after all, the core philosophy behind object-oriented programming.
The second is that smaller pieces of code are also easier to test and fix bugs in.
9. Don't forget about VBA's suite of pre-existing methods and functions
VBA has a lot of pre-existing methods and functions that can be used to do many different things. Many of these can perform everyday tasks you might want to integrate into your code, like splitting strings, searching for a character, closing or opening a file, etc.
There is no need to reinvent the wheel, so make sure you know what VBA already has to offer first. This will save you a ton of time and stress.
10. Use VBA only when needed
When you learn VBA, it's normal to be excited about everything you can do with it. But keep in mind that if you use it too much, it can make your work harder.
You can, in other words, find yourself going OTT with OOP.
Specific Microsoft Office programs, like Excel, come with various built-in features that are good on their own. These tools can solve everyday problems like conditional formatting, pivot tables, formulas, data validation, form controls, etc., so you don't need to build your own.
VBA should only be used when you can't solve a problem quickly with Excel alone. Or, of course, you have a non-standard or overly complex problem to solve.
And that is your lot for today.
The above is just the tip of the iceberg regarding the beautiful world of VBA. But, we hope you have now gained an appreciation and basic understanding of it and that, just perhaps, has inspired you to dip your toe in.
Can we wish you the best of luck? You are about to enter a literal rabbit hole of despair and utter.