CODEWITHSUNDEEP
excelvba
KrHubert
KrHubert

Reputation: 1030

Add zero to charts data source

I want to add number (0) to values and find a bit of trouble with that. Excels macro record this as 

Sub Makro2()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).XValues = _
        "='Sheet'!$C$221;'Sheet'!$C$223;'Sheet'!$C$225"
    ActiveChart.SeriesCollection(2).Values = _
        "='Sheet'!$B$222;'Sheet'!$B$224;'Sheet'!$B$226"
End Sub

But when I try the same with my code I get error.

Dim lineSeries1 As Range
Dim lineSeries2 As Range
With ActiveChart.SeriesCollection.NewSeries
    .Values = "={0;100}" 'It works 
    .Name = ""
    .XValues = "={0;100}" 'It works 
End With

With ActiveChart.SeriesCollection.NewSeries
    .Values = lineSeries1 ' + {0} or & SomeCellWithZero.Address
    .Name = ""
    .XValues = lineSeries2  ' + {0} or & SomeCellWithZero.Address
End With

So the question is how to add zero to Values?

Upvotes: 0

Views: 546

Answers (1)

Mike Woodhouse
Mike Woodhouse

Reputation: 52316

Personally I'd make my ranges one cell bigger and add a constant zero value. If that's not possible for some reason, what follows may help ;-)

Here's a slightly roundabout way to get there. It might be possible to do it with fewer steps but I can't see how yet.

I'm going to use a VBA function to build a new array from the original range with a zero included. I'm putting the zero at the start of the array, change the code for something different. Here's the VBA function:

Public Function PrependZero(rng As range)    
Dim val As Variant, res As Variant
Dim idx As Long
    val = Application.Transpose(rng.Columns(1).Value) ' get range values as a 1-dimensional array, assumes values are in a column
    ReDim res(LBound(val) To UBound(val) + 1) ' array to hold the extended values
    res(LBound(res)) = 0 ' add the fixed value        
    For idx = LBound(val) To UBound(val)
        res(idx + 1) = val(idx) ' copy the other values
    Next        
    PrependZero = res        
End Function

Excel doesn't seem to like us using a VBA function in a series definition, so we need to add some indirection to fool it. Create a new named formula (Formulas...Define Name). I called mine YSeries and set the "Refers to" value to =PrependZero(Sheet1!$A$2:$A$6), using my Y Value range as the input to the function.

That named formula can be used in a chart series definition: set "Series Y Values" to [YourWorkbookNameHere]!YSeries (use whatever name you created above).

If you want to do the same to the X values, the same approach should work.

Upvotes: 2

Related Questions