Create a Chart on Userform from within a Function (not Sub)

[eros]

Hi there,

My function dynamically generates new data set to be used to draw a chart with 3 series in total: one for x axis and 2 for y axis. This is a simple line diagram but the challenge here is that I cannot use the cell values because all data is in a series of public array variables in VBA; therefore I have to use a function instead of a sub.

The chart should be plotted on-the-fly over my userform as the data series change.

Any ideas?

Thanks in advance.

 

[tusharm]

Four points:
1) *Each* series requires X *and* Y values. You cannot have a series for just one axis.

2) Look at using the Values and XValues properties of a Chart Series. You can provide the values from an array, keeping in mind that the length of the resulting SERIES formula is constrained to some maximum size.

3) There's no reason to conclude that you have to use a function rather than a sub. Updating the Values (and XValues) properties is not restricted to a particular type of procedure.

4) You cannot show a chart in an userform. The best one can do is create the chart in Excel, export it as an image, and import that image into the userform.

Hi there,

My function dynamically generates new data set to be used to draw a chart with 3 series in total: one for x axis and 2 for y axis. This is a simple line diagram but the challenge here is that I cannot use the cell values because all data is in a series of public array variables in VBA; therefore I have to use a function instead of a sub.

The chart should be plotted on-the-fly over my userform as the data series change.

Any ideas?

Thanks in advance.

 

[eros]

Thank a lot for your prompt return.

Briefly to reply your concerns:

1) That's fine. The data for the x axis is common for both of the data series in y axis. So, your requirement is provided.

2) I have an array of 10 elements to feed the series in the chart. Could you give me an example as to how to use an array to feed Values and XValues of a chart.

3) I agree. However, my procedure within which data is calculated to feed the chart series is to be a function because I rely on a return value once the function run is over each time.

4) I have just read an article about embedding an Graph Object into a userform. That might be what I am looking for.

Many thanks again for your kind return. May I have your final comments please to my points above, especially for item 2.

 

[pgc01]

Hi

2) this is a small example that creates a chart in Sheet1 and adds 2 series with values taken out of arrays.

Run this code:

Sub CreateChart()
Dim ws As Worksheet
Dim chtO As ChartObject
Dim vXValues As Variant
Dim vYValues1 As Variant, vYValues2 As Variant
 
Set ws = Worksheets("Sheet1")
Set chtO = ws.ChartObjects.Add(50, 50, 400, 200)
chtO.Name = "MyChart"
 
vXValues = Array("First", "Second", "Third")
vYValues1 = Array(3, 5, 4)
vYValues2 = Array(1, 4, 6)
 
With chtO.Chart
    .ChartType = xlColumnClustered
    
    With .SeriesCollection.NewSeries
        .XValues = vXValues
        .Values = vYValues1
        .Name = "MySeries1"
    End With
    
    With .SeriesCollection.NewSeries
        .Values = vYValues2
        .Name = "MySeries2"
    End With
    
End With
End Sub

3) not clear to me