VBA Macros

What is VBA Macro in Excel?

A VBA Macro is nothing but a line of code to instruct the excel to do a specific task. Once the code is written in VBE you can execute the same task any time in the workbook. The macro code can eliminate repetitive boring tasks and automate the process.

If you are new to VBA and you don’t know anything about it, then this is the article to start off your journey in macros. Ok, let’s 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. All the programming we wish to do will be done 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 task to execute in excel. To start off 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.

VBA-Macro

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For 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 don’t see this developer tab in your excel, follow the below steps to enable the developer tab in excel.

Note: I am using Excel 2016 version.

Step 1: Go to File.

Developer tab Step 1

Step 2: Under File, go to Options.

Developer tab Step 2

Step 3: Select Customize Ribbon.

You will see the Excel Options window from that select Customize Ribbon Option.

Developer tab Step 3

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

Developer tab Step 4

Step 5: Click on OK to enable it.

Now you should see the Developer tab.

Developer tab Step 5

How to Record Macros in Excel VBA?

You can download this VBA Macro Excel Template here – VBA Macro Excel Template

Example #1

Ok, we will start straight away by recording the Macro. Under the Developer tab, click on Record Macro.

VBA Macro Example 1

As soon as you click on the Record Macro, you will see excel asks you to give a name to your macro.

VBA Macro Example 1-1

Give a proper name to the macro. It should not contain any space characters and special characters. You can give underscore (_) as the word separator then clicks on OK to start the recording.

VBA Macro Example 1-2

From now onwards, the macro recorder keeps recording all the activities you do in the excel sheet.

Firstly I will select cell A1.

VBA Macro Example 1-3

Now I will type “Welcome to VBA” in A1 cell.

VBA Macro Example 1-4

Now I will stop recording under the developer tab.

VBA Macro Example 1-5

So, excel stops recording the activities we do in the excel. Now let’s see how excel recorded the activities. Under the Developer tab, click on Visual Basic.

VBA Macro Example 1-6

As soon as you click on Visual basic, we will see the below window.

VBA Macro Example 1-7

Double Click on Modules.

VBA Macro Example 1-8

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

VBA Macro Example 1-9

Macro code started with the word SUB.

All the macro has two parts to it one is Head, and another one is Tail. Every macro has a name.

VBA Macro Example 1-10

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

The first thing we did after start recording the macro is we have selected the cell A1 and excel recorded it as Range (“A1”).Select

The second activity was we have entered the value “Welcome to VBA.” As soon as we selected, then it becomes an active cell, so excel recorded the activity as ActiveCell.FormulaR1C1 = “Welcome to VBA.”

Note: R1C1 is row 1, column 1.

A third activity is after typing the word “Welcome to VBA,” we hit enter, and excel selected A2 cell.

Like this, Macro Recorder recorded all the activities we have done in the excel sheet. Now delete the word in cell A1.

VBA Macro Example 1-11

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

Note: Shortcut key to run the code is F5.
VBA Macro Example 1-12

So macro is executed, and we got the same value again. Like this, we can automate our daily routine work to save a lot of time and get rid of boring tasks every day.

Example #2 –

Now let’s 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.

Record Example 2

Click on Ok to start the recording. I will enter 1, 2, 3, and then I will 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 inert serial numbers.

Using Fill Handle, I am inserting serial numbers.

Example 2-1

Now click on stop Recording.

VBA Macro Example 2-2

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

Example 2-3

Let’s look at the code now. Firstly we have selected cell A1.

Code:

Range (“A1”).Select

Secondly, we have inserted 1 to the active cell.

Code:

ActiveCell.FormulaR1C1 = "1"

The third activity was we have selected the cell A2.

Code:

Range (“A2”).Select

The fourth activity was we have inserted 2 to the active cell.

Code:

ActiveCell.FormulaR1C1 = "2"

The fifth activity was we have selected the cell A3.

Code:

Range (“A3”).Select

The sixth activity as we have inserted 3 to the active cell.

Code:

ActiveCell.FormulaR1C1 = "3"

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

Code:

Range ("A1:A3").Select

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

Code:

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

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

Code:

Range ("A1:A10").Select

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

How to Save Macro Workbook?

Excel workbook, which contains macro code, should be saved as Macro-Enabled Workbook. Click on Save As and select the extension of the fileExtension 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.”

VBA Macro Example 2-4

Things to Remember

  • This is just the introduction part of the VBA Macro Tutorial. Keep following our blog to see more posts going forward.
  • Recording macro is the best initialization to start the journey of macros.
  • Record more and more activities and see what the code is.

Recommended Articles

This has been a guide to VBA Macro. Here we learn how to record a macro in excel to show automatically generated code in VBA editor along with step by step examplesBelow are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>