Chapter 13: The Excel Object Model - Main Objects

In the previous chapter, we looked at the Excel object model in terms of how it works, its hierarchy, and passing parameters. In this chapter, we'll look in more detail at the main collections and objects that you will be using within your code to communicate with the Excel spreadsheet.

The objects discussed are Application , Workbook , Windows , Worksheet , and Range .

Application Object

The Application object is at the highest point in the hierarchy of the object model and represents the whole Excel application. It contains the collections of workbooks and worksheets that make up spreadsheets in Excel, and it will give high-level information about the application itself, such as the active cell (the cell where the cursor currently is) and the active worksheet (the worksheet that has the focus of the cursor). It also has methods for functions such as calculating the spreadsheet (in the same way as pressing F9 on your Excel spreadsheet) or retrieving the Open filename or the Save As filename of the Excel application. You can also quit the Excel application and close it completely. Note that the Application object has no print options, as it is only an Application object and not a worksheet or workbook.

The Application object is the default object and does not have to be specified within the syntax of the statement for some properties. For example, the statement ActivePrinter returns the details of the active printer.

Main Properties, Methods, and Collections

This section details the main properties, methods, and applications that you will use within the Application object.

ActiveCell

The ActiveCell property represents the active cell the cursor is currently on within your Excel spreadsheet. You can use it to obtain the cell address of the active cell by going to the next tier down represented by the ActiveCell and using the Address property:

 Msgbox Application.ActiveCell.Address 

This will return the address of the active cell in absolute format, for example, $C$4. Note that it will only give the address of the cell and not the full address including the worksheet and workbook references.

ActivePrinter

This property returns the name of the active printer and the connection it is using, such as LPT1 or EPUSB1 if you are using a USB port. This gives the same information as selecting the File Print option from the VBE or Excel spreadsheet menu.

 MsgBox  Application.ActivePrinter 

In my case, this displays the string ‚“EPSON Stylus CX3200 on EPUSB1: ‚½ This is useful if your code is going to print information out and you want to know where the user is going to send it on the network to be printed out.

ActiveSheet

This property represents the active worksheet being displayed in Excel. One use of ActiveSheet is to select a cell on that worksheet:

 Application.ActiveSheet.Cells (10,10).Select 

This moves the cursor to the cell 10 rows down and 10 columns across on the active worksheet.

ActiveWindow

This property represents the active window in Excel. Selecting Window from the Excel menu displays a list of all open windows. One of these will have a tick against it to show that it is the active one. You can use ActiveWindow to get the caption (title bar text) of the active window:

 MsgBox Application.ActiveWindow.Caption 

ActiveWorkbook

You can use this property to find out the name of the active workbook in Excel:

 MsgBox Application.ActiveWorkbook.Name 

This will display ‚“Book1 ‚½ or whatever your current workbook is called.

It is easy to confuse ActiveWorkbook with ActiveWindow from the previous section. On the face of it, it may look as if one workbook is the same as one window, but this is not the case. You can select Window New Window from the Excel menu and insert another instance of the current workbook. This has exactly the same information as the other window, but you can make completely different selections on it. If you select Windows from the Excel menu, there will be two windows, both based on one workbook, and either window could be the active one.

AddIns

This collection represents all the add-ins currently loaded into Excel. You can list the names of these, including the pathname they were loaded from, by using the following subroutine:

 Sub test() 
 Dim MyAddin As AddIn 
 For Each MyAddin In AddIns 
 MsgBox MyAddin.FullName 
 Next 
 End Sub 

This is very useful if your code depends on a certain add-in being available to Excel. If it has not been loaded, your code will crash. Checking in the Addins collection for it allows you to display your own message that it is not present.

Assistant

This object represents the Office Assistant, the peculiar character who appears when you click Help. You either love or hate this feature! You can use the Assistant object to customize the Office Assistant to do your bidding. See Chapter 39 on how this can be used.

Calculate

This method forces recalculation of the entire spreadsheet just as when you press F9 .

 Application.Calculate 

Calculation

This property sets the method of calculation used in the Excel application. It can be set to xlCalculationAutomatic , xlCalculationManual , and xlCalculationSemiAutomatic .

 Application.Calculation = xlCalculationManual 

