Excel's programming model centers around the Range object, which represents any single cell or adjacent or nonadjacent blocks of cells on a worksheet. The Range object allows you to retrieve or set data in cells as well as apply formatting to cells in a worksheet. When you work with a worksheet on screen, you can select a range of cells and then hold down the CTRL key while selecting another range of cells. Each range of cells selected is a contiguous block. In this case, if you set a variable to the selection in the active window in Excel, the Range object would contain a combination of cells from both cell blocks. You can iterate through the cells in the range to return data such as a cell's address to apply settings to all cells simultaneously.
You can access the Range object in several ways, such as from the selection in the active window, a hyperlink object, and a worksheet object. The following line of code provides the most common access to the Range object. The code assumes that the active sheet is a worksheet rather than a chart. The Cells property returns a Range object representing all cells in the active worksheet.
ActiveSheet.Cells |
TIP
The code listed here that describes how to insert and manipulate workbook content in Excel is also found in the XlContnt.bas code module in the Chapter 6 practice folder on the CD that comes with this book.
As you learned in the Quick Guide at the beginning of this chapter, there are multiple ways to access and manipulate content. If you start with a new workbook in Excel, the following lines set the same cell to the same value:
ActiveSheet.Cells(1, 1).Value = "Region"
ActiveSheet.Range("A1").Value = "Region"
ActiveSheet.Cells.Range("A1").Value = "Region"
|
In fact, because the Value property on the Range object in Excel is the default property, you really don't need to specify Value in the lines above. If you remove the .Value from each line, they'll still be functionally equivalent. Each way is inherently different and suited for specific scenarios and for developing more compact, efficient code. The following procedure provides a very simple way of retrieving data in a cell. The value and formula of the first ten cells in the first column of the active worksheet are printed to the Immediate window in the Visual Basic Editor in Excel.
Sub ReadingValueInCells()
Dim i As Integer
For i = 1 To 10
Debug.Print ActiveSheet.Cells(i, 1).Value
Debug.Print ActiveSheet.Cells(i, 1).Formula
Next i
End Sub
|
By default, a range's Address property is provided in the A1-style reference. This means that the address of a cell is returned as a string such as "A1" or "B4" as opposed to "R1C1" or "R4C2" (or row 4, column 2). In Excel the A1-style reference is displayed in the Name box to the left of the Formula bar. Reference styles can be one of the following XlReferenceStyle constants: xlA1 or xlR1C1. If you returned the address of a single cell, for example, A1, the value returned from the Address property on the Range object is "$A$1." In a custom dialog box, you may want to display the address without the dollar signs so that the user sees a simpler address value.
Sub GetAddressOfSelection()
Dim rng As Range
If TypeName(ActiveWindow.Selection) = "Range" Then
Set rng = ActiveWindow.Selection
Debug.Print rng.Address
Debug.Print StrippedAddress(rng.Address)
End If
End Sub
Function StrippedAddress(str As String) As String
Dim i As Integer, temp As String
For i = 1 To Len(str)
If Mid$(str, i, 1) <> "$" Then
temp = temp & Mid$(str, i, 1)
End If
Next i
StrippedAddress = temp
End Function
|
This procedure returns the address of the currently selected cells. If the selection isn't a range (that is, a shape is selected), the procedure is exited. Otherwise, the procedure passes the address of the range to the StrippedAddress function to remove dollar signs. If you select one or more cells in the active worksheet and run the GetAddressOfSelection procedure in a module in the Visual Basic Editor in Excel, the result is printed to the Immediate window.
In order to insert cell content, you may have to insert new cells first. The following procedure uses the Insert method on the Range object to insert a new row at the top of the active worksheet and a new column at the left. If the active sheet isn't a worksheet (that is, it's a chart sheet), the procedure is exited. Otherwise, the use of the Rows property returns a Range object representing the first row of the active worksheet and the use of the Columns property returns a Range object representing the first column.
Sub InsertRowAndColumnByShiftingCells()
Dim sh As Worksheet
Set sh = ActiveSheet
If TypeName(sh) <> "Worksheet" Then Exit Sub
sh.Rows("1:1").Insert Shift:=xlDown
sh.Columns("A:A").Insert Shift:=xlRight
End Sub
|
To format cells, you use objects, methods, and properties similar to those in Word and PowerPoint. When you access a Range object, you can use objects such as Borders and Font to manipulate the border and font attributes of the cells in the range. You access both attributes through the Range object by using the Borders and Font properties, respectively. In the following procedure, you set the rng variable to the selection in the active window if the selection is one or more cells. Other selections could include charts or shapes. Chapter 9 provides further information on working with selections in Excel.
Sub FormatCell()
Dim rng As Range
If TypeName(ActiveWindow.Selection) <> _
"Range" Then Exit Sub
Set rng = ActiveWindow.Selection
If TypeName(rng) <> "Range" Then Exit Sub
With rng
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 1
End With
With .Font
.Name = "Arial"
.Color = RGB(112, 220, 21)
.Bold = True
.Italic = True
End With
End With
End Sub
|
Once you set the range to the rng variable, the line style of the bottom edge border of each cell is set so there's a continuous, thick black line. If the cell displays any value, the text font will be Arial and boldface and the color will be green. You can change this example so that the rng variable is set to any cell range in a workbook.
The methods and properties you use to insert and manipulate text in a cell in Excel are not as rich as those in Word and PowerPoint. In Word and PowerPoint you can access and manipulate text by any unit of text. For example, you can access text by characters, words, sentences, or paragraphs. In Excel, however, you have two options—you can access all the text in a cell at once, or you can access a set of text characters in a cell.
In Word and PowerPoint, you can use the Text property to return and set text. In Excel, on the other hand, the Text property on the Range object is read-only. For example, you can use the Text property to read the text in a cell, but you have to use the Value property to write text into a cell. Because you can also use the Value property to read text in a cell, you don't need to use the Text property on the Range object. When you access the Range object that represents a cell, you can use the Value property to return all text in the cell.
If you want to apply formatting or change text in any part of a text string in a cell, use the Characters collection object, which is the only unit of text you can retrieve from a cell. Using the Characters property on the Range object, you can access the Characters collection object and format characters in the text string. To return a specific sequence of characters, you specify the Start and Length arguments in the Characters property of the Range object. In the following procedure, set the variable rng to the selection in the active window. If the selection isn't a cell, the procedure exits. If the selection is more than one cell, the procedure applies the value and formatting of text to each cell in the selection.
Sub FormatCellText()
Dim rng As Range
If TypeName(ActiveWindow.Selection) <> _
"Range" Then Exit Sub
Set rng = ActiveWindow.Selection
rng.Value = "This is some text"
With rng.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Shadow = True
.Underline = xlUnderlineStyleSingle
.ColorIndex = 21
End With
With rng.Characters(Start:=9, Length:=4).Font
.Name = "Arial"
.Bold = True
.Size = 11
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub
|
The value of the cells in the rng variable is set to "This is some text." Two arguments are specified in the Characters property of the Range object. The first is the Start argument, which represents the character position in the cell text string at which the part to be returned begins. The second argument, Length, indicates the number of characters to return. Both arguments are optional. If you don't specify either one, rng.Characters returns a Characters collection object that represents all the characters in a cell's text string. Excel's Characters property is similar to the Mid function, which is built in to the Visual Basic for Applications language. See Chapter 4 for information on the Mid function (as used to parse a filename string) or use the online help in the Visual Basic Editor to learn more.
You use the Font property on the Characters collection object to return the Font object in Excel. You can use the Name and Size properties to change the name and size of the font, and you use other properties to apply more settings to characters such as color, bold, and italics.
The breakdown of content in Excel is similar to that of PowerPoint. In a PowerPoint presentation, the content resides on a slide. In an Excel workbook, the content resides in a worksheet. The Worksheets collection allows your code to iterate through and work with specific worksheets in a workbook. You can also use the Worksheets collection object to add a new worksheet and hence, new content.
The following procedure iterates through worksheets in the Worksheets collection object. Each worksheet is assigned to the variable sh and the name and index of each worksheet is printed to the Immediate window in the Visual Basic Editor in Excel.
Sub IterateThroughWorksheets()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
Debug.Print "Sheet name: " & sh.Name
Debug.Print "Sheet Index: " & sh.Index
Next sh
End Sub
|
The Index property returns a number representing the position of the worksheet in the Worksheets collection. The Index number is directly related to the position of the worksheet's tab on screen. The Name property returns the name visible on the tab. You can also use the value returned by the Name property as the Index argument in the Worksheets(index) property on the Workbook object so your code can return a specific Worksheet object.
You use the Add method of the Worksheets collection to insert a new worksheet in the workbook. The Add method takes four arguments: Before, After, Count, and Type. If you don't specify the Before or After argument, a new worksheet is inserted before the active worksheet in the active workbook. This is the same behavior as inserting a new worksheet by clicking the Worksheet command on the Insert menu.
Sub InsertWorkSheet()
Dim sh As Worksheet, shNew As Worksheet
Dim wb As Workbook
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = "Sheet3" Then
Set wb = sh.Parent
Set shNew = wb.Worksheets.Add(Before:=sh)
shNew.Name = "New Sheet"
Exit For
End If
Next sh
End Sub
|
In this procedure, you use the For Each…Next loop to iterate through the Worksheets collection. You use the If…Then block to determine the name of the worksheet implicitly assigned to the variable sh by the For Each…Next loop. If the worksheet name is "Sheet3," the program adds a new worksheet before Sheet3, sets its name to "New Sheet," and exits the loop.
TIP
In the preceding procedure, sh.Parent returns the Workbook object. The parent of a worksheet isn't the Worksheets collection, but the workbook. In all cases in Word, Excel, and PowerPoint, the parent of an item in a collection isn't the collection, but the parent of the collection. In many cases the Parent property of an object returns an object defined as the generic Object type. If you replace the If…Then block in the preceding procedure with the following, when you type the dot ('.') after sh.Parent, the Auto List Members drop-down list doesn't appear.
If sh.Name = "Sheet3" Then
Set shNew = sh.Parent.Worksheets.Add(Before:=sh)
shNew.Name = "New Sheet"
Exit For
End If
|
NOTE
To take advantage of the Auto List Members drop-down list again, the preceding procedure declared a new variable wb as type Workbook and assigned wb to sh.Parent. When you use the variable wb in your code, the Auto List Members drop-down list will appear. See the sidebar "When Auto List Members Doesn't Appear" under the section "Auto List Members" in Chapter 1.
To insert a hyperlink into an Excel workbook, you select a range of cells and then click Hyperlink on the Insert menu. In Visual Basic for Applications, you can use Excel's Find method, which quickly iterates through all the content in a workbook, searches for specified text, and adds a hyperlink. When the method finds text matching the criteria, it adds a hyperlink to a cell's text and sets the hyperlink to the URL specified in the procedure TestFindAndReplace. Adding hyperlinks to documents will become even more common now that Office 2000 adds the capability to easily save Word, Excel, and PowerPoint documents in HTML format for display in your Web browser. If you type the word "Microsoft" in several workbook cells and then run the TestFindAndReplace procedure, all cells containing the text "Microsoft" would have a hyperlink to http://www.microsoft.com inserted.
In the following procedure, the For Each…Next loop iterates through the Worksheets collection of the active workbook and implicitly assigns the object variable sh to the next Worksheet object in the collection. Within the loop, the Find method searches through all the cells in the worksheet for the value specified in the What argument of the Find method. The Set statement assigns the object variable rng to the cell range returned by the Find method.
Sub TestFindAndReplace()
FindAndReplaceHyperlink sFindText:="Microsoft", _
sHyperlink:="http://www.microsoft.com"
End Sub
Sub FindAndReplaceHyperlink(sFindText As String, _
sHyperlink As String)
Dim rng As Range, sh As Worksheet
Dim sFirstAddress As String
For Each sh In ActiveWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=sFindText, _
LookIn:=xlValues, MatchCase:=True)
If Not rng Is Nothing Then
sFirstAddress = rng.Address
Do
If Trim$(rng.Text) = sFindText Then
rng.Hyperlinks.Add _
Anchor:=rng, Address:=sHyperlink
End If
Set rng = sh.Cells.FindNext(rng)
Loop While rng.Address <> sFirstAddress
End If
Next sh
End Sub
|
You use the variable rng to store the cell range in the worksheet that has text matching the value of sFindText. If the trimmed value of the cell found by the Find or FindNext method is equal to the sFindText value, the program adds a hyperlink to the cell. The Add method of the Hyperlinks object accepts three arguments. The first argument, Anchor, represents the cell range where the hyperlink is to be anchored. The second argument, Address, represents the hyperlink address, which can be a filename or URL, and is the same value that's entered in the "Type the file or Web page name" text box in the Insert Hyperlink dialog box. The optional third argument, SubAddress, isn't specified here.
Because most of Excel's content is inherently organized in cells, tables are second nature. Hence, unlike Word, the capability of explicitly inserting and returning access to a Table object doesn't exist in Excel. Excel creates tables by organizing cell data to appear as a table.
Organizing and setting data in cells commonly occurs relative to a specific cell. For example, as the following example shows, if you select cell B5, then ActiveWindow.Selection.Range("B1") returns cell C5 because B1 is relative to the Range object, B5, that is returned by the Selection property. On the other hand, the code ActiveSheet.Range("B5") always returns cell B5. To see this in operation, select cell B5 in the active worksheet, switch to the Visual Basic Editor in Excel, and add the following line in the Immediate window:
?ActiveWindow.Selection.Range("B1").Address
|
When you press ENTER after the line, the program prints the value "$C$5". In the following example, the data for the table, including row and column headings, are entered relative to the starting cell B5. Later, you use the text in the cells that represent the row and column headings, as well as the cells containing data, to create a chart in Excel.
The following two procedures create a simple five-row by three-column table. You create the table starting at cell B5 in the active worksheet, setting the variable rngStart to cell B5. You enter all values for the table, including the labels for the x axis and legend, in the worksheet relative to cell B5. Within the With…End block, you set the heading for each row and column to the appropriate cell value. You call the procedure InsertTableData, which fills the second and third columns with random values. The cell B5, represented by the variable rngStart, is passed as the one and only argument to the InsertTableData procedure.
Sub InsertTable()
Dim sh As Worksheet, rngStart As Range
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
Set sh = ActiveSheet
Set rngStart = sh.Range("B5") ' starting cell
With rngStart
.Cells(1, 1) = "Region"
.Cells(1, 2) = "Projected"
.Cells(1, 3) = "Actual"
.Cells(2, 1) = "Northwest"
.Cells(3, 1) = "Northeast"
.Cells(4, 1) = "Southwest"
.Cells(5, 1) = "Southeast"
End With
InsertTableData rngStart
rngStart.Range("A1:C5").Columns.AutoFit
' InsertChartObject sh:=sh, rngSource:=rngStart.Range("A1:C5")
End Sub
|
Note that the last line in the procedure contains a comment at the beginning of the line. You'll use this line in the next example when the example is extended to insert a chart with the data in the table.
You write the code to insert data into the table so that it exists in a separate procedure. That's because the source of the data can vary. By keeping the code separate, you keep table insertion and formatting separate from the data that's inserted.
Sub InsertTableData(rngStart As Range)
Dim x As Integer, y As Integer
Dim iNumOfRows As Integer, iNumOfCols As Integer
iNumOfRows = 5: iNumOfCols = 3
With rngStart
For x = 2 To iNumOfRows
For y = 2 To iNumOfCols
.Cells(x, y).Value = Format$(Rnd(x + y), "###0.00")
Next y
Next x
End With
End Sub
|
Just before the end of the InsertTable procedure, you set the width of columns B through D on the active worksheet to AutoFit, which lets you achieve the best fit based only on the contents of cells. Note the code used just above the last line of the InsertTable procedure. You use the Range property on the range represented by the variable rngStart. The argument in the Range property specifies the cells A1 through C5, relative to B5. That is, starting from cell B5, take three columns to the left and five rows down and AutoFit the columns based on the data in this range of cells
You commonly use charts to help analyze data. In Excel a chart can exist in two locations. The first is as a chart sheet and the second is as an embedded chart object on a worksheet. A chart sheet displays a tab on screen. In Chapter 8, you'll see how to insert a new chart sheet into an Excel workbook. You create the chart sheet from data in a worksheet and then copy and paste the chart to a PowerPoint presentation. In the following example, you extend the InsertTable example to create an embedded chart on the same sheet where the table data is inserted.
The example uses the ChartObjects collection object to create a new chart placed in the worksheet passed into the InsertChartObject procedure. You can also use the Add method on the Charts collection object to insert a new chart. The difference, however, is that when you use the Add method on the Charts collection object, you create and locate a chart on a new chart sheet rather than placing the chart in an existing worksheet. You access charts located as a sheet in the workbook by using the Charts property on the Workbook object. On the other hand, you access charts embedded on a worksheet by using the ChartObjects property on the Worksheet object.
In the InsertTable procedure in the previous example, remove the comment from the beginning of the following line. This line is the last line in the InsertTable procedure and it calls the InsertChartObject procedure, passing two arguments. The first is the worksheet where the table data is inserted, and the second is the range representing the table data.
InsertChartObject sh:=sh, rngSource:=rngStart.Range("A1:C5")
|
The chrtObject variable is declared as a ChartObject in the InsertChartObject procedure and is set to the embedded chart object created by the Add method of the ChartsObjects collection object. The first With…End block determines the left and top position of the chart object on the worksheet specified by the sh argument. You determine the left value by adding 10 points (units) to the right edge of the cell range. In the second With…End block, you set the chart type along with the chart's source data and other properties, such as the chart and axes title. You set the data source for the chart to the range rngStart.Range("A1:C5"), which is passed into the InsertChartObject procedure. You set the name of the embedded chart object to "Q2 Sales."
Sub InsertChartObject(sh As Worksheet, rngSource As Range)
Dim chrtObject As ChartObject
Dim lLeft As Long, lTop As Long
With rngSource
lLeft = .Columns.Left + .Columns.Width + 10
lTop = .Rows.Top
End With
Set chrtObject = sh.ChartObjects.Add(Left:=lLeft, _
Top:=lTop, Width:=328.5, Height:=192.75)
chrtObject.Name = "Q2 Sales"
With chrtObject.Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=rngSource, PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Q2 Sales"
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Region"
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Increase"
End With
End With
End Sub
|
In the previous example, you used the ChartObjects collection object to create a new chart. The new chart is created as an embedded chart on the worksheet passed into the InsertChartObject procedure. You can switch the location of an embedded chart to a new sheet (and vice versa) by using the Location method on the Chart object. For example, if an embedded chart exists on a worksheet, such as this example created, you can specify the Where argument in the Location method as xlLocationAsNewSheet to move the chart to a new sheet.
Sub ChangeLocationOfEmbeddedChart()
Dim chrtSheet As Chart
Set chrtSheet = ActiveSheet.ChartObjects("Q2 Sales") _
.Chart.Location(Where:=xlLocationAsNewSheet)
chrtSheet.Name = "Q2 Sales"
End Sub
|
This procedure changes the location of the embedded chart object, named "Q2 Sales" in the InsertChartObject procedure, to a new sheet. The procedure sets the name of the sheet to "Q2 Sales" as well.
