VBA problem with FullSeriesCollection object, how to get "Values" Property returned?

[TomCon]

I added a series to a chart and recorded as a Macro. The following VBA was recorded:

Sub Macro2()
'
' Macro2 Macro
'
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(6).Name = "=""XXX"""
ActiveChart.FullSeriesCollection(6).Values = "=Goal2!$P$120:$P$3929"
End Sub

I deleted the series and ran the VBA recorded to test it, and it worked fine.

I then deleted the series again, and put a breakpoint at the "End Sub" line, and ran the Macro to the breakpoint at the end.

In the VBA immediate window, I did the following:

?activechart.FullSeriesCollection(6).name
XXX
?Activechart.FullSeriesCollection(6).values
[Runtime Error 13, Type Mismatch]
<runtime error="" 13,="" type="" mismatch"="">
As you see the query for the Name worked fine and returned the correct name, "XXX".

The query for the "Values" property gives "Run time error 13, type mismatch".

How can i fetch the "Values" property for an item of a FullSeriesCollection? Ultimately, I want to, within VBA code, get the string from the "Values" property, manipulate it, and then re-set the property to the new value after i manipulate it.

Any help is appreciated.

Thank you!</runtime>

 

[James006]

Hello,

You could test following with a txt string :

[COLOR=#333333]txt = ActiveChart.SeriesCollection(6).Formula [/COLOR]

HTH

 

[AlphaFrog]

For a chart series, the .XValues and .Values properties are arrays. You can't return an array like that in the Immediate Window.

Loop through the array and manipulate each element one at a time.

[COLOR=darkblue]Sub[/COLOR] Macro2()
[COLOR=green]'[/COLOR]
[COLOR=green]' Macro2 Macro[/COLOR]
[COLOR=green]'[/COLOR]


ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(6).Name = "=""XXX"""
ActiveChart.FullSeriesCollection(6).Values = "=Goal2!$P$120:$P$3929"

[B][COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]

v = ActiveChart.FullSeriesCollection(6).Values

 [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](v) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v)
    Debug.Print v(i) & " " & i & " Original"
    v(i) = Left(v(i), 3) [COLOR=green]'Manipulate string here[/COLOR]
    Debug.Print v(i) & " " & i & " New"
 [COLOR=darkblue]Next[/COLOR] i

ActiveChart.FullSeriesCollection(6).Values = v[/B]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[TomCon]

Thank you for taking the time for this reply. It seems like the Values property is an array of length "the number of points in the series". But, that is not what i actually wanted to manipulate and re-set. I wanted a string in the form that the Macro uses to set the Values property. The Macro uses this line of code:

ActiveChart.FullSeriesCollection(6).Values = "=Goal2!$P$120:$P$3929"

What i want to do is substitute for "3929" and change it to extend the range. The reason i want a macro is that i have many charts and many series, and want to make a change globally, so i thought i could loop through all the charts and all the series in each chart, with a macro.

I see another reply mentioned the "Formula" property. By experimenting, i see that that property can both return a string and then be set as a string in the same form as it returns, so i think it will solve my problem. The form of the string returned by the "Formula" property is not the same as the one used to set the Values property in the generated Macro code, but it still is a form i can manipulate and use to accomplish my purpose.

My only remaining question/observation is that "this seems confusing". Why does macro code use a string value to set the Values property, but if you turn around and ask for the value of the Values property, immediately after it has just been set, it is not the string value that the property was just set to? In fact, no value is returned; you simply get an error.

Thanks again for your time and these suggestions and clarifications!

 

[AlphaFrog]

My only remaining question/observation is that "this seems confusing". Why does macro code use a string value to set the Values property, but if you turn around and ask for the value of the Values property, immediately after it has just been set, it is not the string value that the property was just set to? In fact, no value is returned; you simply get an error.

The .Values property of a series collection is an array. When you assign a string, it automatically evaluates that string as a range reference which is an array. You cannot apply just any string. It must be a string that evaluates as a range reference. Alternatively, you can assign an actual array to it. e.g;

ActiveChart.FullSeriesCollection(6).Value =  Array(1, 2, 3, 4)

When you read the .Values property. It returns the actual array of values and not an error. That array cannot be written on whole to the Immediate window. That's the "type mismatch" error you get; trying to write an array to the Immediate window. You would have to loop through the array and write each element to the Immediate window.

 

[TomCon]

Thanks much for the explanation! So, i would call it a "quirk" or "secret" or something. But, good to know thats the way it works. You can assign the array values, or you can assign a string that has the form of a range reference, and it is evaluated to the values that it points to.

Appreciate it!

 

[Jon Peltier]

You can assign an array to the .Values or .XValues of a chart series, or you can assign an address (a range reference, including the sheet prefix), and you can also assign a VBA range variable.

But I sense what you want is to modify the series formulas of a lot of series, like a Find-replace. I've written such routines, and describe them in a tutorial, Change Series Formula – Improved Routines.

 

[TomCon]

Thanks much1 Indeed that is my case, very many chart sheets in a workbook, and generally about 2-8 series on each chart. Indeed I want to make a "global" edit to the range of all the series of all the charts. So, your link does help, as well as all the comments above. I think i will probably fetch the "Formula" property of the Series, and then change it, and then re-set it.

Thanks!