Using Office Web Components to draw graphs on Excel userforms
The Office Web Components (OWC) are a series of components that you can add into a web page or any Office application. A typical use of the OWC in Office application is to add a graph in a user defined form. When I developed a userform for curve fitting, I’d like to see how good the data was fitted by a given optimized parameter set. If it is not good enough, user can rerun optimization process to refit the data even can get rid of some data points. The userform is an interactive tool for curve fitting. I really need draw a scatter plot and predicted curve on the userform. The way to achieve the goal is to utilize OWC objects.
The posts give a simple example to demonstrate how to use the OWC to draw a graph on a userform in Excel. To facilitate learning, here is an step-by-step instruction. I suppose you a;ready open Excel with an blank document.
- Click Tools -> Macro -> Visual Basic Editor to open VBA for Application
- Select VBAProject(Book1) in the Project window
- Click Insert -> UserForm to create new userform in the selected VBA project
- Put a command button to the userform
- Find the Toolbox window and right click its blank area
- Click Additional Controls … from the popup menu
- Select “Microsoft Office Chart xx.xx” and click Ok button in the popup window. Now there is one more button in the toolbox window
- Put a new control to the userform and adjust its size. The new control is the chart space holder that you can add new chart and series to it. The form looks like Figure 1.
Figure 1 - Double click the command button and switch to code view. Here you can enter code to generate graph. To demonstration purpose, I generate two data series. They will be drawn on a chart object. The code is listed at the end of the post.
- Once you have done this. You can click “Run Sub/UserForm’ button on the toolbar. Then click the CommandButon1 in the userform windowto show the curve. The windows looks like Figure 2.
Figure 2
The source code
Private Sub CommandButton1_Click()
Dim i As Integer
Dim NumObs As Integer
NumObs = 10
ReDim obs(NumObs) As Variant
ReDim pred(NumObs) As Variant
ReDim ot(NumObs) As Variant
Randomize
For i = 1 To NumObs
ot(i) = i * 5
obs(i) = i * 5 + Rnd() * 5
pred(i) = i * 5 + Rnd()
Next
Dim oChart As WCChart
Dim oSeries1 As WCSeries
Dim oSeries2 As WCSeries
With ChartSpace1
.Clear
.Refresh
Set oChart = .Charts.Add
Set oSeries1 = oChart.SeriesCollection.Add
oChart.Type = chChartTypeScatterMarkers
oSeries1.Type = chChartTypeScatterMarkers
oSeries1.SetData chDimXValues, chDataLiteral, ot
oSeries1.SetData chDimYValues, chDataLiteral, obs
Set oSeries2 = oChart.SeriesCollection.Add
oSeries2.Type = chChartTypeScatterLine
oSeries2.SetData chDimXValues, chDataLiteral, ot
oSeries2.SetData chDimYValues, chDataLiteral, pred
End With
With oChart
With .Axes(chAxisPositionBottom)
.MajorUnit = 20
.MajorTickMarks = chTickMarkInside
.MajorGridlines.Line.Color = RGB(230, 230, 230)
End With
With .Axes(chAxisPositionLeft)
.MajorUnit = 5
.MajorTickMarks = chTickMarkInside
.MajorGridlines.Line.Color = RGB(230, 230, 230)
End With
With .PlotArea
.Interior.Color = RGB(255, 255, 255)
End With
Dim xmin, xmax
xmin = 1000
xmax = -1000
For i = 1 To NumObs
If obs(i) < xmin Then
xmin = obs(i)
End If
If obs(i) > xmax Then
xmax = obs(i)
End If
Next
'Set the maximum and minimum axis values
.Scalings(chDimXValues).Maximum = Round(ot(NumObs) + 2, 0)
.Scalings(chDimXValues).Minimum = Round(ot(1) - 2, 0)
.Scalings(chDimYValues).Maximum = 60 'Round(xmax + 1, 0)
.Scalings(chDimYValues).Minimum = 0 'Round(xmin - 1, 0)
'Change the marker and line styles for the series
With oSeries1
.Marker.Style = chMarkerStyleSquare
.Marker.Size = 3
.Interior.Color = RGB(0, 0, 0)
.Line.Weight = 0
.Line.Color = RGB(255, 255, 255)
End With
With oSeries2
.Line.Weight = 1
.Line.Color = RGB(0, 0, 255)
End With
End With
End Sub
The above code and steps were realized in Microsoft Excel 2000. For Excel 2003, you have do the following change: WCChart to ChChart and WCSeries to ChSeries. The OWC version change from 9.0 to 11.0. Other things are essentially same.
Reference
- Using worksheet data in the chart office web component
- HOW TO: Find Office Web Components (OWC) Programming Documentation and Samples
Download the sample userform and code – form-chart
Great site. Keep doing.,