Visual Basic for Applications, otherwise known as VBA, is the coding platform attached to Excel; it’s clunky, archaic, inconsistent, and generally considered the feeble Grandad of the coding family or perhaps the runt of the litter.
So, with that introduction, it may appear like I am very wrong when I say every engineer should know how to use VBA, but hear me out…
Why is VBA the right choice for engineers?
The creation and upkeep of engineering products finds itself mostly driven by desk-jockeys. Whether your discipline is mechanical design, electronics design, FEA, lifecycle engineering, or another from the almost infinite number of roles, I am willing to bet that after your email client and any specialist software related to your role, Excel is your next most frequently used program.
In this post, I do not aim to cajole you into dropping your day job to become a VBA developer, but rather to highlight the three biggest reasons why I think it’s worth spending a days effort on becoming proficient enough to make those monotonous tasks we have come to accept in Excel disappear.
1. You already have it.
As mentioned above, VBA is part of Excel and is installed on your computer as part of Excel. Just strike ALT + F11 or show the developer tab and you will be able to start programming in VBA.
As VBA is so readily available to the masses, there is a huge community of people out there – this is great because any time you want to do anything you are unsure of, 99 times out of 100 you will find the solution in the first 5 Google results.
One copy and paste operation later and you can continue coding.
2. You already use it.
In software development, beyond the capability of the language to solve the problem, one of the biggest things the developer has to consider is the user interface.
For Visual Basic, Excel is the user interface.
And in most businesses, almost every employee is familiar with the program and knows how to use it.
For this reason, you can create something, pass it onto someone else to use and they will not have to pause and think about how to operate your creation. Excel is immediately familiar to them and therefore people can plug and play.
Imagine you have created a spreadsheet where you enter a few design parameters and it iterates to give you the best solution.
With VBA ANY user can easily enter the parameters and click a button to find the best solution. If you need to use ‘goal seeks’ for example without VBA the user would have to perform this manually, with VBA you can automate it all.
Not only can this save time with repeated use, but it can also, more importantly, reduce errors.
If you use a different programming language you would need to make it either interact with Excel or have an entirely different executable application.
3. You can save so much time.
Thirdly, and most importantly, the biggest reason I think you should take the time to learn some VBA is the ability to save yourself enormous amounts of time.
With only a small amount of the most basic VBA knowledge, you would be well equipped to save yourself so much time on boring and repetitive tasks.
The amount of time you can save from just a few lines of code is sometimes shocking and almost always embarrassing when you realise how much of your life you have wasted processing data.
Recently I was provided many CSV files which were not delimited. I needed to break this into multiple columns but the location at which to delimit was not consistent. Writing the code to automate this took me only a handful of minutes and the subsequent running of 30+ files took me no more than 20 mins.
VBA in the real world.
Earlier I mentioned how VBA can make painful jobs disappear quickly.
Take the example I gave when I had awkward CSV files that the basic excel tools could not handle.
The data looked like something similar to the raw data in the table below, where the timestamp was an inconsistent number of characters and the identifier was a fixed character length.
The task was to separate the raw data into two separate columns, one that contained the timestamp data and another that contained the identifier.
This can be solved by using formulas quite easily but when you have tens of thousands of data points per CSV it quickly becomes a boring and time-consuming task.
With VBA you can solve this problem once and apply it to all your data sets easily and quickly.
Below you can see all the code required to perform this task…
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub example() Counter = 1 While Range("A" & Counter) <> "" tempstr = Range("A" & Counter) Range("B" & Counter) = Right(tempstr, 3) Range("A" & Counter) = Left(tempstr, Len(tempstr) - 3) Counter = Counter + 1 Wend End Sub |
It’s 12 lines, nine if you remove the blanks.
It’s simple coding and this is what I am pitching to you.
Not to be able to code The Matrix but instead with a basic understanding make your life simpler!
In word logic the code above effectively says “for each data point put the last 3 characters in column B and the rest in column A”.
It ran in 20.96 seconds for 63,000 data points!
You can see that when you need to repeat this task many times writing a few lines of code is certainly quicker than recreating and dragging down formulas. With a few more lines of slightly higher level coding, you could make this example run in just 2.23 seconds!
At the risk of sounding like a broken record, this is a very simple example of how one can implement VBA to make their life simpler and cut out the mundane Excel jobs which, at times, seem too common during the 9-5.
How hard is it to learn VBA?
The basics of VBA are FOR loops, WHILE loops, IF statements, and knowing how to interact into and out of Excel.
If you can already code in another language, you have a headstart as you will be familiar with the logic of various loops and statements.
If not, then you aren’t massively behind. The resources available to teach you how to become VBA proficient are almost indefinite and from my personal experience, I would recommend the WiseOwl tutorials.
Although there are a lot of videos in the playlists do not be discouraged. I found that after the first 15 or so I was equipped with the skillset to achieve most things and the rest was learned from Google.
If you’re keen to start learning, I recommend creating a copy of the spreadsheets and going through the video as the tutor covers it – but perhaps that is just my style of learning.
In conclusion
So, to wrap this up, you probably use Excel daily, VBA is already installed on your computer, good tutorials are free to watch, you can save time on boring tasks, and best of all you only need a basic understanding to start automating your boring tasks!
With all this being said, I hope I have made a strong enough case for you to at least think twice when you’re sat staring down the barrel of your next boring, monotonous, and repetitive task in Excel.
A final bit of advice would be if you do decide to learn VBA keep this as one of your closely guarded secrets!
When your colleagues find out about your new abilities you will forever be doing them favours and trust me from experience… I stopped counting the unrepaid favours a long time ago.
