If you use Excel more often than not, it might be a wise decision to learn how to write macros. Using Excel VBA Macros, it is in fact possible to shorten many processes and perform complex operations on the spreadsheet. This will definitely make your work more effective and easier!
Write VBA macros in Excel it can give you access to many capabilities and features. In this post, you’ll learn the basics of how to start writing a VBA macro in Excel. You don’t necessarily have to become an expert VBA programmer, you just need to understand the basics of how it works and then you can also rely on experts and ask for advice on forums such as ExcelVba.it where you can receive 100% free support.
What is an Excel VBA Macro?
Simply put, a macro is a small programming code that helps automate tasks on Excel. A macro helps you record routine steps in Excel so you can replay them when needed.
VBA, on the other hand, is an acronym and stands for “Visual Basic for Applications“. It is in this language that Excel records your daily functions in its environment.
Contrary to popular belief, you don’t need to be a programming pro to enjoy the benefits of VBA macros. Excel has simplified the process by helping you generate codes through specific features. Furthermore, on the internet you can find ready-to-use VBA Macro codes that can be adapted to your needs.
Why is Excel VBA macro important?
By programming a macro you will be able to carry out 10 or 100 steps that you used to do in Excel in just one click! The macro helps you do all these operations for example with just one click:
- Import the data
- Format the data to fit your company standards
- Perform all the operations you want
But as in all things, along with the advantages there are some disadvantages. For example, attackers could access the system via macros. You will then need to enable the use of macros in the Excel window to run and protect them.
Also make sure you only run macros from trusted sources. When saving macros, the workbook must be saved in * .xlsm, which is the macro-enabled format. There is no need to add spaces to your macro name.
When creating a macro you should also make sure to provide the description. This way, you and any other person who has access to the file understands exactly what that macro does and avoids doing “damage”.
How to Write VBA Macros in Excel
Let’s reiterate that it is not very difficult to write VBA macros. It’s simpler than you might imagine. You can use macros to merge two or more Excel sheets or even to identify elements duplicates. Below we see how to start writing these VBA macro codes.
Enable developer options
The first thing to do before writing macros is to activate the developer option in Excel, considering that it is disabled by default. The following steps will help you enable the developer option.
1. Open Microsoft Excel and click FILE –> Options.
2. On the left of the Options window click on “Ribbon customization”
3. On the right, select the “DEVELOPMENT” box
4. Click OK to continue
Now in the Excel toolbar and in the Excel menu you will see the DEVELOPMENT item
How to create a macro
Now that you have the DEVELOP tab, you can create a command that will help you run a program on your spreadsheet. Do it by following these steps:
1. Go to your C drive and create a folder with the name “Bank Receipts” (example)
2. Open an Excel file and save the name as “receipts.csv” file.
Next, you need to click on the Development tab. This will show you a variety of options, click on “Record macros“. After this action a dialog box will open.
How to record a macro
After creating the file and selecting macro recording
1. Type a name for the macro in the window that opens
2. To use this macro in all new documents you create, make sure the macro is saved in “Personal Macro Folder”.
3. Select whether you want to use a shortcut for the macro. It is recommended that you leave the shortcut option blank to prevent replacing an existing shortcut. However, in this case we choose the H key.
4. Fill in the macro description. In our example, for example, we want to “highlight duplicate values present in bank receipts” and select OK.
5. Start recording and perform all the actions you usually do to highlight duplicates:
- Highlight the selection of relevant data.
- Go to “Conditional Formatting” on the Home tab.
- Click “Highlight Cell Rules” and select “Duplicate Values.”
- Choose how you want to highlight duplicate values and select OK.
6. Go to the bottom left of your workbook and click “Stop recording“.
7. To make sure the macros have been recorded, go to the new workbook and use the shortcut Ctrl + H. Here you will find the VBA code of your macro.
As you can see, you will have written a VBA macro without using the programming language but simply using the “macro recording” function!