If you make use of Excel very often, it would be a wise decision to research macros and learn how to write it. By using Excel VBA Macro, you can make so many of your processes on the spreadsheet shorter. This will make life easier for you.
The good thing about making use of VBA to write your macros is that it helps you dictate your preferences. Another thing it does is give you access to lots of capabilities and functionalities. In this post you will learn the basics of how to write a VBA Macro in Excel.
Also read: How to Insert a Trendline in Microsoft Excel
What Is Excel VBA Macro?
Simply put, a macro is a little programming code that helps to automate tasks on Excel. Let’s make it easier for you to understand. A macro helps you to record routine steps on Excel so that you can replay them when the need arises.
VBA, on the other hand, is an acronym and it stands for “Visual Basic for Applications.” It is in this language that Excel makes records of your daily functions in its environment.
Contrary to the common notion, you don’t have to be a professional at programming to enjoy the benefits of VBA macros. Excel has made it easy by helping you generate codes through specific features. This article will show you how to maximize this.
Why Is Excel VBA Macro Important?
Assuming you are a cashier working in a water utility company. Certain clients prefer to make their payments at the bank. It is your responsibility to get this data and convert it into what suits your business requirements.
For example, if your bank wants to find out how many customers pay a certain sum for utility bills in a day, this can be done easily in Excel. Now here’s the question, how would you feel carrying out this task every day? Exactly, bored which makes the task tedious.
This is where macro come in. By programming a macro, you can get this done with a single button. Thus, the macro will help you:
- Import the data
- Format it to suit your business standards
Precautions to Take When Using Macros
As advantageous as macros are, they have a few disadvantages. For example, attackers can gain access to your system through macros. You have to enable the use of macros in your Excel window to run them.
When doing this, ensure that you only run macros from reliable sources. When saving macros, your workbook should be saved in *.xlsm, which is the macro-enabled format. You shouldn’t add any spaces to your macro name.
It’s wise when creating a macro to ensure you supply the description. This way, you and any other person who has access to the file understands exactly what that macro does.
How to Write VBA Macros in Excel
Once again, it is not as difficult as it sounds. You can use macros to merge two or more excel sheets or even identify duplicate items. This illustration will be used to explain how to write macros below.
Enable Developer Option
This is the first thing to do considering it is disabled by default. The following steps will help you enable the developer option. When you do, you can pin it to the main ribbon in your Excel.
1. Click on FILE, then select Options.
2. A new window opens. Click “Customize Ribbon.”
3. Select the “Developer” option to mark the checkbox.
4. Click OK to continue.
At this point, the DEVELOPER tab becomes visible on your Excel ribbon.
Creating a Macro
Now that you have the DEVELOPER tab, you can create a command that will help execute a program on your spreadsheet. Do this by following these steps:
1. Go to your drive C and create a folder with the name Bank Receipts.
2. Open an Excel book and save the name as “receipts.csv” file.
Following this, you should click the Developer tab. This will show you a variety of options, click “Record Macro.” A dialogue box opens following this action.
Recording a Macro
After creating the file and selecting record macro:
1. Type a name for the macro in the pop-up box that opens.
2. To use this macro in any new documents you make, be sure the Store macro in the box says “Personal Macro Workbook.”
3. Select whether you want to use a shortcut for the macro. It is advisable to leave the shortcut option blank to prevent replacing an already existing shortcut. However, in this case we choose the H key
4. Fill in the description of the macro. In this case, “highlight duplicate values of bank receipts,” and select OK.
5. Your macros start recording, so carry out all the relevant actions needed to highlight duplicates:
- Highlight the relevant data selection.
- Go to “Conditional Formatting” on the Home tab.
- Click “Highlight Cell Rules,” and select “Duplicate Values.”
- Choose how you want the duplicate values to be highlighted and select OK.
6. Go to the bottom-left of the workbook and click “Stop Recording.”
7. To ensure the Macros were recorded, go to a new workbook and use the shortcut Ctrl + H.
Conclusion
Once you know what you want to achieve with Excel, writing VBA Macros should not be a tough option. To help hone your skills, there are courses on LinkedIn Learning as well Udemy that you can try.
Our latest tutorials delivered straight to your inbox
