In the previous chapter, you learned to work with some of the main objects in the Excel object model, such as Workbook
objects, the ActiveCell
object, Range
objects, and the Options
object. This chapter shows you how to expand your programming facility with VBA in Excel by working with charts, windows, and Find and Replace.
The following sections show you how to use VBA to create and format charts, either as an entire chart, sheets in a workbook, or as objects on an existing worksheet.
VBA uses the Chart
object to represent a chart on a chart sheet and a ChartObject
object to represent an embedded chart on a worksheet. The ChartObject
object contains a Chart
object, which you can manipulate by accessing it through the ChartObject
object. Confused? Object classification schemes can certainly be a bit bewildering.
When writing a macro, you create a chart or chart object in a different order than when working interactively and doing things by hand within Excel. Here are the steps you take when creating charts programmatically (via macro code rather than interactively via a mouse and keyboard):
Chart
object variable.Chart
object using the Set
command.SetSourceData
method.ChartType
property.To create a chart on a new chart sheet, use the Add
method with the Charts
collection. The syntax is as follows:
expression.Add(Before, After, Count, Type)
Here are the components of this syntax:
expression
is a required expression that returns a Charts
collection.Before
is an optional Variant argument that you can use to specify the sheet before which to add the new chart sheet.After
is an optional Variant argument that you can use to specify the sheet after which to add the new sheet.
Typically, you'll use either Before
or After
. If you omit both arguments, VBA adds the new chart sheet before the active sheet.
Count
is an optional Variant argument that you can use to specify how many chart sheets to add. The default is one.Type
is an optional Variant argument that you can use to specify which kind of chart you want displayed. The choices are xlWorksheet
, xlChart
, xlExcel4MacroSheet
, and xlExcel4IntlMacroSheet
. The default value is xlWorksheet
, so you have to specify xlChart
in the following code example because it adds a chart, not an ordinary worksheet.The following code declares an object variable named myChartSheet
as being of the Chart
type (a chart worksheet) and then assigns to myChartSheet
a new chart sheet added after the last existing sheet in the active workbook:
Dim myChartSheet As Chart
Set myChartSheet = ActiveWorkbook.Sheets.Add _
(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count), _
Type:=xlChart)
To create a chart on an existing worksheet, use the Add
method with the ChartObjects
collection. The syntax is as follows:
expression.Add(Left, Top, Width, Height)
Here are the components of this syntax:
expression
is a required expression that returns a ChartObjects
collection.Left
is a required Double (variable type) argument that specifies the position of the upper-left corner of the chart in points from the left edge of cell A1.Top
is a required Double argument that specifies the position of the upper-left corner of the chart in points from the top edge of cell A1.Width
is a required Double argument that specifies the width of the chart in points.Height
is a required Double argument that specifies the height of the chart in points.For example, the following statements declare a new ChartObject
object named myChartObject
and assign to it a new chart object (chart area) 400 points wide by 300 points deep, positioned 200 points from the left edge and 200 points from the top of the worksheet:
Dim myChartObject As ChartObject
Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, _
Width:=400, Height:=300)
To work with the chart inside the ChartObject
, return the Chart
property of the ChartObject
object.
So far, the chart (on the chart sheet or in the Chart
object) is blank. To give it contents, specify the chart's source data by using the SetSourceData
method of the Chart
object. For example, the following statement specifies the range A1:E5
on the worksheet named Chart Data
in the active workbook as the source data of the Chart
object in the ChartObject
object named myChartObject
:
myChartObject.Chart.SetSourceData Source:= _
ActiveWorkbook.Sheets("Chart Data").Range("A1:E5")
To specify a chart type, set the ChartType
property of the Chart
object. Excel offers too great a variety of charts to list here (73 different types), but you can easily identify the chart types from their enumeration-constant names. For example, the constant xl3DArea
represents the 3D Area chart type, xlColumnStacked
represents the Stacked Column chart type, and xlDoughnutExploded
represents the Exploded Doughnut chart type.
The following statement sets the type of the chart represented by the object variable myChart
to the Stacked Column type:
myChart.ChartType = xlColumnStacked
To work with series in a chart, you use the SeriesCollection
collection, which contains all the series in the specified chart.
To create a new series, use the NewSeries
method with the SeriesCollection
collection. For example, the following statement adds a new series to the chart represented by the object variable myChart
:
myChart.SeriesCollection.NewSeries
To add a new series to a SeriesCollection
collection, use the Add
method with the appropriate SeriesCollection
object. The syntax is as follows:
expression.Add(Source, Rowcol, SeriesLabels, CategoryLabels, Replace)
Here are the components of this syntax:
expression
is a required expression that returns a SeriesCollection
collection.Source
is a required Variant argument that specifies the source of the data for the new series. You can supply the data either as a range or as an array of data points.Rowcol
is an optional argument that you can set to xlRows
to specify that the new values are in rows in the specified range, or you can use the default setting, xlColumns
, to specify that the new values are in columns. If you omit this argument, Excel uses xlColumns
.SeriesLabels
is an optional Variant argument that you can set to True
to specify that the first row or column in the source area contains the series labels, or set to False
to specify that the first row or column in the source area contains the first data point for the series.
If you omit this argument, Excel tries to work out whether the first row or column contains a series label. It's best to specify this argument to avoid confusion. However, if Source
is an array, VBA ignores this argument.
CategoryLabels
is an optional Variant argument that you can set to True
to specify that the first row or column contains the name for the category labels, or set to False
to specify that it does not contain them.
If you omit this argument, Excel tries to work out whether the first row or column contains a category label. It's best to specify this argument to avoid confusion. Again, if Source
is an array, VBA ignores this argument.
Replace
is an optional Variant argument that you can set to True
when CategoryLabels
is True
to make the categories replace the existing categories for the series, or set to False
(the default value) to prevent the existing categories from being replaced.The following procedure brings together several elements used in the previous code examples in this chapter. It illustrates how to create a complete chart and add a new series to the chart identified by the object variable myChart
. The procedure draws the data from the range A4:K4
on the active worksheet in the active workbook, using rows:
Sub test()
Dim myChartObject As ChartObject
Dim MyChart As Chart
Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=100, Top:=100, _
Width:=400, Height:=300)
Set MyChart = myChartObject.Chart
MyChart.ChartType = xlConeBarStacked
MyChart.SeriesCollection.Add _
Source:=ActiveSheet.Range("A4:K4"), Rowcol:=xlRows
End Sub
If you execute this example, you'll see results similar to those shown in Figure 23.1. A chart will be generated based on whatever data lies within the specified range.
To extend an existing series, use the Extend
method with the appropriate SeriesCollection
object. The syntax is as follows:
expression.Extend(Source, Rowcol, CategoryLabels)
Here are the components of this syntax:
expression
is a required expression that returns a SeriesCollection
object.Source
is a required Variant argument that specifies the source of the data for the new series. You can supply the data either as a range or as an array of data points.Rowcol
is an optional argument that you can set to xlRows
to specify that the new values are in rows in the specified range, or you can use the default setting, xlColumns
, to specify that the new values are in columns. If you omit this argument, Excel uses xlColumns
.CategoryLabels
is an optional Variant argument that you can set to True
to specify that the first row or column contains the name for the category labels, or you can set to False
to specify that it does not contain them.
If you omit this argument, Excel tries to work out whether the first row or column contains a category label. It's best to specify this argument to avoid confusion. If Source
is an array, VBA ignores this argument.
For example, the following statement extends the series in the chart identified by the object variable myChart
using the data in the cells P3:P8
on the worksheet named Chart Data
:
myChart.SeriesCollection.Extend _
Source:=Worksheets("Chart Data").Range("P3:P8")
To add a legend to the chart, set its HasLegend
property to True
. To manipulate the legend, work with the properties of the Legend
object. Key properties include these:
Position
property controls where the legend appears:
xlLegendPositionBottom
xlLegendPositionCorner
xlLegendPositionLeft
xlLegendPositionRight
xlLegendPositionTop
.Height
property and the Width
property control the height and width of the legend, respectively, in points.Font
property returns the Font
object, which has properties you can set to specify the font size, name, and effects.For example, the following statements add the legend to the chart represented by the object variable myChart
and apply 16-point Arial font to it:
With myChart.Legend
.HasLegend = True
.Font.Size = 16
.Font.Name = "Arial"
End With
To add a title to the chart, set its HasTitle
property to True
, as in this example:
myChart.HasTitle = True
Excel adds the title with the default text Chart Title
. To change the text, set the Text
property of the ChartTitle
object, which represents the chart title. Here's an example:
myChart.ChartTitle.Text = "Industrial Mixups in North Dakota"
To position the title, set its Top
property (specifying the number of points from the top edge of the worksheet) and its Left
property (specifying the number of points from the left edge of the worksheet), as in this example:
With myChart.ChartTitle
.Top = 100
.Left = 150
End With
To format the text of the title, work with its Font
object, as follows:
myChart.ChartTitle.Font.Name = "Arial"
To work with an axis of a chart, use the Axes
method to access the appropriate axis. The syntax is as follows:
expression.Axes(Type, Group)
Here, expression
is a required expression that returns a Chart
object. Type
is an optional Variant argument that specifies the axis to return. Use
xlValue
to return the value axisxlCategory
to return the category axisxlSeriesAxis
to return the series axis (on 3D charts only)Group
is an optional argument that you can set to xlSecondary
to specify the second axis group instead of xlPrimary
(the default setting), which specifies the first axis group.
For example, the following statements work with the category axis in the primary group of the chart, applying its title, adding text, setting the font and font size, and turning major gridlines on and minor gridlines off. Note that this With
structure should be placed within a second, outer With
structure representing the chart itself:
With MyChart
With .Axes(Type:=xlCategory, AxisGroup:=xlPrimary)
.HasTitle = True
.AxisTitle.Text = "Years"
.AxisTitle.Font.Name = "Times New Roman"
.AxisTitle.Font.Size = 12
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
End With
You can manipulate headers and footers easily via VBA by using a built-in set of format and content constants. These include format specifications such as &U
for underlining and &C
for centering. Content constants include &D
, which inserts the current date, &P
for the page number, and &F
for the document's name. The complete list of VBA header and footer constants can be found here:
http://msdn.microsoft.com/en-us/library/office/ff822794.aspx
This next code turns on italics and underlining, and on the right side of the header prints Dr. Dancy Page followed by the current page and the total number of pages: Dr. Dancy Page 2 of 7. If there is no header, one is created.
ActiveSheet.PageSetup.RightHeader = "&U&I Doctor Dancy Page &P of &N"
The Windows
collection contains a Window
object for every open window in the Excel application. Normally, when you open a workbook, Excel opens a window so that you can see it. You can also open further windows as necessary—for example, by clicking the Ribbon's View tab and then clicking the New Window button in the Window area.
In most cases, using Window
objects isn't a very useful way to access data via VBA because you can access it more easily using objects such as the ActiveSheet
object or the ActiveCell
object. However, if you want to display data to the user in a special way, you may want to open, close, activate, or arrange windows programmatically (via a macro rather than having the user do it by hand interactively).
To open a new window on a workbook, use the NewWindow
method of the appropriate Window
object. This method takes no arguments. For example, the following statement opens a new window showing the contents of the first window open on the workbook identified by the object variable myWorkbook
:
myWorkbook.Windows(1).NewWindow
To close a window, use the Close
method with the appropriate Window
object. The syntax is as follows:
expression.Close(SaveChanges, Filename, RouteWorkbook)
Here, expression
is a required expression that returns a Window
object. This syntax is the same as for closing a workbook (see “Closing a Workbook” in the previous chapter). The difference is that if two or more windows are open on the same workbook, closing the second or subsequent window does not close the workbook, so the arguments are not relevant. (If the window you're closing is the workbook's last window, however, you do need to specify the windows—otherwise, Excel prompts the user to save any unsaved changes.) For example, the following statement closes all windows open on the workbook referenced by the object variable myWorkbook
except for one window:
Do While myWorkbook.Windows.Count > 1
myWorkbook.Windows(myWorkbook.Windows.Count).Close
Loop
Activation means to give a particular window the focus. The one window on the monitor that responds to user typing is said to have the focus. To activate a window, use the Activate
method of the appropriate Window
object. For example, the following statement activates the first window open on the workbook Planning.xlsx
:
Workbooks("Planning.xlsx").Windows(1).Activate
Similarly, you can activate the previous window (in the currently available set of windows) by using the ActivatePrevious
method or the next window by using the ActivateNext
method.
To arrange windows, use the Arrange
method with the appropriate Windows
collection. The syntax is as follows:
expression.Arrange(ArrangeStyle, ActiveWorkbook, SyncHorizontal, SyncVertical)
Here are the components of this syntax:
expression
is a required expression that returns a Windows
collection.ArrangeStyle
is an optional argument that you can set to
xlArrangeStyleTiled
to tile the windows (the default setting)xlArrangeStyleHorizontal
to arrange the windows horizontallyxlArrangeStyleVertical
to arrange the windows verticallyxlArrangeStyleCascade
to cascade the windows in an overlapping arrangement that lets you see the title bar of each window but the contents of only the front windowActiveWorkbook
is an optional Variant argument that you can set to True
to make VBA arrange only the windows of the active workbook. The default value is False
, which arranges all open windows.SyncHorizontal
and SyncVertical
are optional Variant arguments that you can set to True
when you use ActiveWorkbook:=True
to make the windows of the active workbook scroll horizontally or vertically in sync (when you scroll one window, the other windows scroll by the same amount in the same direction). The default is False
.For example, the following statement arranges the windows in the workbook Budget.xlsx
vertically and sets synchronized scrolling on them:
Workbooks("Budget.xlsx").Windows.Arrange _
ArrangeStyle:=xlArrangeStyleVertical, _
ActiveWorkbook:=True, SyncVertical:=True
You can maximize, minimize, or restore the application window by setting the WindowState
property of the Application
object to xlMaximized
, xlMinimized
, or xlNormal
. Similarly, within the application window, you can maximize, minimize, or restore a document by setting its WindowState
property.
When a window is in a “normal” state (xlNormal
; not maximized or minimized), you can position it by using the Top
and Left
properties to specify the position of the upper-left corner of the window and size it by setting its Height
and Width
properties. Check the UsableWidth
property and the UsableHeight
property of the Application
object to find the amount of space available in the Application
window. (Similarly, you can check the UsableWidth
property and the UsableHeight
of the Window
object to see how much space is available in the window—for example, so that you can size or position an object correctly.)
The following example declares two Window
object variables, myWindow1
and myWindow2
, and assigns myWindow1
to the active window and myWindow2
to a new window showing the same worksheet as myWindow1
. The example then sizes and positions the two windows so that each is the full height available in the application window, with myWindow1
taking one-quarter of the available width and myWindow2
taking the remaining three-quarters of the available width:
Dim myWindow1 As Window, myWindow2 As Window
Set myWindow1 = ActiveWindow
Set myWindow2 = myWindow1.NewWindow
With myWindow1
.WindowState = xlNormal
.Top = 0
.Left = 0
.Height = Application.UsableHeight
.Width = Application.UsableWidth * 0.25
End With
With myWindow2
.WindowState = xlNormal
.Top = 0
.Left = (Application.UsableWidth * 0.25) + 1
.Height = Application.UsableHeight
.Width = Application.UsableWidth * 0.75
End With
To change the zoom, set the Zoom
property of the appropriate Window
object. Zoom in this context means merely to change the size of the window onscreen—it doesn't necessarily imply enlarging it, merely changing it.
For example, the following statement zooms the active window to 150 percent:
ActiveWindow.Zoom = 150
In some procedures, you may need to change the display of the Excel window to ensure that certain features are (or are not) available to the user. Use the Boolean properties DisplayScrollBars
, DisplayStatusBar
, and DisplayFormulaBar
to control whether Excel displays the scroll bars, status bar, and formula bar. Use the DisplayFullScreen
property to toggle full-screen view on and off.
For example, the following statements make sure that the scroll bars and status bar are hidden and that the formula bar is displayed:
With Application
.DisplayScrollBars = False
.DisplayStatusBar = False
.DisplayFormulaBar = True
End With
Excel's Find and Replace features can be useful for locating data in your procedures. In Excel, Find and Replace are implemented through methods rather than (as in Word) through a Find
object.
Both the Range
object and the WorksheetFunction
object have Find
methods and Replace
methods (but with different syntax). For most find and replace operations, you'll want to use the Range
object—for example, to replace the contents of specific cells on a worksheet.
The syntax for the Range
object's Find
method is as follows:
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
Here are the components of this syntax:
expression
is a required expression that returns a Range
object.What
is a required Variant argument that specifies the data to find. This data can be a string of text or any Excel data type.After
is an optional Variant argument that you can use to specify the cell after which to begin searching.
After
must be a cell in the range that's being searched. If you omit After
, Excel begins the search at the upper-left cell in the range.
LookIn
is an optional Variant argument that you can use to specify whether to search in formulas (xlFormulas
), values (xlValues
), or comments (xlComments
).LookAt
is an optional Variant argument that you can set to xlWhole
to search for the entire contents of a cell, or to xlPart
to search for the match within the contents of cells.SearchOrder
is an optional Variant argument that you can set to xlByRows
to search by rows, or to xlByColumns
to search by columns.SearchDirection
is an optional Variant argument that you can set to xlNext
to search downward, or to xlPrevious
to search upward.MatchCase
is an optional Variant argument that you can set to True
to use case-sensitive searching. The default setting is False
.MatchByte
is an optional Variant argument used only if you've installed double-byte language support.SearchFormat
is an optional Variant argument that controls whether Excel searches for specified formatting (True
) or not (False
).The following example code searches for 2020
in formulas in cells after the active cell, without searching for formatting:
Cells.Find(What:="2020", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Notice that in this code each argument is named. And one, MatchByte
, is omitted. Recall that if you leave out an argument in an argument list, you must either insert a comma as a placeholder or use named arguments. Given that Excel's Find
arguments are persistent, it is a good idea to use named arguments here to remind yourself that they need to be restored to the user's previous settings.
After you have executed a search using the Find
method, you can use the FindNext
method to find the next instance of the search item, or the FindPrevious
method to find the previous instance. The syntax is as follows:
expression.FindNext(After)
expression.FindPrevious(After)
Here, expression
is a required expression that returns a Range
object, and After
is an optional Variant argument that specifies the cell after which you want to search (for the FindNext
method) or before which you want to search (for the FindPrevious
method). After
must be a single cell.
For example, the following statement finds the next instance of the search item:
Cells.FindNext
To replace using VBA, use the Replace
method with the Range
object. The syntax is as follows:
expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)
The components of the syntax are the same as for the Search
method except for the following:
Replacement
is a required Variant argument that specifies the replacement string for the search.ReplaceFormat
is an optional Variant argument that controls whether Excel replaces formatting in the search (True
) or not (False
).For example, the following statement replaces the instances of the word Sales
in column B of the active worksheet with the words Sales & Marketing
, using case-sensitive matching:
ActiveSheet.Columns("B").Replace What:="Sales", _
Replacement:="Sales & Marketing", SearchOrder:=xlByColumns, _
MatchCase:=True
To search for formatting, use the FindFormat
property of the Application
object to define the formatting, and then set the SearchFormat
argument of the Find
method to True
. Similarly, use the ReplaceFormat
property of the Application
object to define the replacement formatting, and then set the ReplaceFormat
property of the Replace
method to True
.
For example, the following statements use a With
structure to set the Application.FindFormat.Font
properties for which to search, a With
structure to set the Application.ReplaceFormat.Font
with which to replace them, and the Replace
method of the Cells
collection to effect the replacement:
With Application.FindFormat.Font
.Name = "Arial"
.Size = "12"
.Bold = True
End With
With Application.ReplaceFormat.Font
.Name = "Arial Black"
.Bold = False
End With
Cells.Replace What:="5", Replacement:="5", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
It's easy to add shapes to a worksheet. Shapes can be used to draw attention to important points or liven up statistical data for a presentation. Here's an example that adds two explosion graphics to a worksheet:
Sub AutoShapes()
ActiveSheet.Shapes.AddShape(msoShapeExplosion2, 425, 145, 86, 101).Select
ActiveSheet.Shapes.AddShape(msoShapeExplosion1, 265, 224, 190, 190).Select
End Sub
The AddShape
method takes the following arguments:
AddShape(Type, Left, Top, Width, Height)
The Type
argument specifies one of a set of msoShape
constants that can be found in Excel's VBA Editor. There are dozens of shapes, including a moon, a heart, and a tear. Press F2 to display the Object Browser. In the list box at the top left of the Object Browser, you'll likely see All Libraries displayed by default. Instead, open this list and select Office. (This list box specifies the library of objects that will be searched.) Now in the search field directly below that, type msoshape and click the binoculars icon next to the field.
NewWindow
method of the appropriate Window
object.
NewWindow
method take any arguments?