Recording macros

Looking at the code

Ways of running macros

Where macros are stored

Reasons to write macros

Writing macros

Procedure types

Visual Basic editor (VBE)

Rules & conventions

Excel objects

Range/Selection objects

Object hierarchy

Object browser

Chart objects

Pivot Table objects


Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables



Message Box

VBA Input Box

Excel Input Box

Making decisions (If)

Making decisions (Case)

Looping (Do...Loop)

Looping (For...Loop)

With...End With blocks

User defined functions

Event handling

Error handling


Creating User Forms

DAO/ADO Objects

Input/Output Files


Other links

Example code snippets

Userform input example






Object Hierarchy

Most applications consist of many objects arranged in a hierarchy.


 Objects, Methods, Properties and Variables

Each line of code generally has the same structure (which is also known as Syntax). VBA is loosely based around the concept of Object Orientated Programming (OOP) and the following syntax is used to define how you write code using any of the libraries that are loaded.




The square brackets wrapped around the sub_Identifier is the convention meaning it is optional and therefore not always required.


An Identifier and sub_Identifier can be one of three types:


1.  Property

2.  Method

3.  Event


Similar to physical objects such as a car or a chair, the application objects, as listed above, have Properties and Methods (as well as Events)














Identifying the Objects, Methods and Properties from the previous example.



  1. Create a new blank workbook.

  2. Click on the Tools menu, select Macro and choose Visual Basic Editor.

  3. Click on the Insert menu and select Module.


A Property is an attribute of an object, e.g. the colour of a car, the name of a worksheet.

Object.Property = Value


Car.Colour = Red

Worksheets(“Sheet1”).Name = "My Sheet"


The following example will change the name of “Sheet1” to “My Sheet”.



A Method is an activity that an object can be told to do, e.g. accelerate the car, select a cell, insert a worksheet, delete a worksheet.






The following example will select a range of cells (A2 to A10) in the current worksheet.

 Methods that contain arguments

There are methods that contain many arguments, for example inserting a worksheet(s).  The numerous arguments contain information about how many worksheets you would like to insert, the position of the worksheet(s) and the type of the worksheet(s).


Object.Method Argument1,Argument2,...



Worksheets.Add Before, After, Count, Type


Add a new worksheet.


Before which worksheet?

After which worksheet?


How many worksheets


What type of worksheet ie worksheet, chart sheet etc

The following example will place 2 new sheets after Sheet 2.

Worksheets.Add, Sheets(“Sheet2”), 2


§   The comma after Add represents the "Before" argument.

§   If "Type" is omitted, then it will assume the Default Type.  The Default Type is xlworksheet.


Sub Insert2Sheets()

      Worksheets.Add Sheets("Sheet2"), 2

End Sub



Events are like Methods the only difference being when and who/what calls this action. It is an action like a method but the system will trigger it for you instead of the user invoking the action.


This is very useful when a system changes state and needs to automate a procedure on behalf of the user.


In fact, there are many events being triggered all the time; users are simply not aware of this unless there is a procedure to trigger it. The system constantly listens for the event to take place.


When you use standard Excel features like Data Validation or Conditional Formatting, the system automatically creates code for an event so when users enter a value in a cell it will automatically trigger the feature and validate and format the active cell without a user calling the a macro manually. This type of event is normally known as 'Enter' for a Worksheet.


There are many predefines events which have no code just a starting and ending signature and users need to add code in between these signatures. Take a look at Event Handling for more information.



Next Topic: Object Browser


Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: Application, WorkBook(s), WorkSheet(s), Range, Font, Border, Interior, Select, ActiveCell, Add.
Home | Terms of Use | Privacy Policy | Contact

© copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.

All trademarks are copyrighted by their respective owners. Please read our terms of use and privacy policy.