UEU-co logo

Common VBA Charting Techniques


Previous Page

Next Page

Common VBA Charting Techniques

In this section, I describe how to use VBA to perform some common tasks that involve charts.

Creating an embedded chart

In Excel 2007, 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 of 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.

In many cases, you may find it efficient to create an object variable when the chart is created. The following procedure creates a line chart that can be referenced in code by using the MyChart object variable:

Sub CreateChart() Dim MyChart As Chart Set MyChart = ActiveSheet.Shapes.AddChart(xlLineMarkers).Chart End Sub

Image from book

Creating a Chart the Old Way

Using the AddChart method of the Shapes collection (as described in “Creating an embedded chart“) is the “new” way of creating charts, introduced in Excel 2007. For compatibility purposes, you can still use the Add method of the ChartObjects collection. This method, unlike the AddChart method of the Shapes objects, does not allow you to specify the chart type as an argument, so you need to use the ChartType property if you want to use anything except the default chart type. In addition, the Left, Top, Width, and Height arguments are required.

The procedure that follows uses the Add method of the ChartObjects collection to create an embedded chart.

Sub CreateChart2() Dim MyChart As Chart Dim DataRange As Range Set DataRange = ActiveSheet.Range(“A1:C7”) Set MyChart = ActiveSheet.ChartObjects.Add(10, 10, 354, 210).Chart MyChart.SetSourceData Source:=DataRange MyChart.ChartType = xlColumnClustered End Sub

Image from book

A chart without data is not 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 Figure 18-2.

Image from book
Figure 18-2: A few lines of VBA code created this chart. Sub CreateChart() Dim MyChart As Chart Dim DataRange As Range Set DataRange = ActiveSheet.Range(“A1:C7”) Set MyChart = ActiveSheet.Shapes.AddChart.Chart MyChart.SetSourceData Source:=DataRange End Sub

Creating a chart on a chart sheet

The preceding section describes the basic procedures for creating an embedded chart. 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:C7”) Set MyChart = Charts.Add MyChart.SetSourceData Source:=DataRange ActiveChart.ChartType = xlColumnClustered End Sub

Using VBA to activate a chart

When a user clicks any area of an embedded chart, the chart is activated. Your VBA code can activate an embedded chart with the Activate method. Here’s a VBA statement that’s the equivalent of Ctrl+clicking an embedded chart:

ActiveSheet.ChartObjects(“Chart 1”).Activate

If the chart is on a chart sheet, use a statement like this:

Sheets(“Chart1”).Activate

Alternatively, you can activate a chart by selecting its containing Shape:

ActiveSheet.Shapes(“Chart 1”).Select

When a chart is activated, you can refer to it in your code by using the ActiveChart property (which returns a Chart object). For example, the following instruction displays the name of the active chart. If there is no active chart, the statement generates an error:

MsgBox ActiveChart.Name

To modify a chart with VBA, it’s not necessary to activate it. The two procedures that follow have exactly the same effect. That is, they change the embedded chart named Chart 1 to an area chart. The first procedure activates the chart before performing the manipulations; the second one doesn’t:

Sub ModifyChart1() ActiveSheet.ChartObjects(“Chart 1”).Activate ActiveChart.ChartType = xlArea End Sub Sub ModifyChart2() ActiveSheet.ChartObjects(“Chart 1”).Chart.ChartType = xlArea End Sub

Moving a chart

A chart embedded on a worksheet can be converted to a chart sheet. To do so manually, just activate the embedded chart and choose Chart Tools Image from book Design Image from book Location Image from book Move Chart. In the Move Chart dialog box, select the New Sheet option and specify a name.

You can also convert an embedded chart to a chart sheet by using VBA. Here’s an example that converts the first ChartObject on a worksheet named Sheet1 to a chart sheet named MyChart:

Sub MoveChart1() Sheets(“Sheet1”).ChartObjects(1).Chart. _ Location xlLocationAsNewSheet, “MyChart” End Sub

The following example does just the opposite of the preceding procedure: It converts the chart on a chart sheet named MyChart to an embedded chart on the worksheet named Sheet1.

Sub MoveChart2() Charts(“MyChart”) _ .Location xlLocationAsObject, “Sheet1” End Sub

Image from book

What’s Your Name?

Every ChartObject object has a name, and every Chart contained in a ChartObject has a name. That certainly seems straightforward enough, but it can be confusing. Create a new chart on Sheet1 and activate it. Then activate the VBA Immediate window and type a few commands:

