Chart and VBA

Excel’s Charts

Excel’s charting feature lets you create a wide variety of charts using data that’s stored in a worksheet. You have a great deal of control over nearly every aspect of each chart. An Excel chart is simply packed with objects, each of which has its own properties and methods. Because of this, manipulating charts with Visual Basic for Applications (VBA) can be a bit of a challenge. We discuss the key concepts that you need to understand in order to write VBA code that generates or manipulates charts.

The Chart object model

When you first start exploring the object model for a Chart object, you’ll probably be very confused — which isn’t surprising; the object model is very confusing. It’s also very deep. For example, assume that you want to change the title displayed in an embedded chart. The toplevel object, of course, is the Application object (Excel). The Application object contains a Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which contains a Chart object. The Chart object has a ChartTitle object, and the ChartTitle object has a Text property that stores the text that’s displayed as the chart’s title. Here’s another way to look at this hierarchy for an embedded chart:

Application

Workbook

Worksheet

ChartObject

Chart

ChartTitle

Your VBA code must, of course, follow this object model precisely. For example, to set a chart’s title to Annual Sales, you can write a VBA instruction like this:

WorkSheets(“Sheet1”).ChartObjects(1).Chart.ChartTitle. Text = “YTD Sales”

Note that the preceding statement will fail if the chart doesn’t have a title. To add a default title to the chart (which displays the text Chart Title), use this statement:

Worksheets(“Sheet1”).ChartObjects(1).Chart.HasTitle = True

Creating an Embedded Chart

A ChartObject is a special type of Shape object. Therefore, it’s a member of the Shapes collection. To create a new chart, use the AddChart method of the Shapes collection. The following statement creates an empty embedded chart:

ActiveSheet.Shapes.AddChart

The AddChart method can use five arguments (all are optional):

Type: The type of chart. If omitted, the default chart type is used. Constants for all the chart types are provided (for example, xlArea, xlColumnClustered, and so on).

 Left: The left position of the chart, in points. If omitted, Excel centers the chart horizontally.

Top: The top position of the chart, in points. If omitted, Excel centers the chart vertically.

Width: The width of the chart, in points. If omitted, Excel uses 354.

Height: The height of the chart, in points. If omitted, Excel uses 210.

A chart without data isn’t very useful, so you’ll want to use the SetSourceData method to add data to a newly created chart. The procedure that follows demonstrates the SetSourceData method. This procedure creates the chart shown in below Figure.

Data for Chart

Macro Code for Chart

Sub CreateChart()

Dim MyChart As Chart

Dim DataRange As Range

Set DataRange = ActiveSheet.Range(“A1:C13”)

Set MyChart = ActiveSheet.Shapes.AddChart.Chart

MyChart.SetSourceData Source:=DataRange

End Sub

OutPut

Creating a Chart on a Chart Sheet

To create a chart on a chart sheet, use the Add method of the Charts collection. The Add method of the Charts collection uses several optional arguments, but these arguments specify the position of the chart sheet — not chart-related information.

The example that follows creates a chart on a chart sheet and specifies the data range and chart type:

Sub CreateChartSheet()

Dim MyChart As Chart

Dim DataRange As Range

Set DataRange = ActiveSheet.Range(“A1:C13”)

Set MyChart = Charts.Add

MyChart.SetSourceData Source:=DataRange

ActiveChart.ChartType = xlColumnClustered

End Sub

Exporting a Chart or Graphics

In some cases, you may need an Excel chart in the form of a graphics file. For example, you may want to post the chart on a Web site. One option is to use a screen-capture program and copy the pixels directly from the screen. Another choice is to write a simple VBA macro as given below.

Sub SaveChartAsGIF ()

Dim Fname as String

If ActiveChart Is Nothing Then Exit Sub

Fname = ThisWorkbook.Path & “\” & ActiveChart.Name & “.gif”

ActiveChart.Export FileName:=Fname, FilterName:=”GIF”

End Sub

Other choices for the FilterName argument are “JPEG” and “PNG”.

Another way to export all graphic images from a workbook is to save the file in HTML format. Doing so creates a directory that contains GIF and PNG images of the charts, shapes, clipart, and even copied range images (created with HomeàClipboardàPasteàPicture (U)).

Here’s a VBA procedure that automates the process. It works with the active workbook:

Sub SaveAllGraphics()

Dim FileName As String

Dim TempName As String

Dim DirName As String

Dim gFile As String

FileName = ActiveWorkbook.FullName

TempName = ActiveWorkbook.Path & "\" & _

ActiveWorkbook.Name & "graphics.htm"

DirName = Left(TempName, Len(TempName) - 4) & "_files"

' Save active workbookbook as HTML, then reopen original

ActiveWorkbook.Save

ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml

Application.DisplayAlerts = False

ActiveWorkbook.Close

Workbooks.Open FileName

' Delete the HTML file

Kill TempName

' Delete all but *.PNG files in the HTML folder

gFile = Dir(DirName & "\*.*")

Do While gFile <> ""

If Right(gFile, 3) <> "png" Then Kill DirName & "\" & gFile

gFile = Dir

Loop

' Show the exported graphics

Shell "explorer.exe" & DirName, vbNormalFocus

End Sub

Changing chart data based on the active cell

Figure below shows a chart that’s based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically.

This example uses an event handler for the Sheet1 object. The SelectionChange event occurs whenever the user changes the selection by moving the cell pointer. The event-handler procedure for this event (which is located in the code module for the Sheet1 object) is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target  As Excel.Range)

If CheckBox1 Then Call UpdateChart

End Sub

In other words, every time the user moves the cell cursor, the Worksheet_SelectionChange procedure is executed. If the Auto Update Chart check box (an ActiveX control on the sheet) is checked, this procedure calls the UpdateChart procedure, which follows:

Sub UpdateChart()

Dim ChtObj As ChartObject

Dim UserRow As Long

Set ChtObj = ActiveSheet.ChartObjects(1)

UserRow = ActiveCell.Row

If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then

ChtObj.Visible = False

Else

ChtObj.Chart.SeriesCollection(1).Values = _

Range(Cells(UserRow, 2), Cells(UserRow, 5))

ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text

ChtObj.Visible = True

End If

End Sub

The UserRow variable contains the row number of the active cell. The If statement checks that the active cell is in a row that contains data. (The data starts in row 4.) If the cell cursor is in a row that doesn’t have data, the ChartObject object is hidden. Otherwise, the code sets the Values property for the Series object to the range in columns 2–5 of the active row. It also sets the ChartTitle object to correspond to the text in column A.

Animating Charts: How to Animate Chart we will show you in next post