To start with creating the Excel VBA macro, we will create 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”
You can use the Msgbox function to provide different messages during the execution of Macro. E.g. Alert Messages, Critical Warning Messages, Decision Making by allowing user to Clik Yes, No or whatever values you want to assign.
I will cover this in another blog on how to use message box for user input during the macro execution. Till then Happy Learning !