Excel In Your Office

Hello….…..

We already saw little bit of introduction about VBA introduction here and here and VBA appearance. Before diving head first into the coding, let's finish introduction with another short post.

VBA is an Object Oriented Language (not cent per cent, but that wouldn't affect our learning). Object is something like a tool or a thing that has certain functions and properties, and can contain data. For example in Excel, single cell is an object. Collection of cells is another object; a sheet is an object etc. Likewise, everything you see in Excel is represented by an object in VBA. Nearly everything you do in VBA will be with objects.

So, object represents an element of Excel, such as a cell, a sheet, a workbook. A collection is an object that contains several other objects, usually of the same type; for example, all the Sheet objects in a workbook are contained in a single collection object called Sheets.

Objects will have properties, Methods and Events. Using properties and methods, you can modify a single object or an entire collection of objects.

Property:

A property is an attribute of an object or an aspect of its behaviour. For example, properties of a workbook include its name, number of sheets, and it’s save status, as well as whether change tracking is turned on. To change the characteristics of an object, you change the values of its properties.

Method:

A method is an action that an object can perform. For example, Excel can update the formula calculations automatically every time you change a related cell value. This is a method for Application object.

In most cases, methods are actions and properties are qualities. Using a method causes something to happen to an object, while using property returns information about the object or it causes a quality about the object to change.

Event:

An Event is an action in Excel front end, initiated either by user action or by other VBA code. You can use the events to trigger a procedure that tells Excel what to do. Remember the post on why use macros? I mentioned that you can use Excel’s events if you want to protect a sheet automatically whenever the book is saved.

Saving a book is an event. You can write code to protect under this event so that Excel always runs the code when the save button is clicked (of course, on pressing of Ctrl + S too).

Events and event procedures are the same for versions 97 through 2007. No significant changes have been made to the event model since its introduction in Excel97. A few new events have been added, but the overall structure of the event system is unchanged.

We will see about various events and coding structure for events during our plethora of future posts.

Object Model:

Excel has an object model. The object model is a big hierarchy of all the objects that you can use in VBA. At the top of this hierarchy is the Application object, and all the other objects are below it. To use the object properties and methods etc, you will have to drill down to that object from top.

The dot operator (.) is used to navigate through the hierarchy. You connect objects with a dot to get to lesser objects. For example, if you want to apply red font colour in cell A1.

A cell in VBA is represented by object named Range. Range has a property called Font. Font is also an object which has a property Color. You can set value of this property to a value that returns red colour. Red colour in VBA is represented by RGB (255,0,0).

We have Application as a higher level object. Application has a property ActiveWorkBook. Using this you can work with whichever book was active when the macro is run. ActiveWorkBook contains a property ActiveSheet which works with whichever sheet is active when the macro is run. Range object is a sub set of ActiveSheet.

So we will start with Application object and reach the Font object gradually. Our code should look like below.

Application.ActiveWorkbook.ActiveSheet.Range("A1").Font.Color = RGB(255, 0, 0)

That's it. You just wrote a code that will colour Range A1 in red.

Default Objects

Whenever you’re dealing with Excel’s object model, there are always default objects. For instance, the Application object is always assumed, regardless of where your code is. The path to the Font object could be written as;

ActiveWorkbook.ActiveSheet.Range("A1").Font.Color = RGB(255, 0, 0)

Notice that Application object is not used above. When you omit the Application object, Excel assumes that it’s there. There would not be any change in what this line of code does.

Standard Modules

When you’re in a standard module (like Module1), there are some more default objects at your disposal. When you omit the workbook reference, Excel assumes you want the ActiveWorkbook. Below code is the same as the previous two snippets.

ActiveSheet.Range("A1").Font.Color = RGB(255, 0, 0)

You can omit the worksheet reference and the ActiveSheet will be assumed. Yet another equivalent code snippet is;

Range("A1").Font.Color = RGB(255, 0, 0)

That sure is shorter than the line we started with. Don’t get too excited, there’s a downside. When you omit the workbook reference in the ThisWorkbook module (not standard module like Module1), the workbook that contains the code is assumed, not the ActiveWorkbook. Similarly, omitted sheet references in a sheet module are assumed to be the sheet which module the code is in, not the ActiveSheet. So I suggest using the long line with entire code till you get used to the object structure. Following full hierarchy is called Fully Qualified Reference. There is a problem with this too. Your code is going to be ten times longer. But that’s ok for starters, right?

Knowing the VBA Object Model is half battle won. Just check the VBA help (through the famous F1) for Object Model and you can see what all objects are at your disposal. VBA help is pretty much comprehensive.

Getting Help on objects, methods, and properties

Until you become familiar with the Excel object model, there are a few tools you can use to help you to drill down through the hierarchy.

Auto List Members: When you type a period (.) after an object in the Visual Basic Editor, a list of available properties and methods is displayed. For example, if you type Application followed by a dot, a drop-down list of methods and properties of the Application object is displayed.

If Auto list members are not displayed, click on Tools > Options > Editor tab > Check Auto List Members box.

Help: You can also use Help to find out which properties and methods can be used with an object. Each object topic in Help includes a 'See Also' jump that displays a list of properties and methods for the object. Press F1 in the Object Browser or a module to jump to the appropriate Help topic.

Object Browser: The Object Browser in the Visual Basic Editor displays the members (properties and methods) of the Excel objects. You can access this by pressing F2 in VBA.

In the next post on VBA, we will record a macro for few actions in Excel and analyse the code to get deeper in to the VBA world. Stay tuned!!