VBA: Add Mean and Standard Deviation to a dynamically generated graph

Question

I am currently using this function to generate a graph:

Function make_chart(Fam, Data, Row_Start, Title)
Dim wbsheet As String
sheetName = ActiveSheet.Name

...

Set Char = Charts.Add

With Char
    For Each s In .SeriesCollection
        s.Delete
    Next s
    With .SeriesCollection.NewSeries()
        .Values = Data
        .XValues = Fam
    End With
    .ChartType = xlColumnClustered
    .HasTitle = True
    .ChartTitle.Text = Title_Str
    .HasLegend = False
    .Name = Title_Str
    .Axes(xlCategory).TickLabels.Font.size = 8
End With

Worksheets(sheetName).Activate
End Function

Basically it is being called within a loop that is selecting chunks of data to graph dynamically. I have been asked to add a mean and standard deviation to each of these graphs which are being generated without a practical means to alter the source worksheet. Since I am unable to alter source to generate this information I was hoping to use Application.WorksheetFunction.StDev and WorksheetFunction.Average to generate this information and insert it into the chart code.

Is there a way to pass this in via .Values or maybe by adding a new series. Still wrapping my head around how vba works for generating graphs, so I can't think of a way to bring the data into each graph within the current code framework I have. I have found previously that the addition of series seems to create errors in how the original data is plotted. Any help would be appreciated.

Answer 1

You don't specify whether Fam and Data are ranges or arrays. Either can be used as the .Values and .XValues properties of a Series. Either can also be used in WorksheetFunctions:

MyStDevX = WorksheetFunction.StDev(Fam)
MyStDevY = WorksheetFunction.StDev(Data)

MyMeanX = WorksheetFunction.Average(Fam)
MyMeanY = WorksheetFunction.Average(Data)