VBA in Excel is a topic that switches off many Excel users, unfortunately programming languages tend to have that effect on people! However learning VBA in Excel, even the basics and a few commands, can drastically improve your value as an Excel analyst and even your quality of life by freeing up your time…what have you got to lose?
What is VBA in Excel?
To start right at the beginning VBA stands for Visual Basic for Applications. VBA refers to the programming language that can be used within Microsoft Excel along with various other Microsoft Office products such as Word, Access, PowerPoint and even Outlook.
As far as programming languages go it is considered one of the easier languages to grasp, which is good news for us! The reason for this is that VBA is an event-driven programming language meaning that the flow of your program is determined by events such as clicking the mouse or entering a formula and pressing keys, this makes it easier to understand because it forms a logical flow.
For example if you want to change the colour of a cell on your Excel Worksheet to blue you would complete the following steps:
- Select the Worksheet
- Select the Cell to change colour
- Set a new Fill colour for that cell
As VBA is an event-driven programming language it operates on the same process. The VBA to change a cell colour to blue in Excel is:
Sheets("Sheet1").Range("A10").Interior.ColorIndex = 37
While this language may not be fluent to you right now it is possible to break it down. First we select the Worksheet, “Sheet1”, then we select the cell or range which is “A10” and lastly we tell Excel to change the colour of the cell to blue. I know 37 may not seem like blue but colour indexes are available online if you want to check!
VBA or VB?
You may hear others refer to VBA as Visual Basic or VB for short and while they are fundamentally the same programming language, both derived from Basic, they have a key difference:
- Visual Basic: This can create an executable file or application that can be sent to someone to open, they would need no other software to open this.
- Visual Basic for Applications: This language will program Excel, or other products like Word, PowerPoint, Access and Outlook. However you require the software for it to run, i.e. you need Excel.
What does VBA in Excel actually do?
At a simplistic level VBA gives you the power to direct Excel. You program in a set of instructions such as building a chart from a set of data and formatting it to your company colour scheme and Excel will repeat that task whenever you like, as often as you like, and it will do it very, very fast!
This makes Excel VBA great for:
- Repetitive tasks like regular reporting or jobs where you do the same process frequently such as inserting company logos and information on every worksheet.
- Complicated tasks like looping through tens of thousands of cells and testing a complex condition, something a human would take days and days to complete.
Why bother to learn and use VBA?
The benefits of knowing some Excel VBA are probably becoming clearer to you but here are a few of the key benefits to keep in mind:
- Learning VBA will give you a more exciting life: Who out there actually enjoys completing the same repetitive tasks every day, week or month? It’s boring and takes up too much time to achieve a small result. Learning some VBA will help you automate those boring repetitive Excel tasks and free up your time to work on more exciting projects.
- Learning VBA will give you more money: Yes that’s right a bit of VBA knowledge will boost your bank balance over time. Excel users are quite common, after all Excel is used in almost every office around the world, but Excel users with VBA knowledge and the power to automate and build more complicated projects are a rarer breed and by default a more expensive breed! Having VBA on your CV will guarantee that you stand out and enable you to command a bigger salary.
- Learning VBA will improve your future: If you work in an office environment one of the key recent topics is automation. Robotics, Automation, Deep Learning and the like will all take jobs out of the office-place and anyone who understands some automation such as VBA is sure to have better job security in the future. Excel is going nowhere for now so pitch yourself at a more expert level to guarantee work in the years ahead.
That’s by no means an exhaustive list, I expect you have several other ideas on how you can benefit already.
Excel VBA sounds great, are there any disadvantages?
Excel VBA can speed up your tasks, complete repetitive tasks, perform calculations on hundreds of thousands of values, it all sounds amazing! Well yes it is amazing but there are a few downsides that are worth knowing:
- Other users must have a copy of Excel if you want them to be able to use your VBA programs. In an office environment this is usually not an issue but if you are creating VBA programs outside of that to a wider audience it is a limitation.
- Writing VBA to start with requires some knowledge, this means you have to make a little effort and give up a bit of time to start with in order to learn.
- VBA is only as perfect as the code you write. If you write an error into your code and fail to spot it then Excel will continue to produce that error over and over again. Equally if you fail to future-proof your VBA code, which basically means considering how your data may change over time, then you can have VBA code that suddenly stops working one month and has to be edited.
Can I just use the Excel Macro Recorder?
Excel has a built-in feature that records the tasks you complete on screen, it is quite a neat little tool if you have never used it before and can be found in the Developer tab on the Excel Ribbon.
You simply press record then perform your tasks and when complete press to stop recording and Excel will generate a Macro that will perform those exact same tasks again, in the same order you carried them out.
Under the hood of this Macro is VBA programming, Excel kindly creates it for you as you record your tasks.
So I hear you thinking (or many in the offices saying!) that you don’t need to learn VBA, the Excel Macro Recorder can do it for you, unfortunately while this may be true of the most simple of tasks it really doesn’t cut if for doing anything even slightly complicated.
The problem with Excels Macro recorder is that while it may be OK for the most basic tasks it is a very crude way of generating the VBA programming. Every scroll movement is recorded, any mistakes or mis-clicks are recorded and you have some huge limitations in that in the event your data changes even slightly like a new row for a new month your macro will no longer work.
Let’s not be totally negative about the Excel Macro Recorder, when you are beginning it can be a useful tool for training and understanding some of the basic VBA programming. For example if you want to learn how to put a formula into a cell with VBA you can record yourself entering the formula first, stop recording and then view the VBA code that has been created…you might have to sift through a bit of noise but it is a helpful learning tool.
- In summary Excel VBA is an event-driven programming language contained within Excel and other Microsoft Office Applications like Word, Access and PowerPoint.
- An event-driven programming language follows a logical flow that imitates clicks, keystrokes and other events.
- Using VBA in Excel is a fantastic way of automating repetitive tasks or solving complicated projects that would be too time-consuming for a human to complete. It will also help you become more useful and valuable around in the workplace.
- Excel VBA has a few disadvantages in that you have to spend a little bit of time learning it and the end result is only as good as your programming skills.
- The Excel Macro Recorder should not be seen as a replacement for knowing VBA, it is far too restrictive. Instead consider the Excel Macro Recorder a useful training tool.
- Next in the series will be a look at the Visual Basic Editor in Excel, I hope you are excited as you will be up and running some of your own VBA scripts in no time 🙂