How to Create a Macro in Excel VBA ?

To start with creating the Excel VBA macro, we will be creating a simple display message macro.

Create Macro
Using Excel VBA we can automate tasks in Excel by writing macros. A set of VBA code instructions to automate the repeated tasks or complex, time consuming tasks.

1. Press Alt + F11 to view the Visual Basic Editor
2. Select VBAProject("your excel file name")
3. Right click on on the name
4. Click Insert -- Module

Module - the codes written will be saved in the modules. you can create multiple modules, under same file to manage your codes.

On the blank editor window write below code:

Sub DisplayMessage()

MsgBox "My First VBA Macro!"

End Sub

In above code the DisplayMessage is our macro name. After executing this macro, it will give us a pop-up message with "My First VBA Macro!".

How to run this macro from Excel :

1. You can assign the code to the command button or any excel object.

2. Insert text box in excel, rename it as Display Message.

3. Right click on text box and click Assign Macro

4. Select this workbook and you will be able to see the macro name created by us

5. Select the macro name

Now if you hover the mouse over the text box, the mouse arrow will change to hand icon which means the text box is now clickable.

Now you can click on the text box and the macro will be executed. You will receive a pop-up message saying, "My First VBA Macro!"

Message box in excel VBA

The MsgBox dialog box in Excel VBA can be used in many ways to provide the required information to the users. Wherever they need to know and take actions based on that.

1. Write a simple message in Excel VBA, MsgBox "First message box code". When executed, it will display the below messagebox:

2. Combining the message box to display the value in the specific cell. If you want to display the updated or changed value in cell B4, we can use the code, MsgBox "The value in cell B4 is " & Range("B4").Value

Here in this code, we have used the & operator to concatenate the text and the value is cell B4.

3. Writing the message on the multiple lines we just need to concatenate the text with vbNewLine. for e.g., 

MsgBox "This is my first Line" & vbNewLine & "This is my second Line"

Use of Workbook and Worksheet Object

In Excel VBA we use the worksheet object to give references while working with the code. In Excel VBA there can be a object hierarchy i.e., an object can contain another object and that object can contain another object and so on.

The hierarchy starts with Excel itself, say excel is at the top of the hierarchy as an application object. The application object contains other objects. e.g., the workbook object excel file. This excel file can be any file which is open or created by us.

Now further the Excel workbook object, contains other objects, such as worksheet. Further the worksheet object contains Range objects.

The flow is like below:

Application object --> Workbook object --> Worksheet object --> Range object

When we write Range("B4").Value in the VBA code, it actuually refers as:

Application.Workbooks("your file name").Worksheet(1).Range("B4").Value

the objects as connected by dots.

Don't worry about the object hierarchy much, as you don'y need to write the code this way. This is just for your knowledge, that how the object hierarchy is connected and works.

Use of properties and methods

The collection of workbooks or worksheets have Properties, while methods are something which perform some actions with help of properties. We can see few examples below to understand this.

1. We can use the count property of the worksheets collection to count the number of worksheets in a workbook.

Sub WorksheetCount ()

MsgBox worksheets.count

End Sub

This code when executed, will give us the number of worksheets present in the workbook.

2. Similarly we can use the Add method of the workbooks collection to create a new workbook.

Sub AddWorkbook

Workbooks.Add

End Sub

This code will add the new workbook, in existing open application.