Object.Property
or Object.Method
Let’s see a simple example to understand this.
Imagine the object is a car, and we want a red car, with 2 doors, and 200 horse
power engine. Those are the properties and we would assign them like this:
Car.Color = Red
Car.DoorNumber = 2
Car.EnginePower = 200
If we want the car object to drive and then stop
we could write something like this (these are methods).
Car.Drive
Car.Stop
Let’s see how that works with Excel objects. The
picture below shows some of the most common objects used in VBA macros. The
Application object represents the entire Microsoft Excel application. Within
the application, there are Workbook objects, with respective Worksheet objects,
Range objects, Cells, etc.
For example, if we want to refer to a particular
range in the active sheet we would write the following (in this case we select
that object):
Range("D4:G8").Select
Objects of the same type form a collection. For
example, the Workbook object is part of the Workbooks collection, and the
Worksheet object is part of the Worksheets collection. This means, when
referring to a particular Workbook or Worksheet we need to reference the right
name or index that represents it within the collection.
To select a particular sheet we could use the
sheet name or index to reference it in the Worksheets collection as follows:
Worksheets("Sheet1").Select
Worksheets(1).Select
Objects are arranged in a hierarchy that needs
to be followed. In order to refer to a range in other sheet we need to specify
the path within the object hierarchy using the dot separator as follows (in
this case we copy the range):
Worksheets("Sheet2").Range("D4:G8").Copy
Or if that should always apply to whatever sheet
is active, then:
ActiveSheet.Range("D4:G8").Copy
The object hierarchy can go even further if we
want to reference the range in a particular sheet of a particular workbook of
the Excel application:
Application.Workbooks("Book1").Worksheets("Sheet2").Range("D4:G8").Copy
And if you want to dive deeper into this you
should know that an object is an instance of a class, which holds the
instructions for the object to operate. Excel comes with libraries of classes
that set the basis to work with objects. Those libraries are part of the
available references we can access in the VBA editor window under Tools -> References.
All classes (and objects) and their respective members (meaning, the
properties, methods, events that apply to them) can be found in the Excel
Object Browser. Note the methods are referred to as functions or subs in the
object browser.
Properties
Properties are attributes we can get from or
give to an object. Some commonly used properties are: Name, Value, Size, Color,
RowHeight, NumberFormat, HorizontalAlignment, BorderAround, etc.
For example, the ‘Name’ property of the
‘Application’ object returns the name of the application we are using.
MsgBox Application.Name
Remember that properties can either get or
give/modify attributes. In this case, we just get the name attribute of the
Application object, and that’s displayed in a message box.
In this other example, we use the ‘Value’
property of the Range object to give or assign a value to the cell A1. Again,
we use the Object.Property structure, but here we pass or assign an attribute
(Value).
Range("A1").Value = 10
Methods
Methods are actions that can be applied to an
object. Some common methods include: Select, Clear, Copy, Cut, Paste, Open,
Close, Move, Merge, Add, Delete, etc. The below expression adds or inserts a new
worksheet in the active workbook.
Worksheets.Add
Note we wrote “Worksheets” and not “Worksheet”.
As we explained earlier, Worksheet is an object of the Worksheets collection.
Most of the properties and actions for a worksheet need to specify the
particular sheet or worksheet within the Worksheets collection.
Worksheets("Sheet1").Delete
This other example copies the whole range
(including values and formatting).
Range("A1:D4").Copy
That would take the range in the active sheet
and active workbook at the moment of running the macro. As we have seen
earlier, if we want to target a range in a particular worksheet we need to specify
in which sheet is located following the hierarchy:
Worksheets("Sheet1").Range("A1:D4").Copy
Events
Objects respond to various events that occur such
as opening or closing a file, changing the selected worksheet or range,
right-clicking or double-clicking in a worksheet, following a hyperlink, etc.
As we have seen earlier, Excel VBA allows to handle certain events for the Worksheet
and Workbook objects with event procedures.
The below example shows a Workbook_Open event
procedure that runs when the workbook opens and displays a welcoming message
box.
Private Sub Workbook_Open()
MsgBox "Welcome to my spreadsheet"
End Sub
In this other example, Excel calculates all
formulas and saves the workbook when changing the selected range or cell in a
particular worksheet.
Private Sub Worksheet_SelectionChange(ByVal
Target As Range)
Application.Calculate
ThisWorkbook.Save
End Sub
There are other events that do not apply to
neither the Workbook nor the Worksheet objects, and do not require an event
procedure to be handled. An example of these is the OnTime event of the
Application object, which runs an existing procedure (in this example
OnTimeMacro) at a certain time.
Application.OnTime
TimeValue("18:00"), "OnTimeMacro"
Another example is the OnKey event, which runs a
procedure when a particular key is pressed. In this case, it will run MyMacro
when the letter “a” is pressed in the keyboard.
Application.OnKey "a", "MyMacro"
The With statement
The ‘With’ statement is used to simplify object
references. It requires an object qualifier to follow, and everything between
‘With’ and ‘End With’ refers to that object. This is very useful when applying
numerous properties to an object and can reduce the volume of code and make it
easier to read.
The following example assigns a value and
formats the font name, size, and style of the cell A1 in the active sheet.
With ActiveSheet.Range("A1")
.Value
= "Discussion Topics"
.Font.Name =
"Arial"
.Font.Size = 14
.Font.FontStyle =
"Bold Italic"
End With
No comments:
Post a Comment