Excel VBA tutorial for beginners:

Object Properties & Methods

Understanding Objects, Properties and Methods in VBA is really important if you’d like to write your own Excel VBA code from scratch or update the VBA code you find on forums.

This Excel VBA tutorial for beginners includes two lectures from my “Unlock Excel VBA and Macros” course.

I cover objects and the Object library in detail inside the course.

This is also a key concept.

We cover this in detail as well as how to find the right object reference and the right syntax to be able to write your code.

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

  1. Some properties don’t have details
    • Range(“A1”).Address
    • Range(“A1”).Value
  1. 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.
  • Copy([Destination])– 1 argument for destination
    • Range(“A3”).Copy Range(“B3”)
      • This copies cell A3 to cell 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
  • PasteSpecial – detailed pasting (formatting, transposed, etc.)
  • After – detailed pasting (formatting, transposed, etc.)

Video

TWEET THIS EXCEL VBA TECHNIQUE

Try it yourself

I hope you liked my tutorial. What did you think of it?

Do you have any questions?

In any case, give me an idea of how this worked out by leaving a comment below.

Unlock Excel VBA & Macros Course is here!

Now available on Udemy!

Save time. Achieve more. Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Redeem your coupon for 75% off below.

Get 75% off the course
Free Ebook

Leave A Comment

Share This