VBA Macros
It is a human-readable programming language that can be used to write a series of instructions to automate simple and complex tasks in Excel.
What are VBA Macros in Excel?
Let me guess; you are new to this entire VBA scenario and looking to find out what it exactly is. Perhaps your boss has asked to prepare a macro that will copy data from one file to another.
Either way, you are not sure where to begin. If that's the case, let me tell you that you are in the right place.
VBA also called the Visual Basics for Application, is a human-readable programming language that can be used to write a series of instructions to automate simple and complex tasks in Excel.
When these sets of instructions or actions are grouped, it is called a macro which can be run an unlimited number of times to execute a task.
For example, suppose you have the financial model for Nike Inc. It’s possible to auto-populate most of the numbers on the template with just a click of a button. All that’s left is to go through the assumptions and fine-tune the model.
Now, how you would run the macro or even write to begin with might be one of your questions. But worry not; we will try to cover all the basics of macro in this article so that it can act as a launching pad for writing really good code!
Key Takeaways
- VBA is a human-readable programming language that can be used to write a series of instructions to automate simple and complex tasks in Excel.
- A macro is a group of instructions that can automate a repetitive task.
- To write a macro, you must ensure that the Developer tab is enabled from ‘Customize Ribbon.’
- Once you have enabled the Developer tab, you have two options: record the macro or write your own code.
- If you prefer recording the macro, it's necessary to remember that the code will not automatically adjust to the data dimensions, i.e., it won’t be dynamic.
- Instead, you can record the basic structure of the code and then add supplemental snippets of code that will make the code dynamic.
- This way, even if the data dimensions change, i.e., from B2:B6 to E2:E7, the code will automatically identify the range and take the next series of actions.
- To run a macro, you can either press the F5 key when the VBA editor is open or create a button, assign the macro, and run the code easily.
Enabling the Developer tab in Excel
One of the first things you must do before creating a macro is, enable the developer tab.
If you cannot see the Developer tab on your screen, all you need to do is right-click on the Home tab > Customize the Ribbon as illustrated below:
This will open up the window to ‘Customize the Ribbon’ wherein you need to tick the checkbox for the ‘Developer’ tab.
Once done, click on Ok. Now you will find that the Developer tab is present right after the view tab all the way above the spreadsheets.
We can move towards writing our very own macro using the VBA.
Recording the VBA Macro
Let’s say you still don’t feel like writing a macro…yet. So instead, you wish to explore the other options available for you to write those series of automated instructions.
In this case, let us introduce you to this super cool method of automating a task by demonstrating how it gets done in Excel.
What we are talking about is recording the macro and then running it to perform the same task on a different dataset.
To record a macro, click on Developer > Record Macro, which will start storing the series of actions in the database.
For example, if the task is to bold the text string and use the yellow color cell fill-in range B2:B4, just demonstrate in Excel how it's done after clicking the Record button.
While recording, the dialog illustrated below will open up, for which you need to click Ok.
The set of instructions can be broken down into four steps:
- Selecting the range B2::B6
- Using the bold format on text
- Using the yellow color cell fill for range B2:B6
- Finally, click elsewhere to deselect the range B2:B6.
Once you have demonstrated these instructions to Excel, again click Stop Recording, which will save those instructions for you.
Next time, if you run the macro, it will automatically bold the text in range B2:B6 and add a yellow color cell fill.
However, all the other cells on the spreadsheet will remain unaffected since we did not assign any actions that bring changes to them.
Writing your First Macro
Recording a macro is okay, but it isn’t always the feasible option. I mean, if you think logically, the data requirements will always change.
For example, let’s take the previous example. We had only evaluated the range B2:B6 for the text strings and tried to use the bold format along with cell fill. What if the data is present in C2:C6 or even E2:E6?
In that case, we want Excel to identify where the data is present dynamically and then bold the text along with the cell fill.
And if you are wondering, then let me tell you that it is only possible by writing your very own VBA code. Of course, you can record the macro to form a basic structure, but in the end, the fine tunings must be made by you.
To write the macro, click Developer > Visual Basics, which will open the window as illustrated below:
Next, click Insert > Module and start writing your code.
For example, if you write a code that automatically identifies the first text string along with a couple of formattings, then the code would look as illustrated below:
This code will automatically capture the first non-empty cell from column A: AB, select the entire range of data, bold the text strings, and finally use a cell fill with blue color.
For example, we have the data illustrated below:
To run the code, click Run > Run Sub/Userform or press the F5 key. You will find that the code automatically runs a series of actions in mini seconds to give the result:
This is one of the simplest tasks you can perform using VBA; imagine only if you could harness the true power of macro!
Running the VBA Macro
Generally, there are two ways to run a macro in Excel.
You open the VBA editor, press the F5 key, or click Run > Run Sub/Userform. The other option is to create a button, assign the macro, and just click on it to run the code.
The latter option removes all the human interaction parts from further automating the code, which is why most users prefer adding a button to run the macro.
To create a button, click Developer > Insert > Button, which will open a dialog box as illustrated below:
Select the Macro code we wrote earlier to assign it to the button, then click Ok.
Now, just click on the button, and you will find that the macro runs to give the result:
You can even rename the button or make other customizations, such as using an icon as a button to edit the size of the button to fit a particular cell.
We have a plethora of articles on the basics of VBA coding that you can access to learn basics and grow as you learn.
All we ask of you is to take that initiative, spend 20 mins of your time daily, and in no time, we assure you, you will be a level 99 Excel wizard!
or Want to Sign up with your social account?