We all use Microsoft Excel in our place of work. The most common usage is for presenting data in a nice tabular format. Beyond a doubt, that is a use-case where Excel excels! (pardon the pun…)
People who start using Excel start with basic arithmetic calculations (using SUM and AVERAGE functions).
Later on, they graduate to using more complex functions such as CONCATENATE and VLOOKUP. Learning these makes you immediately realize the power that Excel has just given you. VLOOKUP mastery (especially) can make you look really good to your managers. 🙂
What I want to touch on today is how learning and using Excel VBA can make you a superstar! 🌟
What is VBA?
VBA stands for Visual Basic for Applications and it is commonly referred to as a Macro. So, what is it? It is a powerful tool to automate repetitive tasks (such as building KPI reports from raw data).
The best part about VBA is that it comes free along with Microsoft Office. To view the VBA Editor, all you have to do is enter the keyboard shortcut “Alt + F11” while Excel is open.
How I Have Used It.
I have used it quite a bit in my organization for various purposes. The most useful application has been in adding validations to Excel upload templates.
I work at a logistics warehousing solutions company as the Warehouse IT Head. People here use Excel templates to create various documents in our core warehousing application. In the last 2 years I have created VBA-validated versions of all these templates. We achieved 2 things as a result.
- The number of failed uploads (due to incorrect input) has dramatically reduced. This has saved time and therefore cost.
It has also reduced the IT Help Desk service calls (more time and cost savings). - Training new users on how to use the templates has become very easy.
Who Should Learn It.
Ideally, everyone! 😛
Yes, yes, I know that is not practical. My hope is that this post will inspire you to at least make an attempt at learning it. I will explain how I learned it in a bit.
I believe that, at a minimum, your IT applications guy should know VBA. They should then be tasked to go around and automate others’ Excel work. As I mentioned earlier, doing this has great productivity and cost benefits.
How I Learned It.
I used the traditional way of learning. Through a book.
Specifically, I used Excel 2010 Power Programming with VBA by John Walkenbach. A superb book where the author gradually takes you from the basics to the more advanced topics. I suggest you have a read and see what I am talking about.
Once I had read through most of the book, I found areas of my work which I could automate. To begin with, I started with automating some of my “simple” calculation based repetitive tasks.
Then I started looking beyond my personal area and towards my team’s tasks. Wherever I could, I made macros to help them save time. The expressions I saw on their faces when I changed 20 minute tasks to 1 second clicks were priceless! 💎
In Conclusion
Learning Excel VBA is not easy. But I guarantee that you will be amazed by the benefits you will get from learning it. If that is not possible, get your organization to invest in VBA training for your IT department.
I will be sharing various code snippets in my future posts. So stay tuned! All you have to do is experiment and keep an open mind. You will reap the benefits (and adulation ❤️).