Object Properties
A property refers to what an object has.
As an analogy, a car has a predefined set of properties such as color, size, type, engine, etc.
Likewise, an Excel object could have its own set of properties, such as color, font, and value.
Properties come after the object hierarchy.
This means that the object is specified first, followed by a period, then the property name, and the details or value assigned to it.
It also has to be specific in cases where there might be some ambiguity.
Taking a car and a shoe as examples:
Car.Color – might be too broad
Car.Interior.Color = Black – this makes sure that Excel knows which part of the car is being referred to.
Property Details
There are different property scenarios in Excel
- Some properties don’t have details
- Range(“A1”).Address
- Range(“A1”).Value
- Some properties return an object – this occurs when an object’s property is also an object with its own properties
- Range(“A2”).Interior.Color – The Range A2 has Interior as its property, while Interior has Color as its property.
- Range(“A2”).Font.Color
An interior property returns an interior object.
More details on this can be found through Microsoft Help.
Property Type
A property can be either read-only, write, or both.
Below are some use cases:
- Range(“A1”).Value = ActiveCell.Address
- This reads the address of the active cell and puts it as the value of cell A1.
- Range(“A1”).Interior.Color = vbRed
- This assigns the color red to cell A1’s interior.
- Range(“A1”).Font.Color = vbBlue
- This changes the font color of cell A1 to blue.
Object Methods
A method dictates what to do with an object or what an object does.
Taking a car for an example, its methods can be:
- Start
- Stop
- Crash
Method arguments
Some methods don’t have arguments, while some methods have additional arguments or information.
For example, how do you want to start the car?
Quickly or slowly?
This then becomes:
Car.Stop Quickly.
Methods can also change properties.
For a car, the “Crash” method would change the “Size” property of the car.
Ways of writing arguments
- Using a space
- Car.Stop Quickly
- Assign a name of an argument and assign a value using :=
- Car.Stop StopStyle:= Quickly
Below are some examples:
- Clear – no further arguments
- Range(“A2”).Clear
- Delete([Shift]) – can have 1 argument that determines if you want to shift to the left, right, etc.
- Range(“A2”).Delete xlShiftToLeft
- This deletes cell A2 and shifts the table to the left.
- Range(“A2”).Delete xlShiftToLeft
- Copy([Destination])– 1 argument for destination
- Range(“A3”).Copy Range(“B3”)
- This copies cell A3 to cell B3.
- Range(“A3”).Copy Range(“B3”)
- Copy([Before], [After])– 2 optional and exclusive arguments which can be written in two ways
- Sheet1.Copy After:=Sheet3
- Copy Sheet1 Sheet3.
- Sheet1.Copy , Sheet2
- Skip the [Before] argument and skip to the [After] argument
- Sheet1.Copy After:=Sheet3
- PasteSpecial – detailed pasting (formatting, transposed, etc.)
- After – detailed pasting (formatting, transposed, etc.)
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
