What is VBA Macro in Excel?

If you are new to VBA and do not know anything about it, this is the article to start your journey in Macros. So, let us start the journey of your coding class today.

VBA Visual Basic for Applications is the Microsoft programming language for Microsoft products like Excel, Word, and PowerPoint. It will do all the programming we wish to do in the VBE (Visual Basic EditorVisual Basic EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more). It is the platform to write our code of tasks to execute in Excel. To start with VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more in Excel, you need to record a Macro.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Macros (wallstreetmojo.com)

Enable Developer Tab in Excel

VBA coding is available under the Developer tab in excelDeveloper Tab In ExcelEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more.

If you do not see this developer tab in your Excel, follow the below steps to enable the Developer tab in Excel.

Step 1: Go to File.

Step 2: Under File, go to Options.

Step 3: Select Customize Ribbon.

You will see the Excel “Options” window from that select Customize Ribbon option.

Step 4: Check the box Developer Tab to enable it.

Step 5: Click on OK to enable it.

Now you should see the Developer tab.

How to Record Macros in Excel VBA?

Example #1

We will start straight away by recording the MacroRecording The MacroRecording macros is a method whereby excel stores the tasks performed by the user. Every time a macro is run, these exact actions are performed automatically. Macros are created in either the View tab (under the “macros” drop-down) or the Developer tab of Excel. read more. Then, under the “Developer” tab, click on Record Macro.

As soon as you click on the “Record Macro,” Excel asks you to give a name to your Macro.

Give a proper name to the Macro. It should not contain any space characters or special characters. For example, you can give underscore (_) as the word separator and then click on “OK” to start the recording.

From now onwards, the macro recorder keeps recording all your activities in the Excel sheet.

Firstly, we will select cell A1.

Now, we will type “Welcome to VBA” in the A1 cell.

Now, we will stop recording under the “Developer” tab.

So, Excel stops recording the activities we do in Excel. Now, let us see how Excel recorded the activities. Under the “Developer” tab, click on “Visual Basic.”

As soon as you click on “Visual Basic,” we will see the below window.

Double click on “Modules.”

Now, we will see the code on the right-hand side.

Macro code started with the word SUB.

All the macro has two parts: the “Head” and the “Tail” because every macro has a name.

In between the head and tail of the macro, excel recorded all the activities.

The first thing we did after starting recording the macro was we selected cell A1, and Excel recorded it as Range (“A1”). Select.

The second activity was when we entered the value “Welcome to VBA.” When we selected it, it became an active cell, so Excel recorded the activity as ActiveCell.FormulaR1C1 = “Welcome to VBA.”

A third activity is after typing the word “Welcome to VBA,” we press the “Enter” key, and Excel selects the A2 cell.

Like this, the Macro recorder recorded all our activities in the Excel sheet. Now, delete the word in cell A1.

After deleting the word, go to VBE, where our code is once again. Click on the “Run” button to enter the same text value to cell A1.

So the macro is executed, and we get the same value again. Like this, we can automate our daily routine work to save time and eliminate boring daily tasks.

Example #2 –

Now, let us record one more macro to understand better. In this recording, we will insert serial numbers from A1 to A10.

Go to the “Developer” tab and click on the “Record Macro” option, as shown in the above example.

Click on “OK” to start the recording. First, we will enter 1, 2, 3, and then drag the fill handleFill HandleThe fill handle in Excel allows you to avoid copying and pasting each value into cells and instead use patterns to fill out the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications.read more to insert serial numbers.

Using “Fill Handle,” we will insert serial numbers.

Now, click on “Stop Recording.”

Go to “Visual Basic Editor” and see what the code is.

Let us look at the code now. Firstly, we have selected cell A1.

Code:

Range (“A1”).Select

Secondly, we have inserted 1 into the active cell.

ActiveCell.FormulaR1C1 = “1”

The third activity was when we selected cell A2.

Range (“A2”).Select

The fourth activity was we inserted 2 into the active cell.

ActiveCell.FormulaR1C1 = “2”

The fifth activity was when we selected cell A3.

Range (“A3”).Select

In the sixth activity we inserted 3 into the active cell.

ActiveCell.FormulaR1C1 = “3”

Then, we selected the range of cells from A1 to A3.

Range (“A1:A3”).Select

After selecting the cells, we filled the serial numbers using the fill handle.

Selection.AutoFill Destination:=Range(“A1:A10”), Type:=xlFillDefault

So finally, we have selected the range A1 to A10.

Range (“A1:A10”).Select

So, you can run this code whenever we want to insert serial numbers from 1 to 10 in cells A1 to A10.

How to Save a Macro Workbook?

One must save the Excel workbook containing macro code as “Macro-Enabled Workbook.” Then, click “Save As” and select the file’s extensionExtension Of The FileExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code.read more as “Macro-Enabled Workbook.”

Things to Remember

  • It is just the introduction part of the VBA Macro tutorial. Keep following our blog to see more posts in the future.Recording macro is the best initialization to start the journey of macros.Record more and more activities and see what the code is.

This article has been a guide to VBA Macro. Here, we learn how to record a macro in Excel to show automatically generated code in the VBA Editor, along with step-by-step examples. Below are some useful Excel articles related to VBA: –

  • VBA Delete SheetGoal Seek in Excel VBAVBA Operators