Creating The Chart For An Owc Chartspace Control

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The following code, which is located in the UserForm code module, creates a chart using data stored on a worksheet. The category labels are in A2:A13, and the chart data is in B2:B13. It assumes that the ChartSpace object is named ChartSpacel.

Sub CreateChart()

Dim Chartl As ChChart 'WCChart Dim Seriesl As ChSeries 'WCSeries Dim r As Integer Dim XValues(1 To 12) Dim DataValues(1 To 12)

' Add a chart to the ChartSpace

Set Chart1 = ChartSpace1.Charts.Add

' Give it a title With Chart1

.HasTitle = True .Title.Caption = Range("B1") End With

XValues(r - 1) = Cells(r, 1) DataValues(r - 1) = Cells(r, 2) Next r

' Create a chart series

Set Series1 = Chart1.SeriesCollection.Add

' Specify chart type and data With Series1

.Type = chChartTypeColumnClustered .SetData chDimCategories, chDataLiteral, XValues .SetData chDimValues, chDataLiteral, DataValues End With End Sub

The code starts with variable declaration. Two arrays are declared: one to hold the category labels (XValues) and one to hold the data (DataValues).

The Set statement creates a Chart object within the ChartSpace. This Chart object is named Chart1. The next block of statements sets the chart's title, using the label in cell B1. A For-Next loop reads the worksheet data into the arrays.

The next Set statement adds a series to the chart, and the Series object is named Series1. The With-End With block of code specifies the chart type (a standard column chart) and specifies the data for the series.

You'll find documentation for the OWC objects on your hard drive. These Help files are installed when the OWC is installed. Or, you can use the Object Browser to learn more about the properties and methods of these controls.

It's important to understand that the object model for creating a chart in the OWC does not correspond to the object model for creating a chart in Excel. Chapter 18 explains how to use VBA to manipulate "real" Excel charts.

Figure 15-14 shows a slightly more sophisticated version of this example. In this case, the user can choose which data will appear in the chart; this version also includes an option to export the chart as a GIF file.

Using the Office Web Components

The Office Web Components were designed to create interactive Web pages. The components include a Spreadsheet, a Chart, and a Pivot Table. When you create an application that uses the OWC, anyone who uses your application must have the OWC installed on his or her computer.

The OWC is included with Microsoft Office 2000 and later. Installation is not automatic. In other words, you can't assume that all Microsoft Office users have the OWC installed on their system because they might have chosen not to install it.

Therefore, you should use caution before deciding to include any OWC controls in your Excel application. If your application will have general distribution, you'll probably want to avoid using the OWC.

Vba Userform Chartspace Type
Figure 15-14: This UserForm contains a ChartSpace control (part of the Office Web Components).

This application is available on the companion CD-ROM. It requires Excel 2002 or later.

Was this article helpful?

+3 -1


Post a comment