Excel VBA Dot Notation

Basic Description

Excel VBA uses dot notation to separate the various things you can access and manipulate with the programming language. Dot notation is hierarchical, and usually starts with an object. (In Excel, an object is the thing you’re trying to manipulate, such as a worksheet.) After the object, you type a dot. You then specify what you want to do with this object, or what you want to manipulate. The doing is called a method. The manipulating is done via properties or parameters. If all this is confusing, let’s try and clear it up.

Think of a television. This is an object. We can notate it like “tv.“. OK, all very simple so far. But you’ll need some more information if you were going to buy a television. One thing you may want to know is how big the tv To add a size property, you’d do this: tv.size

You’d want this to equal something, though, so add an equal sign and a size: tv.size = “55 inch”

We now have an object (the tv) and a property (the size). We also have a value for the size (55 inch). If we wanted to buy this tv then we’d be doing something (buying). We can call this “doing” a method. It is a method of the tv: tv.buy

Methods can come with extra settings, called parameters. A parameter of the buy method could be PaymentType. The PaymentType would then come with its own values (credit card, cash, cheques, etc). We could represent all this as follows: tv.buy PaymentType:=Cash

We have a space between the method (buy) and the parameter (PaymentType). The value for the parameter comes after a colon and equal sign (:=), with no spaces in between.

We could add more parameters for buy. For example, we could have a Discount parameter, and a DeliveryCharge parameter:

tv.buy PaymentType:=Cash Discount:=No DeliveryCharge:=No

Notice that we’ve used a space to separate the three parameters and their values. So, back to Excel. In the VBA programming language, you’ll uses these object, methods, properties and parameters a lot. As an example, there’s an object called ActiveCell. This is the cell where your cursor currently is. The ActiveCell object can have a font set for it: ActiveCell.Font

Fonts have a name property all of their own. So after another dot, you type the Name property:

ActiveCell.Font.Name

Because it’s a property, you need a value for it. Let’s add the name of a font: ActiveCell.Font.Name = “Times New Roman”

This sets “Times New Roman” to be the font of the ActiveCell. We can also set a bold value for the Font:

ActiveCell.Font.Bold = True

Again, we have an object called ActiveCell. This is followed by the Font property. The Font property has a bold property of its own. This is set to True.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment