VBA charting Question

[Gromit]

Hi,

I'm trying to write some code to create a chart and format the series
Things were all going well until I ran into the problem of adding
different MarkerStyle to each series. I have a group of cells in
worksheet (cells 12,15) to (cells 17,15) with the markertype name
listed (e.g. xlDiamond etc). I first put these into an arra
MarkerTypeArray(i) using a For Next Loop which the watch window tell
me was successful.

However, when it comes to putting the names from the array into th
chart series, things don't work out, and I can't figure out why. I'
using the code: (With series) .MarkerStyle = MarkerTypeArray(i)


When I hardwire .MarkerStyle = xlDiamond, it works. And I know tha
MarkerTypeArray(i) contains the string 'xlDiamond'. So why doesn'
.MarkerStyle = MarkerTypeArray(i) work?

Thanks in advance. The full code is below.

Graham


Sub AddChart()
Dim myChtObj As ChartObject
Dim MyNewSrs As Series
Dim MarkerTypeArray(12 To 17)
Dim i As Integer

For i = 12 To 17
MarkerTypeArray(i) = Workbooks("B19.xls").Sheets("Summary").Cells(i
15)
Next i


Workbooks("B19.xls").Sheets("Summary").ChartObjects.Delete

Set myChtObj
Workbooks("B19.xls").Sheets("Summary").ChartObjects.Add _
(Left:=100, Width:=500, Top:=75, Height:=400)
myChtObj.Chart.ChartType = xlLineMarkers
myChtObj.Chart.SetSourceData Source:=Sheets("Summary").Range("A1")

For i = 12 To 17
Set MyNewSrs = myChtObj.Chart.SeriesCollection.NewSeries
MsgBox MarkerTypeArray(i)
With MyNewSrs
.Name = Cells(i, 13).Value
.Values = Cells(i, 14).Value
.XValues = Array(6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5)
.MarkerStyle = MarkerTypeArray(i) '******** GIVES ERROR
.Border.Weight = xlThin
.Border.LineStyle = xlNone
.MarkerBackgroundColorIndex = 2
.MarkerForegroundColorIndex = 11
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With

Next

 

[Tom Ogilvy]

the string xlDiamond is meaningless. xlDiamond is a constant defined to
mean

? xlDiamond
2

the number 2.

so you would need to pass the value 2 to MarkerSTyle

..MarkerStyle = 2

or MarkertypeArray(1) = xlDiamond
or MarkertypeArray(1) = 2

then
i = 1
..MarkerStyle = MarkertypeArray(i)

 

[Gromit]

Much appreciated!


Graha