# Set chart series point value to a variable

Posted by Tom on January 02, 2001 2:09 PM

Hi

Could someone please help I am trying to read and set a chart series point to a variable name. This is what I have tried MValue = ActiveChart.SeriesCollection(1).Points(d).Value and Set MValue = ActiveChart.SeriesCollection(1).Points(d).Value

Posted by Tim Francis-Wright on January 03, 2001 11:30 AM

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