none
How to read values from a chart object RRS feed

  • Question

  • I'm trying to read values from my chart data series then store them in an array. I'm using VB for applications. The chart object is in MS Excel. I run into an error when I attempt to use this extract values from the data series (see loop at bottom - S1.Value(X)). Error is object does not support this property. Obviously, this is not the correct property, but is there one to read individual data point values from a chart?

    Thanks is advance for your help. 

    *******************************my code************************** 

    Dim varDivA(), varDivP, varDivH As Variant
    Dim cht As Chart
    Dim S1, S2, S3 As Series
    Dim P As Point
    Dim vals1, Vals2, Vals3 As Variant
    Dim X, P1, P2, P3 As Integer


    Private Sub CommandButton1_Click()

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set S1 = cht.SeriesCollection(1)
    Set S2 = cht.SeriesCollection(2)
    Set S3 = cht.SeriesCollection(3)


    vals1 = S1.Values
    Vals2 = S2.Values
    Vals3 = S3.Values

    P1 = S1.Points.Count
    P2 = S2.Points.Count
    P3 = S3.Points.Count

    ReDim varDivA(P1)

    For X = LBound(vals1) To UBound(vals1)

        varDivA(X) = S1.Value(X)
        
    Next X

    Monday, June 25, 2018 6:51 PM

Answers

  • Hello ViennaNight,

    I would suggest you try if below code works for you.

    Dim S1 As Series
    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set S1 = cht.SeriesCollection(1)
    vals1 = S1.Values
    P1 = S1.Points.Count
    ReDim varDivA(1 To P1)
    For X = LBound(vals1) To UBound(vals1)
        varDivA(X) = S1.Values(X)
    Next X

    Or

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set S1 = cht.SeriesCollection(1)
    vals1 = S1.Values
    P1 = S1.Points.Count
    ReDim varDivA(1 To P1)
    For X = LBound(vals1) To UBound(vals1)
        varDivA(X) = vals1(X)
    Next X
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ViennaNight Monday, July 2, 2018 12:23 PM
    Friday, June 29, 2018 1:48 AM

All replies

  • Hello ViennaNight,

    >> varDivA(X) = S1.Value(X)

    varDivA(X) = S1.Values(X)

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 26, 2018 3:00 AM
  • Thanks Terry! I fixed the typo, but the issue is an error message stating that "the property let procedure not defined and the property get procedure did not return an object - run time error 451". The code breaks at that same statement. Any ideas of how to fix this? It is the values property from the data series object that is not working when used in a loop to retrieve values from an Excel chart.

    Many thanks,

    Allen

    

    Tuesday, June 26, 2018 4:47 PM
  • Hello Allen,

    What's the source data of the chart? How do you create the chart? For avoiding misunderstanding, I would suggest you share a simple workbook so we could use it to try to reproduce your issue. Please remember to remove any sensitive information from it.

    For sharing the document, you could share it via Cloud Storage, such as One Drive, and then put the link address here.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 27, 2018 3:05 AM
  • Thanks Terry! This is just an Excel chart with data pulled from Excel tables. See link below to the file.

    https://drive.google.com/drive/folders/12WdqFzXnCKEVGdl4MnnM6UfLzhE6-Kbo?usp=sharing

    Let me know if that worked. 

    Best,

    Allen

    Wednesday, June 27, 2018 2:19 PM
  • Hello VienaNight,

    I failed to download the document. Is there any thing I should do to download the the file? 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 28, 2018 3:17 AM
  • Sorry about that! Try this link again:

    https://drive.google.com/drive/folders/12WdqFzXnCKEVGdl4MnnM6UfLzhE6-Kbo?usp=sharing

    Thursday, June 28, 2018 11:57 AM
  • Hello ViennaNight,

    I would suggest you try if below code works for you.

    Dim S1 As Series
    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set S1 = cht.SeriesCollection(1)
    vals1 = S1.Values
    P1 = S1.Points.Count
    ReDim varDivA(1 To P1)
    For X = LBound(vals1) To UBound(vals1)
        varDivA(X) = S1.Values(X)
    Next X

    Or

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set S1 = cht.SeriesCollection(1)
    vals1 = S1.Values
    P1 = S1.Points.Count
    ReDim varDivA(1 To P1)
    For X = LBound(vals1) To UBound(vals1)
        varDivA(X) = vals1(X)
    Next X
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ViennaNight Monday, July 2, 2018 12:23 PM
    Friday, June 29, 2018 1:48 AM
  • Thanks Terry! That works. The key is to make sure that when dimensioning the series is to use a separate statement for each series; otherwise, it will revert to Variant.

    Best,

    ViennaNight

    Monday, July 2, 2018 12:22 PM