none
VBA Chart X - Y Axis RRS feed

  • Question

  • Hello guys, I found the code below from stackoverflow, https://stackoverflow.com/questions/44361927/coding-a-scatter-plot-with-smooth-lines-in-vba

    Can anyone give a clue, how I can use it in Word VBA?

    Thank you.

    Sub CreateChart()
    Dim wsData As Worksheet, wsChart As Worksheet
    Dim LastRow As Long
    Dim xData As Range, yData As Range, GraphRange As Range
    Dim cht As Shape
    
    Application.ScreenUpdating = False
    
    Set wsChart = Sheets(1)
    Set wsData = Sheets(2)
    LastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
    Set xData = ThisWorkbook.Worksheets(2).Range("A1:A" & LastRow)
    Set yData = ThisWorkbook.Worksheets(2).Range("B1:B" & LastRow)
    
    Set GraphRange = Union(xData, yData)
    
    'Create a chart
    Set cht = ThisWorkbook.Worksheets(1).Shapes.AddChart2(, xlXYScatterLinesNoMarkers)
    
    'Give chart some data
    cht.Chart.SetSourceData Source:=GraphRange
    cht.Chart.FullSeriesCollection(1).Format.Line.Weight = 5
    Application.ScreenUpdating = True
    End Sub


    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Monday, August 28, 2017 8:34 AM

Answers

  • Hi cguan,

    Data source of a chart in Word is also stored in a workbook. You could get the workbook and then edit data in it.

    Here is the example.

    Sub addchartinword()
    Dim cht As Chart
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set cht = ActiveDocument.Shapes.AddChart2(, xlXYScatterLinesNoMarkers).Chart
    Set ws = cht.ChartData.Workbook.Worksheets(1)
    For i = 2 To 100
    ws.Cells(i, 1) = i
    ws.Cells(i, 2) = i Mod 10
    Next i
    cht.FullSeriesCollection(1).Format.Line.Weight = 5
    Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    • Edited by Terry Xu - MSFT Monday, August 28, 2017 11:42 AM
    • Marked as answer by cguan Tuesday, August 29, 2017 3:11 AM
    Monday, August 28, 2017 11:39 AM

All replies

  • Hi cguan,

    Data source of a chart in Word is also stored in a workbook. You could get the workbook and then edit data in it.

    Here is the example.

    Sub addchartinword()
    Dim cht As Chart
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set cht = ActiveDocument.Shapes.AddChart2(, xlXYScatterLinesNoMarkers).Chart
    Set ws = cht.ChartData.Workbook.Worksheets(1)
    For i = 2 To 100
    ws.Cells(i, 1) = i
    ws.Cells(i, 2) = i Mod 10
    Next i
    cht.FullSeriesCollection(1).Format.Line.Weight = 5
    Application.ScreenUpdating = True
    End Sub

    Best Regards,

    Terry


    • Edited by Terry Xu - MSFT Monday, August 28, 2017 11:42 AM
    • Marked as answer by cguan Tuesday, August 29, 2017 3:11 AM
    Monday, August 28, 2017 11:39 AM
  • Thank you so much, Terry.


    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Tuesday, August 29, 2017 3:00 AM
  • This codein Excel:
    cht.Chart.SetSourceData Source:=GraphRange

    The equivalent of this in Word is this:
    Set ws = cht.ChartData.Workbook.Worksheets(1)

    Is it correct? Or what?

    Sorry for asking again, i just want to know.


    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.



    • Edited by cguan Tuesday, August 29, 2017 3:12 AM edit
    Tuesday, August 29, 2017 3:04 AM
  • Hi cguan,

    When inserting a chart to a word document, it has been set source data to its worksheet automatically, so it don't need to set source data again unless you need set another range as data source. So you could get worksheet from its chartdata directly.

    When inserting a chart to a worksheet, it's data source is empty until you set its data source. So you have to set the data source. You could easily to see the difference when you insert a chart in word and excel.

    Best Regards,

    Terry

    Tuesday, August 29, 2017 4:10 AM
  • Got it, Thanks Terry.

    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Tuesday, August 29, 2017 4:28 AM