The objects that Excel exposes have different names, but they form an equally sensible and structured hierarchy for accessing data stored in a-tabular arrangement. Just as Word’s basic unit of information is the text segment (not characters or words), Excel’s basic unit of information is-also called Range. A Range object can contain a single cell or an entire worksheet (and everything in between).
The Application object represents an instance of Excel, and it supports most of the basic properties and methods of Word’s Application object. In addition, it supports a few more methods that are unique to Excel.Two important methods of Excel’s Application object are the Calculate method, which recalculates all open worksheets, and the Evaluate method, which evaluates math expressions and returns the result. The following statement returns a numeric value that is the result of the math expression . I passed to the Evaluate method as argument:
You can also use variables in your expressions as long as you store their values in specific cells and use the addresses of these cells.in the expression. The following statement returns the logarithm of the.numeric value stored in cell A1:
The Range object represents one or more cells, depending on the address you supply. In this example; we addressed a single cell (A1). You’ll see ROW you can address and access specific cells in an Excel worksheet in the following two sections.
The Worksheets Collection and the Worksheet Object
Each workbook in Excel contains one or more worksheets. The Worksheets collection, which is similar to Word’s Documents collection, contains a Worksheet object for each worksheet in the current workbook. To add a new worksheet, use the Add method, whose syntax is as follows:
The before and after arguments let you specify the order of the new worksheet in the workbook. You can specify one of the two arguments; if you omit both, the new worksheet is inserted before the active worksheet (and also becomes active). The type argument specifies the new worksheet’s type and it can have one of the following values:
• xlWorksheet The default value
• xlExce14MacroSheet A worksheet with Excel 4 macros
• xlExce14lntlMacroSheet A worksheet with Excel 4 international macros.
To access a worksheet, use the Worksheet collection’s Item method, passing the index or the worksheet’s name as an argument: If the second worksheet is named SalesData.xls, the following expressions are equivalent:
Application.WorkSheets.ltem(2)
and
Application.Worksheets.ltem(“SalesData.xls”)
Since Item is the collection default property, you can omit its name altogether:
Application.Worksheets(2)
Objects That Represent Cells
Excel is an application for manipulating units of information stored in cells, but the basic object for accessing the contents of a worksheet is the Range object, which is a property of the Worksheet object. There are several ways to identify a Range, but here’s the basic syntax of the Range method:
Worksheet.Range(cell1:cell2)
Here, cell1 and cell2 are the addresses of the two cells that delimit a rectangular area on the worksheet. They are the addresses of the upper-left and lower-right comers of the selection. In this section, we are going to use the standard Excel notation, which is a number for the row and a letter for the column, for example, C3 or A103. To select the 10 x 10 upper-right Section of the active Worksheet, use the expression:
Worksheet.Range(“A1:J10”)
You can also retrieve a single cell as a Range object with the Cells method, while syntax is:
Worksheet.Cells(row. col)
The row and col arguments are the coordinates of the cell as numbers. Finally, the Rows and Columns methods return an entire row or column by number. The following expressions return the third row and the fourth column as Range objects:
Worksheet. Rows(3)
and
Worksheet.Columns(“D”)
The Range object is not a collection, but you can access individual cells in a Range object with the Cells method. The Cells method accepts a single argument, which is the index of the cells in the range. The index 1 corresponds to the upper-left cell in the . range, the index 2 corresponds to the. second cell of the first row, and so on, up to the.last cell in the first row. The next index corresponds to the first cell of the second row, and so on, up to the last row. The Text property returns the cell’s contents as a string, and the Value property returns the cell’s contents as a string (if it’s text) or as a numeric value (if it’s numeric).
Another way to work with cells is to make”a selection and access the properties and methods of the Selection object. To create a Selection object ‘(which represents the cells that are highlighted with the mouse), use the Range object’s Select method:
Range(‘A2:D2).Select
This statement creates a new Selection object which you can access by name. Because a worksheet has only one selection, you don’t have to specify any arguments. To change the appearance of the Selection,for instance, use the Font properly:
Notice that the selection is always rectangular; you can’t select non-adjoining cells on a worksheet. However, you can specify a multiple-range area consisting of multiple selections. The following statements combine two different ranges with the Union method and assigns them to a new Range object:
The Union method returns a Range object, which you can use to manipulate all the cells in the Titles and Totals ranges together. For example, you can apply common formatting to all the cells in the Range.
In the following section, we are going to create a new spreadsheet from within a Visual Basic application, insert formulas, and print the document.
VB6 at Work: The Excel VBA Project
The Excel VBA application’s Make New Sheet button demonstrates how to access a worksheet, populate it with data, and then format the data. The program starts by setting the App Excel object variable, which references the Excel application.
The new spreadsheet is populated and formatted with the MakeSheet() sub-routine, whose code is shown next. The code uses the Cells collection to access, individual cells and assign their values, To format a group of cells, it creates a Range object that contains all the cells to be formatted alike, selects the range, and then manipulates the’ cells through the Selection object.
While the worksheet is being populated and formatted, Excel is running in the background. Users can’t see Excel, although they will notice activity (the disk is spinning, and the pointer assumes an hourglass shape for several seconds).
After the grid is populated, the code reads the values from the spreadsheet and displays them in two columns on the Textbox control of the Excel VBA Demo Form. To read the data, you can use different technique. The following code .creates a selection on the spreadsheet and then brings it into the Visual Basic application in a single move. The selected cells are read into the C Data array by assigning the Selection object to the name of the array:
These two statements read a range of cell values into the array. The array is created the moment it’s used and you don’t need to declare it separately. Then, you can use straight Visual Basic code.to iterate through the array’s elements and create the two columns of text, shown previously. The lines that read Excel’s cell values are shown next.
