Excel

Microsoft Excel 9.0 Object Library

Location C:\Program Files\Microsoft Office\Office\EXCEL9.OLB

Microsoft Forms 2.0 Object Library

Location C:\WINDOWS\SYSTEM\FM20.DLL

Microsoft Graph 9.0 Object Library

Location C:\Program Files\Microsoft Office\Office\GRAPH9.OLB

Microsoft Office 9.0 Object Library

Location C:\Program Files\Microsoft Office\Office\MSO9.DLL

Microsoft Outlook 9.0 Object Library

Location C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\msoutl9.olb

Microsoft PowerPoint 9.0 Object Library

Location C:\Program Files\Microsoft Office\Office\msppt9.olb

Microsoft Scripting Runtime

Location C:\WINDOWS\SYSTEM\SCRRUN.DLL

Microsoft VBScript Regular Expressions 5.5

Location C:\WINDOWS\SYSTEM\VBSCRIPT.DLL\3

In order to use a class you need to set a reference to it, i.e. use Automation (Object Linking & Embedding Automation) a feature of the Component Object Model (COM), that applications such as Office use to expose their objects, methods, and properties to the macro language.

For example, a spreadsheet application might expose a worksheet, chart, cell, each as a different type of object. A word processor might expose objects such as an application, document, paragraph, bookmark, or sentence. Automation can use the OLE IDispatch interface to gain access to objects that implement this interface. Although programmers know object members (methods and properties) by name, IDispatch keeps track of them internally by a number (as you would expect) - the Dispatch ID (DISPID). Note that the OLE IDispatch interface Errors see here.

Here is the C language take on this, lets not forget that it is the C programmers that provide the Tools for VB programmers to make there job easier, through the intergration of library files with C code. They are the tool makers, we are the Application makers.;

Before an OLE Automation controller can find a method or property, it must have the DISPID that maps to the name of the member. Once it has the DISPID, it can then call IDispatch::Invoke to locate the property or invoke the method, packaging the parameters for the property or method into one of the IDispatch::Invoke parameters. The object's implementation of IDispatch::Invoke must then unpack the parameters, call the property or method, and handle any errors that might occur. When the property or method returns, the object passes its return value back to the controller through an IDispatch::Invoke parameter.

EARLY & LATE BINDING.

Early binding is the friendly name for what C programmers call Virtual Function Table Binding or V-Table binding. So the VTable is a table of pointers to functions that the interface supports.

In order to use Early binding, the controlling application (Automation Server) must establish a reference, as I have mentioned above:

i) Type Library [.TLB] > A file or component within another file that contains standard

descriptions of exposed objects, properties, and methods that are available for Automation.

It is essentially a repository.

ii) Object library files [.olb] > contains type libraries,

iii) dynamic-link library [.DLL] > which defines the objects, methods, and properties of the

server application (The application that exposes the automation object(s)).

These library files are typically found in the Applications directory, have a look for your Excel applications Directory and you will see; EXCEL9.OLB, MSOUTL9.OLB, the Excel & Outlook object library files. If you read my take on API calls you will know that the function calls typically look in the Applications directory.

Benefits of Early Binding

Performance:

Depending on what your code is doing, early binding may significantly improve the

speed of your code.

Compile-time syntax checking:

Syntax errors that you make in Automation code will fail at compile time rather than

at run time.

Code readability:

When you declare object variables as specific types, you can simply glance at those

declarations to determine what objects a particular procedure uses.

Viewing objects:

When you've set a reference to an application's type library, its objects and their

properties and methods show up in the Object Browser.

To find out what properties and methods are available for a particular object, just check

the Object Browser (F2 in VBA). Note right click gives you a help option.

BUT not all properties have help files available. For more info you need:

Microsoft Office 2000 Visual Basic for Applications Language

Reference ISBN 1-57231-955-0

Getting help:

You can get help on another application's object model from the Object Browser, rather

