When you are developing applications with Excel (
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.
Controlling objects is fundamental to developing applications. Throughout this book, you find out how to automate
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:
XLM macro sheets (obsolete, but still supported)
Dialog sheets (obsolete, but still supported)
You can also hide the window that contains a workbook by using the View
Hide command. A hidden workbook window remains open, but it is not visible to the
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
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
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
As you know, a worksheet
It's interesting to stop and think about the actual
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
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.
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.
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
Figure 2-1: A pie chart on a chart sheet.
An XLM macro sheet (also known as an
MS Excel 4 macro sheet
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
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
Supports 64 levels of nesting in a formula
Page Layout view
New conditional formatting options
Less confusing Excel Options dialog box
New collaboration features (requires SharePoint)
SmartArt and improved WordArt
Easier pivot tables
Twelve new worksheet functions, plus integration of the Analysis ToolPak functions
Resizable formula bar
Many new templates
More control over the status bar
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.
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.