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.
  • Click the DEVELOPER tab on the Ribbon.
  • Click Visual Basic in the Code group.
Objects

The VBA editor window opens.

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.

  • Click Module1.

  • Click the View tab on the Ribbon.

  • Select Code from the dropdown list.

Code

The code of your macro appears.

Code of Macro

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 click the command button.

Inserting a Command Button

  • Create a new worksheet.

  • Click in the new worksheet.

  • Click the DEVELOPER button on the Ribbon.

  • Click Insert in the Controls group.

  • Select the button icon from Form Controls.

Inserting Command
  • Click in the worksheet where you want to place the command button.
  • The Assign Macro dialog box appears.
Button1_Click

The Visual Basic editor appears.

Visual Basic

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_Click () 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_Click (). A message box with the given string will be displayed when the command button is clicked.

Message Box

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.

  • Click the Run tab on the Ribbon.

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

Macro From VBA

You can see that the button is selected. Click 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.

  • Click somewhere on the worksheet.
  • Click the Button. The Message box appears on the worksheet.
Macro From Worksheet

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

Advertisements