? ActiveSheet.Shapes(1).Name Chart 1 ? ActiveSheet.ChartObjects(1).Name Chart 1 ? ActiveChart.Name Sheet1 Chart 1 ? Activesheet.ChartObjects(1).Chart.Name Sheet1 Chart 1

If you change the name of the worksheet, the name of the Chart also changes. However, you can’t change the name of a Chart that’s contained in a ChartObject. If you try to do so using VBA, you get an inexplicable “out of memory” error.

What about changing the name of a ChartObject? The logical place to do that is in the Name box (to the left of the Formula bar). Although you can rename a shape by using the Name box, you can’t rename a chart (even though a chart is actually a shape). To rename an embedded chart, use the Chart Name control in the Chart Tools Image from book Layout Image from book Properties group. This control displays the name of the active chart (which is actually the name of the ChartObject), and you can use this control to change the name of the ChartObject. Oddly, Excel allows you to use the name of an existing ChartObject. In other words, you could have a dozen embedded charts on a worksheet, and every one of them can be named Chart 1.

Bottom line? Be aware of this quirk. If you find that your VBA charting macro isn’t working, make sure that you don’t have two identically named charts.

Image from book
Note 

Using the Location method also activates the relocated chart.

Using VBA to deactivate a chart

You can use the Activate method to activate a chart, but how do you deactivate (that is, unselect) a chart? According to the Help System, you can use the Deselect method to deactivate a chart:

ActiveChart.Deselect

However, this statement simply doesn’t work – at least in the initial release of Excel 2007.

As far as I can tell, the only way to deactivate a chart by using VBA is to select something other than the chart. For an embedded chart, you can use the RangeSelection property of the ActiveWindow object to deactivate the chart and select the range that was selected before the chart was activated:

ActiveWindow.RangeSelection.Select

Determining whether a chart is activated

A common type of macro performs some manipulations on the active chart (the chart selected by a user). For example, a macro might change the chart’s type, apply a style, or export the chart to a graphics file.

The question is, how can your VBA code determine whether the user has actually selected a chart? By selecting a chart, I mean either activating a chart sheet or activating an embedded chart by clicking it. Your first inclination might be to check the TypeName property of the Selection, as in this expression:

TypeName(Selection) = “Chart”

In fact, this expression never evaluates to True. When a chart is activated, the actual selection will be an object within the Chart object. For example, the selection might be a Series object, a ChartTitle object, a Legend object, a PlotArea object, and so on.

The solution is to determine whether ActiveChart is Nothing. If so, then a chart is not active. The following code checks to ensure that a chart is active. If not, the user sees a message and the procedure ends:

If ActiveChart Is Nothing Then MsgBox “Select a chart.” Exit Sub Else ‘other code goes here End If

You may find it convenient to use a VBA function procedure to determine whether a chart is activated. The ChartIsSelected function, which follows, returns True if a chart sheet is active or if an embedded chart is activated, but returns False if a chart is not activated:

Private Function ChartIsSelected() As Boolean ChartIsSelected = Not ActiveChart Is Nothing End Function

Deleting from the ChartObjects or Charts collection

To delete a chart on a worksheet, you must know the name or index of the ChartObject. This statement deletes the ChartObject named Chart 1 on the active worksheet:

ActiveSheet.ChartObjects(“Chart 1”).Delete

To delete all ChartObject objects on a worksheet, use the Delete method of the ChartObjects collection:

ActiveSheet.ChartObjects.Delete

You can also delete embedded charts by accessing the Shapes collection. The following statement deletes Chart 1 on the active worksheet:

ActiveSheet.Shapes(“Chart 1”).Delete

And this statement deletes all embedded charts (and all other shapes):

ActiveSheet.Shapes.Delete

To delete a single chart sheet, you must know the chart sheet’s name or index. The following statement deletes the chart sheet named Chart1:

Charts(“Chart1”).Delete

To delete all chart sheets in the active workbook, use the following statement:

ActiveWorkbook.Charts.Delete

Deleting sheets causes Excel to display a warning like the one shown in Figure 18-3. The user must reply to this prompt in order for the macro to continue. If you are deleting a sheet with a macro, you probably won’t want this warning prompt to appear. To eliminate the prompt, use the following series of statements:

Image from book
Figure 18-3: Attempting to delete one or more chart sheets results in this message. Application.DisplayAlerts = False ActiveWorkbook.Charts.Delete Application.DisplayAlerts = True

Looping through all charts

In some cases, you may need to perform an operation on all charts. The following example applies changes to every embedded chart on the active worksheet. The procedure uses a loop to cycle through each object in the ChartObjects collection and then accesses the Chart object in each and changes several properties.

