Excel VBA - Free online reference guide (Object Hierarchy)

 

VBA HOME PAGE

Menu

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

Formulas

Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables

Arrays

Collections

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

Debugging

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.

 

OBJECT.Identifier[.sub_Identifier]

 

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)

Object

Property

Method

Car

Colour

Accelerate

ActiveCell

Value

 

Worksheets(“Sheet1”)

 

Select

 

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

 

Examples

  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.

 Properties

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”.

 

 Methods

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.
 

Object.Method

 

Car.Accelerate

Range("A2:A10").Select

 

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,...

 

Example:

Worksheets.Add Before, After, Count, Type

Add

Add a new worksheet.

Before/After

Before which worksheet?

After which worksheet?

Count

How many worksheets

Type

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

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.