none
Unable to set the Values Property of the Series Class

    Question

  • Hello All,
    I'm a very beginner for VBA, infact I learnt it only today.
    I'm having a problem while creating charts.
    What I want is to create a chart that will compare two distributions on a stack.
    e.g.
    50
    20
    6
    10




    45


    25

    5

    12

    That is two say the two series are far apart. Far apart in the sense that if first is A1:A4 then the other is let's say C9:C12
    but they should compare on a stack chart.

    What I do is, I select the first series first and then select second making two selections thereby.
    Now I set the data range for each series.
    I make a range out of individual ith cells and then unite them into a new range using the union function.
    But the problem is that when I get the chart, its based only on the basis of first series and it entirely neglects the second series. When I check the source data, it only shows the value(not even the address) of the elements of first list.
    Can someone please guide.

    Sub Stack()

    '
    ' Keyboard Shortcut: Ctrl+t
    '

        Dim SR, t1, t2, tmpRange As Range
        Set SR = Selection
       
        Dim tempVariant As Variant
           
        Charts.Add
        ActiveChart.ChartType = xlColumnStacked
        ActiveChart.SetSourceData Source:=SR, PlotBy:=xlRows
       
        For i = 1 To ActiveChart.SeriesCollection.Count
           ActiveChart.SeriesCollection(1).Delete
        Next i
               
        For i = 1 To SR.Areas(1).Rows.Count
            Set t1 = SR.Areas(1).Cells(i, 1)
            Set t2 = SR.Areas(2).Cells(i, 1)
           
            Set tmpRange = Union(t1, t2)
            MsgBox tmpRange
            ActiveChart.SeriesCollection.NewSeries
           ' MsgBox TypeName(ActiveChart.SeriesCollection(i).Values)
            tmpVariant = tmpRange
           
            ActiveChart.SeriesCollection(i).Values = tmpVariant  ' THIS IS WHERE THE PROBLEM IS
    'If I use tmpRange here, then it gives the error Unable to set The values property
        Next i
       
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
        ActiveChart.HasDataTable = False

    End Sub





    Regards,
    Ashish Agarwal

    Wednesday, June 11, 2008 1:13 PM

All replies

  • It seems that it's not possible. Please correct me if it is.
    So what i did is that  I copied the required values into an array and then set the valued property to that array.

    This is what worked.:


    Sub Stack()
    '
    ' Stack Macro
    ' Macro recorded 6/11/2008 by Ashish Kumar Agarwal
    '
    ' Keyboard Shortcut: Ctrl+t
    '

        Dim SR, t1, t2, tmpRange As Range
       
        Set SR = Selection
        Dim mynum(5) As Variant
       
        Charts.Add
        ActiveChart.ChartType = xlColumnStacked
        ActiveChart.SetSourceData Source:=SR, PlotBy:=xlRows
       
        For i = 1 To (ActiveChart.SeriesCollection.Count)
           ActiveChart.SeriesCollection(1).Delete
        Next i
               
        For i = 1 To SR.Areas(1).Rows.Count
                ActiveChart.SeriesCollection.NewSeries
               
                For j = 1 To SR.Areas.Count
                    Set mynum(j) = SR.Areas(j).Cells(i, 1)
    '               MsgBox mynum(j)
                Next j
               
    '       MsgBox TypeName(ActiveChart.SeriesCollection(i).Values)
           
            ActiveChart.SeriesCollection(i).Values = mynum
           
        Next i
       
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
        ActiveChart.HasDataTable = False

    End Sub



    Please suggest if someone has a better way.

    Regards,
    Ashish Agarwal

    Thursday, June 12, 2008 4:46 AM