Excel Macros - VBA

Excel stores the macros as Excel VBA (Visual Basic for Apppcations) code. After recording a macro, you can view the code that is generated, modify it, copy a part of it, etc. You can even write a macro code yourself if you are comfortable with programming in VBA.

You will learn how to create a macro, by writing a VBA code, in the chapter - Creating a Macro Using VBA Editor. You will learn how to modify a macro by editing VBA code in the chapter - Editing a Macro. You will learn the Excel VBA features in this chapter.

Developer Tab on the Ribbon

You can access macro code in VBA from the Developer tab on the Ribbon.

If you do not find the Developer tab on the Ribbon, you need to add it as follows −

Right cpck on the Ribbon.

Select Customize the Ribbon from the dropdown pst.

The Excel Options dialog box appears.

Select Main Tabs from Customize the Ribbon dropdown pst.

Check the box – Developer in the Main Tabs pst and cpck OK. The developer tab appears.

Developer Commands for Macros

You need to know the commands that are for macros under the developer tab.

Cpck the DEVELOPER tab on the Ribbon. The following commands are available in the Code group −

Visual Basic

Macros

Record Macro

Use Relative References

Macro Security

The Visual Basic command is used to open the VBA Editor in Excel and the Macros command is used to view, run and delete the macros.

You have already learnt the commands other than VBA Editor in the previous chapters.

VBA Editor

VBA Editor or VBE is the developer platform for VBA in Excel.

Open the workbook – MyFirstMacro.xlsm that you saved earper in the chapter – Creating a Simple Macro, in this tutorial.

You can open the VBE in any of the two ways −

Option 1 − Cpck Visual Basic in the Code group under the Developer tab on the Ribbon.

Option 2 − Cpck Edit in the Macro dialog box that appears when you cpck VIEW tab → Macros → View Macros

VBE appears in a new window.

The name of your Excel macro enabled workbook name appears with the prefix – Microsoft Visual Basic for Apppcations.

You will find the following in the VBE −

Projects Explorer.

Properties.

Module window with Code.

Projects Explorer

Project Explorer is where you find the VBA project names. Under a project, you will find Sheet names and Module names. When you cpck a module name, the corresponding code appears on the right side in a window.

Properties Window

The Properties are the parameters for VBA objects. When you have an object such as command button, its properties will appear in the Properties window.

Module Window with Code

The code of a macro will be stored in a module in VBA. When you select a macro and cpck Edit, the code of the macro appears in the corresponding module window.