|Back to Excel Homepage||Excel VBA - Reference Guide|
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:
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.
A Property is an attribute of an object, e.g. the colour of a car, the name of a worksheet.
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
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).
Worksheets.Add Before, After, Count, Type
The following example will place 2 new sheets after
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.
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.
|VBA Keywords: Application, WorkBook(s), WorkSheet(s), Range, Font, Border, Interior, Select, ActiveCell, Add.|
© copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.