Set chart series point value to a variable

Unfortunately, each Point in a SeriesCollection
does _not_ have a Value property. But, take
heart; there are ways around this--the
tricky part is figuring out the form that your
data series is in (there are 3 ways to specify
a data range).

The code assumes that you're working with an X-Y
chart, but I don't think that's going to matter.
You could rework this to be a VB function that
returned a value from a particular point of
a chart.

In an answer late last year, I figured out how
to put a polygon below a line chart--the first
part was to come up with an array of values
corresponding to each of the points in the chart:

Public xleft As Double, xwidth As Double, ytop As Double, yheight As Double
Public xmax As Double, xmin As Double, ymax As Double, ymin As Double

' FillTheChart By Tim Francis-Wright 31 October 2000
' Creates a polygon below the values of an X-Y chart.
' NOTE: The X-Range and Y-Range code is based on code from John
' Walkenbach's site. http://www.j-walk.com/ss. The relevant
' file there is the ChartInfoClass.xls file. Pursuant to
' the copyright notice there, the code may be freely distributed
' and used, but not sold!
'

Sub FillTheChart(ChartNum As Integer)
Dim TheChart As Object, TheArea As Object
Dim Formula As String
Dim FirstComma As Integer, SecondComma As Integer, LastComma As Integer
Dim FirstParen As Integer, SecondParen As Integer
Dim FirstBracket As Integer, SecondBracket As Integer
Dim SeriesName As String
Dim StartY As Integer
Dim XValues, Values
Dim XType As String, YType As String
Dim Points() As Double, XVals() As Double, YVals() As Double
Dim i As Integer, j As Integer, NumPoints As Integer, PlotOrder As Integer


Set TheChart = ActiveSheet.ChartObjects(ChartNum).Chart
xmax = TheChart.Axes(1).MaximumScale
xmin = TheChart.Axes(1).MinimumScale
ymax = TheChart.Axes(2).MaximumScale
ymin = TheChart.Axes(2).MinimumScale
xleft = TheChart.Axes(1).Left
xwidth = TheChart.Axes(1).Width
ytop = TheChart.Axes(2).Top
yheight = TheChart.Axes(2).Height

With TheChart.SeriesCollection.Item(1)
NumPoints = .Points.Count
ReDim Points(NumPoints, 2)
ReDim XVals(NumPoints)
ReDim YVals(NumPoints)

Formula = .Formula
FirstComma = InStr(1, Formula, ",")
SeriesName = .Name

' Get the X Range
If Mid(Formula, FirstComma + 1, 1) = "(" Then
' Multiple ranges
FirstParen = InStr(1, Formula, ",") + 2
SecondParen = InStr(FirstParen, Formula, ")")
XValues = Mid(Formula, FirstParen, SecondParen - FirstParen)
XType = "Multiple"
StartY = SecondParen + 1

ElseIf Mid(Formula, FirstComma + 1, 1) = "{" Then
' Literal Array
FirstBracket = FirstComma + 1
SecondBracket = InStr(FirstBracket, Formula, "}")
XValues = Mid(Formula, FirstBracket + 1, SecondBracket - FirstBracket - 1)
For i = 1 To NumPoints
j = InStr(1, XValues, ",")
If j = 0 Then
XVals(i) = Val(XValues)
Else
XVals(i) = Val(Left(XValues, j - 1))
XValues = Right(XValues, Len(XValues) - j)
End If
Next i

XType = "Array"
StartY = SecondBracket + 1
Else
' A single range
SecondComma = InStr(FirstComma + 1, Formula, ",")
XValues = Mid(Formula, FirstComma + 1, SecondComma - FirstComma - 1)
XType = "Single"
StartY = SecondComma
End If

' Get the Y Range
If Mid(Formula, StartY + 1, 1) = "(" Then
' Multiple ranges
FirstParen = StartY + 1
SecondParen = InStr(FirstParen, Formula, ")")
Values = Mid(Formula, FirstParen + 1, SecondParen - FirstParen - 1)
YType = "Multiple"
LastComma = SecondParen + 1
ElseIf Mid(Formula, StartY + 1, 1) = "{" Then
' Literal Array
FirstBracket = StartY + 1
SecondBracket = InStr(FirstBracket, Formula, "}")
Values = Mid(Formula, FirstBracket + 1, SecondBracket - FirstBracket - 1)
For i = 1 To NumPoints
j = InStr(1, Values, ",")
If j = 0 Then
YVals(i) = Val(Values)
Else
YVals(i) = Val(Left(Values, j - 1))
Values = Right(Values, Len(Values) - j)
End If
Next i

YType = "Array"
LastComma = SecondBracket + 1
Else
' A single range
FirstComma = StartY
SecondComma = InStr(FirstComma + 1, Formula, ",")
Values = Mid(Formula, FirstComma + 1, SecondComma - FirstComma - 1)
YType = "Single"
LastComma = SecondComma
End If

PlotOrder = .PlotOrder
End With

Select Case XType
Case "Single", "Multiple"
i = 1
For Each cel In Range(XValues).Cells
Points(i, 1) = cel.Value
i = i + 1
Next cel
Case "Array"
For i = 1 To NumPoints
Points(i, 1) = XVals(i)
Next i
Case "Multiple"
End Select

Select Case YType
Case "Single", "Multiple"
i = 1
For Each cel In Range(Values).Cells
Points(i, 2) = cel.Value
i = i + 1
Next cel

Case "Array"
For i = 1 To NumPoints
Points(i, 2) = YVals(i)
Next i
Case "Multiple"
End Select

' rest of the code was here
' i is the point #
' Points(i,1) is the X-Value
' Points(i,2) is the Y-Value

End Sub