Excel Macros Series...12

Excel VBA (Visual Basic for Applications) code

Excel stores the macros as Excel VBA (Visual Basic for Applications) 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 article - Creating a Macro Using VBA Editor. You will learn how to modify a macro by editing VBA code in the article - Editing a Macro. You will learn the Excel VBA features in this article.

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 click on the Ribbon.

• Select Customize the Ribbon from the dropdown list.

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

• Right click on the Ribbon.

• Select Customize the Ribbon from the dropdown list.

Developer Commands for Macros

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

Click 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 articles.

VBA Editor

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

Open the workbook – MyFirstMacro.xlsm that you saved earlier in the article – Creating a Simple Macro, in this article.

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

Option 1: Click Visual Basic in the Code group under the Developer tab on the Ribbon.

Option 2: Click Edit in the Macro dialog box that appears when you click 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 Applications.

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 click 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 click Edit, the code of the macro appears in the corresponding module window.