This is the same as selecting Tools Options from the Excel menu and then selecting the Calculation tab and clicking the Manual Calculation button.

Caption

This property holds the caption for the Excel application that is found in the window bar for Excel. For example,

 MsgBox Application.Caption 

will display ‚“Microsoft Excel - Book1, ‚½ assuming it is done on a fresh workbook.

You can also change the caption with the following code:

 Application.Caption = "MyApplication" 

You can reset the application title by writing an empty string:

 Application.Caption = "" 

This changes only the caption, not the current workbook.

Columns and Rows

These collections represent the rows and columns of the current spreadsheet; you can use them to select individual rows or columns:

 Application.Columns(3).Select 

This selects column C, just as when you click the column border.

 Application.Rows(10).Select 

This selects Row 10, just as when you click the row border.

Dialogs

This collection gives access to the built-in Dialogs collection. See Chapter 10 for details on how to use this to display the Excel dialogs.

Help

This method will call up the standard Excel help file; or, if you have access to the Microsoft Help Compiler, you can create your own customized help system:

 Application.Help 

MemoryFree, MemoryTotal, and MemoryUsed

These properties tell you how much of your computer's memory is free, how much is being used, and what the total memory is:

 MsgBox Application.MemoryFree 

This is particularly useful if you have a large spreadsheet that requires a great deal of memory. You can have your spreadsheet check the free memory available when it loads and display a warning message if it is below a certain amount.

OperatingSystem

You can use this property to check out the operating system that Excel is currently being run on.

 MsgBox Application.OperatingSystem 

You may want a minimum version level of the operating system being used. If you are running your code as an add-in that is not part of any particular spreadsheet, and you are using API calls (Chapter 17) within your code, the effect could be critical, depending on what operating system is used.

OrganizationName

This property returns the name of the organization entered into Windows:

 Msgbox Application.OrganizationName 

There is an interesting way to safeguard your application and make sure another employee in the workplace does not take your work to a new employer when they leave. When your worksheet or add-in loads up, check the OrganizationName property. If it is not what it should be, abort the load using Quit , discussed next. This can be bypassed, but it is enough to deter the casual observer.

Quit

This method closes down the Excel application completely, just as if you selected File Exit from the Excel menu. You will still be prompted to save any unsaved files.

 Application.Quit 

RecentFiles

This is a collection of the most recent files loaded into Excel.

 Sub Recent_files() 
 For Each file In Application.RecentFiles 
 MsgBox file.Name 
 Next 
 End Sub 

This displays the recent files loaded just as when you select File from the Excel menu and look at the list at the bottom of the menu bar.

Selection

This property holds the current selection object within the Excel application. You can get the cell address through the Address property:

 Msgbox Application.Selection.Address 

This returns the address in absolute format ‚ for example, $B$1 ‚ but will not tell you which sheet it is on. You can find this out by using the Worksheet property of the selection:

 MsgBox Application.Selection.Worksheet.Name 

Sheets

This collection represents all the worksheets within the current workbook. This sounds similar to the Worksheets collection that we will be looking at later, but it does have different properties and methods. Also, there is no individual Sheet object, and there is within the Worksheets collection.

Another important difference is that you can use Sheets to print or print preview individual sheets; a Worksheet object does not have a method to do this.

You may have a need to print out only one worksheet. This is how you do it:

 Application.Sheets("sheet1").Print 

You can also preview from within your code with the PrintPreview method:

 Application.Sheets("sheet1").PrintPreview 

ThisWorkbook

This property represents where the current macro code is running:

 Application.ThisWorkbook.Name 

Undo

This method undoes the last action on the Excel application. It is the same as selecting Edit Undo.... from the Excel menu.

 Application.Undo 

UserName

This property returns the name of the user logged on to the Windows system.

 MsgBox Application.UserName 

You may want your application to check that the user accessing your spreadsheet is someone whom you wish to have access. You can do this by comparing to a list of valid names:

 If Application.UserName = "Richard Shepherd" Then 

Version

This property returns the version number of VBA being used:

 MsgBox Application.Version 

If you have written an add-in, you may want it to check for the version of VBA before it starts running so that it does not crash if an older version is being used.