than having to launch the application itself. If you have your VBA editor setup as to

list memebers then typing the . forces excel to evaluate and display the properties.

Always good practice to have this set. To set this up goto

Tools > Options > Editor tab

Then click [AutoList members]

With IntelliSense on, you will be provided with drop-down options within the code

window for simplified code writing. This is an invaluable setting you MUST HAVE ON!!

Late Binding

Late binding declares a variable as an Object or a Variant.

The variable is initialized by calling the GetObject or CreateObject functions and specifying the Automation Programmatic Identifier (ProgID).

To allow access to a COM (Component Object Model) object regardless of where it is

located, COM implements a mechanism to identify where the executable implementing a

given COM object is. This mechanism means COM is not restricted to any given path for

storing an object. To do this, COM requires that every non-private object has a unique

identifier. This identifier is called a GUID (Globally Unique Identifier). This is a 128bit number

(thus potentially allowing for a maximum of 2128 possible COM objects) To make it easier

to write a GUID, it is normally expressed as a hexadecimal number with some formatting applied. This form of a GUID is also referred to as a CLSID (Class ID) where the GUID is the identification number for a COM object.

An example CLSID looks like this;

CLSID for the Microsoft Excel 9.0 Object Library:

{00020813-0000-0000-C000-000000000046}

The formatting shown corresponds to the COM GUID type:

Type GUID

Data1 As Long 00020813 4 bytes

Data2 As Integer 0000 2 bytes

Data3 As Integer 0000 2 bytes

Data4 (0 To 7) As Byte C000-000000000046 1 byte

End Type

How would you like to remember something like this !! No way, hence the ProgID. COM supports the concept of Binary Compatibility where two objects with different GUIDs can be used interchangeably. Don't forget computer language is essentially BINARY !

To allow for Binary Compatible objects and to make it easier to get at COM objects, a higher

level of abstraction from the GUID exists. This is the ProgID (Programmatic IDentifier).

The Programmatic ID is a string of the form:

"Project.ClassName" Much easier to remember !?

For example, the ProgID for the Excel automation object is Excel.Application. In a Visual Basic project, the ProgID is determined in a logical way: from the Project Name and the Class Name with VBA it is the Application name.

So for example,

EXCEL

Application Excel.Application, Excel.Application.9

Workbook

Excel.AddIn

Workbook

Excel.Chart, Excel.Chart.8

Returns a workbook containing two worksheets;

one for the chart and one for its data.

The chart worksheet is the active worksheet.

Workbook

Excel.Sheet, Excel.Sheet.8

Returns a workbook with one worksheet.

This information is stored in the registry like this:

HKEY_CLASSES_ROOT\CLSID\{00020820-0000-0000-C000-000000000046}\Typelib

Note: All Excel objects have there own CLSID eg;

DataTable, WorksheetFunction, Pivottable, Range, button etc

they all belong to the Excel TypeLib files eg.

HKEY_CLASSES_ROOT\Interface\{0002087D-0000-0000-C000-000000000046}

Default "Button"

Also Note that DATA4 Type remains constant @ C000-000000000046

In fact Microsoft Office applications expose their functionality as a set of programmable objects (Classes), so you would expect this. Every unit of content and functionality in Office is an object

that you can programmatically examine and control, which is why you don't have to select the

object to manipulate it (a common mistake) just set a reference to it. A workbook, a document, a table, a cell, and a paragraph are all examples of objects that are exposed by Microsoft Office applications.

VIEWING THESE CLASSES

While in the VBA Editor press F2 = Object browser (TIP).

you should be given this:

Classes, Early & Late binding, GUID and COMs

Each one of the above you could dedicate a few pages or books to! The following is by no means the definitive for the above but was aquired by reading, experimenting and researching via the Regedit editor. Hopefully this will give you a general understanding of what a class is and it's relationship to automation (COM) and GUID.

1) First I'll discuss a little about the above and then

