Automate your excel sheets with VBA
Automate anything and everything
VBA (Visual Basic for Applications) is widely used for automating MS-Office products. Most of the time, when you are working with loads of data and want to automate your tasks, VBA comes in very handy.
Last week, I had to work on dozens of workbooks and had to do some process of data cleaning over them. If I would have done it manually by going on each workbook and cleaning it - the same process for dozens of times, it would have sucked all my energy and would have taken hours to get all done and dusted.
To avoid this tedious process of cleaning, I decided to go for a shortcut. And, that was writing a VBA script! And, that made the whole process super fast. I was done with my task in a few minutes. Not only it will save you time, but also bring a serious cut on efforts. And, all it takes will be some good knowledge of VBA script and you will rock at work.
It all starts with Macros
As per Microsoft’s definition,
a macro is an action or a set of actions that you can run as many times as you want
Let’s say, you get a weekly report and it needs some fixes on formatting issues. Every time making those formatting issues correct manually will consume time and you will get bored of doing the same task again and again. Hence, creating a macro for the task will come in very handy.
Every time you get the weekly report, run the macro, and you are done!
Macros are just small programs that you can run and those macros will perform certain actions. Let’s say, your task is to go to column A of your Excel dataset and lookup the cell values from other sheets. The lookup results are stored in Column B.
So, you can write a VBA code (that will be your macro) and run it. Hence, the Vlookup will be performed automatically. Not a thing to do!
This is just a simple example that I am talking about. There can be complicated tasks that may require formatting, cleaning, and even filtering. So, going with the manual approach is too hefty. Hence, VBA comes to the pitch.
Okay, I have talked about VBA a lot now. Let’s play with the coding exercise now. I am going with the Vlookup exercise only for now.
Record Macro is the easy way!
Excel provides a feature that will help you in creating your macro. No need to write the code. It will be handled by Excel itself.
It is an amazing feature. So, if you have to look up a column’s values from another column and you do it regularly, a macro will be very useful for you.
But, how to record a macro?
Very Simple! Go to Developer > Record Macro. And, all your key actions will be recorded and converted into VBA code. Super Amazing, right?
These are the two sheets (sample data) — at one I will be bringing lookup results and will perform the lookup from the right sheet.
So, after you start recording, you have to do the same you would do manually. Go to column B, set the column name at B1 and then, apply vlookup formula to the B2 cell, autofill the column, converting the column to values, and replace #N/A by blanks.
Go to stop recording and now, you can see the macro code. In my case, it looked like.
It is the code that Excel generated after I started recording. Hence, you can use this macro or its code to the same task on any excel sheet.
The other way i.e the hard way is to write code by yourself from the scratch. But, the Record Macro feature might not be as dynamic as you want it to be. But, at least it gives you a blueprint and you are good to go with changes and the baseline.
So, next time you are looking to make things fast. Or, if you are bored with doing the same stuff on excel files again and again, just remember Microsoft has provided some beautiful features to us for crazy automation. That is Macros.
Peace!
