My Favorite Excel Feature: VBA!
Image by https://unsplash.com/@hishahadat on https://unsplash.com/
Introduction
I love Excel because of its simplicity and its ability to efficiently organize data. In this post, I'm going to share my FAVORITE feature in Excel that every data analyst should use.
What is VBA?
VBA stands for Visual Basic for Applications, and it's available in all the Microsoft Office apps. It allows you to write code that'll automate repetitive tasks and save time.
This is perfect for data analysts because they can implement VBA code to accomplish tasks such as easily formatting multiple workbooks with a single button press instead of manually changing each one.
The good news is, if you have previous programming knowledge, learning VBA will be a lot easier since many of the concepts translate to other languages like Python or Java.
Where do I type the VBA Code?
If you open up Excel and do Alt + F11, a new pop-up window will appear:
This is called the VBA Editor, or VBA Interface, and you'll be able to enter in code in the macros that you create.
What are macros?
Inside a macro, you can enter sequences of VBA code statements.
To create a macro in the VBA interface, click on the "Insert" ribbon:
Then, click on "Module". Once you do that, you should see white space that looks like this:
What are procedures?
If you have previous coding knowledge, think of procedures as functions. A procedure contains some VBA code that you can call anytime.
For example, here's a procedure that'll automatically insert headers:
It's ok if the code doesn't make sense! Basically, I inserted column names and made sure that they were on the first row of the data.
If I want to call this procedure called "InsertHeaders", then I'll go back to the Excel interface and go to the "Developer" ribbon:
From there, click on "Macros":
Then, you can select the "InsertHeaders" procedure and click "Run" (ignore the other procedures I've made):
More Excel VBA Concepts
Because Excel VBA is a broad language, there's so many more concepts to learn about! You should check out this website which explains each of the different VBA components.
I've also made YouTube playlists that you might find helpful! https://www.youtube.com/playlist?list=PL8QLO4tKii7fFGGm7H7EHdKYRWHAEDkf_ Make the caption be "Excel VBA in 30 Seconds"
https://www.youtube.com/playlist?list=PL8QLO4tKii7fhXsw1mrdpoI08oO8eUoKJ Make the caption be "Excel VBA Long-Form Videos"
If you made it this far, thank you so much for your time and hope you have a great day 😀!