How to Start Writing VBA Macros in Excel

If you use Excel a lot, it might be a wise decision to learn how to write macros. Using Excel VBA Macro , it is possible to shorten many processes and perform complex operations on the spreadsheet. This will surely make your job more effective and easier!

Writing VBA macros in Excel can give you access to a lot of capabilities and features. In this post, you will learn the basics of how to start writing a VBA macro in Excel. You will not have to become an expert VBA programmer, you just need to understand the basics of its operation 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 in Excel. A macro helps you record routine steps in Excel so that you can play them back 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 professional to take advantage of VBA macros. Excel has simplified the process by helping you generate codes through specific features. Also on the internet you can find VBA Macro codes ready to use and adaptable to your needs.

Why is the Excel VBA macro important?

By programming a macro you can perform 10 or 100 steps that you previously did in excel in just one click! The macro helps you to do all these operations, for example with a single click:

1. Import the data
2. Format the data to fit your company standards
3. Carry out all the operations you want

But as in all things, along with the advantages there are some disadvantages. For example, malicious users 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 reputable sources. When saving macros, the workbook must be saved in * .xlsm , which is the macro-enabled format. You don’t need to add spaces to your macro name.

When creating a macro you must also be sure to provide the description. This way, you and any other person who has access to the file understand exactly what that macro does and avoid doing “damage”

How to write VBA Macros in Excel

We reiterate that it is not very difficult to write VBA macros. It is simpler than you can imagine. You can use macros to merge two or more Excel sheets or even to identify duplicate items . 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 it is disabled by default. The following steps will help you to enable the developer option.

1. Open Microsoft Excel and click FILE -> Options.
2. On the left of the Options window, click on ” Customize Ribbon“
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 DEVELOPMENT 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 leaf and save the name as a “receipts.csv” file.

Next, you need to click on the Developer tab. This will show you a variety of options, click ” Record Macro “. 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 if 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 description of the macro. 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 usual actions 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 the duplicate values ​​and select OK.

6. Go to the bottom left of the workbook and click “ Stop Recording“.
7. To make sure the macros have been recorded, go to the new working folder and use the shortcut Ctrl + H. Here you will find the VBA code of your macro.

As you can see, you have written a VBA macro without using the programming language but simply using the “macro recording” function!