Sub FormatAllCharts() Dim ChtObj As ChartObject For Each ChtObj In ActiveSheet.ChartObjects With ChtObj.Chart .ChartType = xlLineMarkers .ApplyLayout 3 .ChartStyle = 12 .ClearToMatchStyle .SetElement msoElementChartTitleAboveChart .SetElement msoElementLegendNone .SetElement msoElementPrimaryValueAxisTitleNone .SetElement msoElementPrimaryCategoryAxisTitleNone .Axes(xlValue).MinimumScale = 0 .Axes(xlValue).MaximumScale = 1000 End With Next ChtObj End Sub

CD-ROM 

This example is available on the companion CD-ROM. The filename is Image from book format all charts.xlsm.

Figure 18-4 shows four charts that use a variety of different formatting; Figure 18-5 shows the same charts after running the FormatAllCharts macro.

Image from book
Figure 18-4: These charts use different formatting. Image from book
Figure 18-5: A simple macro applied consistent formatting to the four charts.

The following macro performs the same operation as the preceding FormatAllCharts procedure but works on all the chart sheets in the active workbook:

Sub FormatAllCharts2() Dim cht as Chart For Each cht In ActiveWorkbook.Charts With cht .ChartType = xlLineMarkers .ApplyLayout 3 .ChartStyle = 12 .ClearToMatchStyle .SetElement msoElementChartTitleAboveChart .SetElement msoElementLegendNone .SetElement msoElementPrimaryValueAxisTitleNone .SetElement msoElementPrimaryCategoryAxisTitleNone .Axes(xlValue).MinimumScale = 0 .Axes(xlValue).MaximumScale = 1000 End With Next cht End Sub

Sizing and aligning ChartObjects

A ChartObject object has standard positional (Top and Left) and sizing (Width and Height) properties that you can access with your VBA code. Oddly, the Excel 2007 Ribbon has controls (in the Chart Tools Image from book Format Image from book Size group) to set the Height and Width, but not the Top and Left.

The following example resizes all ChartObject objects on a sheet so that they match the dimensions of the active chart. It also arranges the ChartObject objects into a userspecified number of columns.

Sub SizeAndAlignCharts() Dim W As Long, H As Long Dim TopPosition As Long, LeftPosition As Long Dim ChtObj As ChartObject Dim i As Long, NumCols As Long If ActiveChart Is Nothing Then MsgBox “Select a chart to be used as the base for the sizing” Exit Sub End If ‘Get columns On Error Resume Next NumCols = InputBox(“How many columns of charts?”) If Err.Number <> 0 Then Exit Sub If NumCols < 1 Then Exit Sub On Error GoTo 0 ‘Get size of active chart W = ActiveChart.Parent.Width H = ActiveChart.Parent.Height ‘Change starting positions, if necessary TopPosition = 100 LeftPosition = 20 For i = 1 To ActiveSheet.ChartObjects.Count With ActiveSheet.ChartObjects(i) .Width = W .Height = H .Left = LeftPosition + ((i – 1) Mod NumCols) * W .Top = TopPosition + Int((i – 1) / NumCols) * H End With Next i End Sub

If no chart is active, the user is prompted to activate a chart that will be used as the basis for sizing the other charts. I use an InputBox function to get the number of columns. The values for the Left and Top properties are calculated within the loop.

CD-ROM 

This workbook, named Image from book size and align charts.xlsm, is available on the companion CD-ROM.

Exporting a chart

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.

The procedure that follows uses the Export method of the Chart object to save the active chart as a GIF file.

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”. Usually, GIF and PNG files look better. Keep in mind that the Export method will fail if the user doesn’t have the specified graphics export filter installed. These filters are installed in the Office (or Excel) setup program.

Exporting all graphics

One 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 Image from book Clipboard Image from book Paste Image from book As Picture Image from book Paste As Picture).

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

The procedure starts by saving the active workbook. Then it saves the workbook as an HTML file, closes the file, and re-opens the original workbook. Next, it deletes the HTML file because we’re just interested in the folder that it creates (that’s where the images are). The code then loops through the folder and deletes everything except the PNG files. Finally, it uses the Shell function to display the folder.

CD-ROM 

This example is available on the companion CD-ROM. The filename is Image from book export all graphics.xlsm.

Previous Page

Next Page

Leave a Reply


Time limit is exhausted. Please reload the CAPTCHA.

Categories

apply_now Pepperstone Group Limited