2) Give you some code to use the GUID and automatically set up references.

1) From Help

[class]

[The formal definition of an object. The class acts as the template from which an instance of an object is created at run time. The class defines the properties of the object and the methods

used to control the object's behavior.]

[End Help]

So VBA class modules define the properties and methods of an object, but you cannot use them by themselves to manipulate those properties. An object's definition is sometimes called an object class. A good analogy is to think of VBA class modules, and thus object classes, as a "Bread Tin", I'm a Production manager for a Bread Baking Plant :-).

A Bread Tin sets/defines what a particular loaf will look like (you can have different shape, size, type loaves etc depending on the Tin), but it is not itself a "Loaf of Bread" and you can't have a "Loaf of Bread" without a Bread Tin, an object (Tin) that defines the product (bread).

In the case of VBA class modules, you define a set of properties, including their data types and whether they are read-only or read/write, as well as methods, including the data type

returned plus any parameters the methods might require.

This is done for you and defined in, Object library files (.olb) or dynamic link library (.DLL)

If you have a look @ your VBA Reference Table, available via Tools > References you will see something like these as References to these library files;

So Basically;

1) Office applications register all of their classes in the Windows system registry.

2) Each class is associated with a globally unique identifier (or GUID) called a class

identifier (CLSID).

3) In the registry, each class identifier (or CLSID) is mapped to a programmatic identifier

(or PROGID) and to its application.

So you have the relationship:

PROGID <===>

CLSID ====>

SERVER

Excel.Application <===>

{00020820-0000-0000-C000-000000000046} ===>

C:\Program Files\Microsoft Office\Office\EXCEL9.OLB

When you create an instance of the class, COM looks up the ProgID "Excel.Application" which gives it the GUID of the object. COM can then locate the GUID and therefore the path to the object.

Note that the ProgID doesn't have to specify the version number there is a VersionIndependent value @

HKEY_CLASSES_ROOT\CLSID\{00024500-0000-0000-C000-000000000046}\VersionIndependentProgID

Value = Excel.Application

This is why I have set this (Major version) to a lower number in the Reference code I use to reference Library files.

So now we can use the above names in combination with the 2 functions below to reference an Automation object;

CreateObject — Creates a reference to a new automation object.

GetObject — Activates an object that has been saved in a file

or references a server application that is already running.

eg.

Dim XlApp as Object

Set XlApp = CreateObject ("Excel.Application")

'Activate a saved file

Dim xlApp as Object

Set xlApp = GetObject("C:\XcelFiles\MyFile.xls", Excel.Sheet")

Or to activate an application that is already running:

Dim xlApp as Object

Set xlApp = GetObject(,"Excel.Application")

Late binding was the first binding method implemented in Automation controller products (OLE). Late binding is the friendly name for what C programmers call IDispatch-based binding. It uses a lot of overhead and is faster than Dynamic Data Exchange (DDE), but slower than Early binding. Late binding is available in all products capable of being an Automation controller. With late binding in Visual Basic, your project does not need a reference to the Automation server's type library and you can declare your variables as type Object.

Dim oExcelApp as Object

When you declare a variable as type Object, Visual Basic cannot determine at compile time

what sort of object reference the variable contains, so binding occurs at run time, hence the

relative speeds between early & Late binding. When you call a property or method of an object with late binding, the process by which Visual Basic makes the call is twofold:

1) Gets pointer to the object, to get the name of that property or method.

2) Execute the property or method.

A variable declared as an object always contains a pointer to a (Remember C language

(API functions) are essentially C language, Pointers are evident in C) IDispatch interface where as a variable declared as a Certain Class always has a pointer to that Classes interface.

Late binding is most advantageous when you are writing Automation clients that you intend to be compatible with future versions of your Automation server. With late binding, information from the server's type library is not "hard coded" into your client so you can have greater confidence that your Automation client can work with future versions of the Automation server without code.