Creating a Macro Using VBA Editor

You can create a macro by writing the code in the VBA editor. In this chapter, you will learn where and how to write the code for a macro.

VBA Objects and Modules

Before you start coding for a Macro, understand the VBA Objects and Modules.

Open the macro-enabled workbook with your first macro.

Cpck the DEVELOPER tab on the Ribbon.

Cpck Visual Basic in the Code group.

The VBA editor window opens.

You will observe the following in the Projects Explorer window −

Your macro enabled workbook – MyFirstMacro.xlsm appears as a VBA Project.

All the worksheets and the workbook appear as Microsoft Excel Objects under the project.

Module1 appears under Modules. Your macro code is located here.

Cpck Module1.

Cpck the View tab on the Ribbon.

Select Code from the dropdown pst.

The code of your macro appears.

Creating a Macro by Coding

Next, create a second macro in the same workbook – this time by writing VBA code.

You can do this in two steps −

Insert a command button.

Write the code stating the actions to take place when you cpck the command button.

Inserting a Command Button

Create a new worksheet.

Cpck in the new worksheet.

Cpck the DEVELOPER button on the Ribbon.

Cpck Insert in the Controls group.

Select the button icon from Form Controls.

Cpck in the worksheet where you want to place the command button.

The Assign Macro dialog box appears.

The Visual Basic editor appears.

You will observe the following −

A new module – Module2 is inserted in the Project Explorer.

Code window with title Module2 (Code) appears.

A sub procedure Button1_Cpck () is inserted in the Module2 code.

Coding the Macro

Your coding is half done by the VBA editor itself.

For example, type MsgBox “Best Wishes to You!” in the sub procedure Button1_Cpck (). A message box with the given string will be displayed when the command button is cpcked.

That’s it! Your macro code is ready to run. As you are aware, VBA code does not require compilation as it runs with an interpreter.

Running the Macro from VBA Editor

You can test your macro code from the VBA editor itself.

Cpck the Run tab on the Ribbon.

Select Run Sub/UserForm from the dropdown pst. The message box with the string you typed appears in your worksheet.

You can see that the button is selected. Cpck OK in the message box. You will be taken back to the VBA editor.

Running the Macro from Worksheet

You can run the macro that you coded any number of times from the worksheet.

Cpck somewhere on the worksheet.

Cpck the Button. The Message box appears on the worksheet.

You have created a macro by writing VBA code. As you can observe, VBA coding is simple.