Thinking in Terms of Objects

When you are developing applications with Excel ( especially when you are dabbling with Visual Basic for Applications - VBA), it's helpful to think in terms of objects, or Excel elements that you can manipulate manually or via a macro. Here are some examples of Excel objects:

The Excel application
An Excel workbook
A worksheet in a workbook
A range or a table in a worksheet
A ListBox control on a UserForm (a custom dialog box)
A chart embedded in a worksheet
A chart series on a chart
A particular data point in a chart

You may notice that an object hierarchy exists here: The Excel object contains workbook objects, which contain worksheet objects, which contain range objects. This hierarchy comprises Excel's object model. Excel has more than 200 classes of objects that you can control directly or by using VBA. Other Microsoft Office 2007 products have their own object models.

Note	Controlling objects is fundamental to developing applications. Throughout this book, you find out how to automate tasks by controlling Excel's objects, and you do so by using VBA. This concept becomes clearer in subsequent chapters.

Workbooks

One of the most common Excel objects is a workbook. Everything that you do in Excel takes place in a workbook, which is stored in a file that, by default, has an XLSX extension. An Excel workbook can hold any number of sheets (limited only by memory). There are four types of sheets:

Worksheets
Chart sheets
XLM macro sheets (obsolete, but still supported)
Dialog sheets (obsolete, but still supported)

You can open or create as many workbooks as you like (each in its own window), but at any given time, only one workbook is the active workbook. Similarly, only one sheet in a workbook is the active sheet. To activate a sheet, click its sheet tab at the bottom of the screen. To change a sheet's name , double-click the tab and enter the new text. Right-clicking a tab brings up a shortcut menu with additional options for the sheet, including changing its tab color , hiding the sheet, and so on.

You can also hide the window that contains a workbook by using the View Window Hide command. A hidden workbook window remains open, but it is not visible to the user .

Worksheets

The most common type of sheet is a worksheet, which is what people normally think of when they think of a spreadsheet. Worksheets contain cells, and the cells store data and formulas.

Every Excel 2007 worksheet has 16,384 columns and 1,048,576 rows. You can hide unneeded rows and columns to keep them out of view, but you cannot increase the number of rows or columns .

Note

Versions prior to Excel 2007 used the XLS binary format, and worksheets had only 65,536 rows and 256 columns. If you open such a file, Excel 2007 enters "compatibility mode" in order to work with the smaller worksheet grid. To convert such a file to the new format, save it as an XLSX or XLSM file. Then close the workbook and re-open it.

The real value of using multiple worksheets in a workbook is not access to more cells. Rather, multiple worksheets enable you to organize your work better. Back in the old days, when a file comprised a single worksheet, developers wasted a lot of time trying to organize the worksheet to hold their information efficiently . Now you can store information on any number of worksheets and still access it instantly by clicking a sheet tab.

As you know, a worksheet cell can hold a constant value or the result of a formula. The value may be a number, a date, a Boolean value (True or False), or text. Every worksheet also has an invisible drawing layer, which lets you insert graphic objects, such as charts , shapes , SmartArt, UserForm controls, pictures, and other embedded objects.

How Big Is a Worksheet?

It's interesting to stop and think about the actual size of a worksheet. Do the arithmetic (16,384 — 1,048,576), and you'll see that a worksheet has 17,179,869,184 cells. Remember that this is in just one worksheet. A single workbook can hold more than one worksheet.

If you're using a 1024 — 768 video mode with the default row heights and column widths, you can see 15 columns and 25 rows (or 375 cells) at a time - which is about .000002 percent of the entire worksheet. In other words, more than 45 million screens of information reside within a single worksheet.

If you were to enter a single digit into each cell at the relatively rapid clip of one cell per second, it would take you about 545 years , nonstop, to fill up a worksheet. To print the results of your efforts would require more than 40 million sheets of paper - a stack more than a mile high.

As you might suspect, filling an entire workbook with values is not possible. It's not even close to being possible. You would soon run out of memory, and Excel would probably crash.

You have complete control over the column widths and row heights - in fact, you can even hide rows and columns (as well as entire worksheets). You can specify any font size, and you have complete control over colors. Text in a cell can be displayed vertically (or at an angle) and can even be wrapped around to occupy multiple lines.

NEW	In the past, Excel was limited to a palette of 56 colors. With Excel 2007, the number of colors is virtually unlimited. In addition, Excel 2007 supports document themes . A single click lets you apply a new theme to a workbook, which can give it an entirely different look.

Chart sheets

A chart sheet normally holds a single chart. Many users ignore chart sheets, preferring to store charts on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to print a chart on a page by itself, and they are especially useful for presentations. Figure 2-1 shows a pie chart on a chart sheet.

image from book
Figure 2-1: A pie chart on a chart sheet.

XLM macro sheets

An XLM macro sheet (also known as an MS Excel 4 macro sheet ) is essentially a worksheet, but it has some different defaults. More specifically , an XLM macro sheet displays formulas rather than the results of formulas. In addition, the default column width is larger than in a normal worksheet.

What's New in Excel 2007?

Here's a quick-and-dirty overview of the new features in Excel 2007:

A new tab-and-ribbon user interface
New XML file formats
Worksheet tables
Significantly larger worksheet grid (1,048,576 rows — 16,384 columns)
Ability to use more memory
Unlimited conditional formats per cell
100 levels of undo
Maximum formula length increased to 8,000 characters
Supports 64 levels of nesting in a formula
Formula AutoComplete
Better-looking charts
Workbook themes
Skins
Page Layout view
New conditional formatting options
Less confusing Excel Options dialog box
New collaboration features (requires SharePoint)
SmartArt and improved WordArt
Compatibility checker
Easier pivot tables
Twelve new worksheet functions, plus integration of the Analysis ToolPak functions
PDF output
Resizable formula bar
Many new templates
More control over the status bar

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

As the name suggests, an XLM macro sheet is designed to hold XLM macros. As you may know, the XLM macro system is a holdover from previous versions of Excel (version 4.0 and earlier). Excel 2007 continues to support XLM macros for compatibility reasons - although it no longer provides the option of recording an XLM macro. This book does not cover the XLM macro system; instead, it focuses on the more powerful VBA macro system.

Excel 5/95 dialog sheets

In Excel 5 and Excel 95, you created a custom dialog box by inserting a special dialog sheet. Excel 97 and later versions still support these dialog sheets, but a much better alternative is available: UserForms. You work with UserForms in the Visual Basic Editor (VBE).

If you open a workbook that contains an Excel 5/95 dialog sheet, you can access the dialog sheet by clicking its tab.

I don't discuss Excel 5/95 dialog sheets in this book.