Fit line within the chart using Excel-VBA

[elogesh]

hi,

I have the following data in sheet1 from A1 to B10

0 0
100 100
200 100
300 100
400 100
500 100
600 100
700 100
800 100
900 100

Used the following code to plot horizontal line in the chart1. But unable to fit the line within the chart.


Option Explicit


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/17/2016 by Elogesh
'

'


Dim last_row As Integer
Dim i As Integer
Charts.Add

Worksheets("Sheet1").Activate
last_row = Sheet1.Range("A1").End(xlDown).Row

For i = 1 To last_row - 1

ActiveWorkbook.Sheets("Chart1").Activate
ActiveSheet.Shapes.AddLine(Sheet1.Cells(i, 1), Sheet1.Cells(i,2), Sheet1.Cells(i+1, 1), Sheet1.Cells(i+1,2)).Select
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.ForeColor.RGB = RGB(50, 0, 128)
Selection.ShapeRange.Line.Weight = 3



Next i


End Sub




Kindly let us know how to fit the lines within the chart. Whether X, Y range for the chart can be specified prior to "addline", so that it can be autofit/scaled within the chart.



Thanks
Logesh

 

[IDS]

Your macro is creating an empty chart, then drawing some lines inside the chart, but they aren't chart lines, so there is no connection between the chart scale and the scale of the lines. Also the lines use screen coordinates, not chart coordinates, so the first line slopes down from the top-left corner, rather than sloping up from the origin.

It is possible to scale drawn lines with VBA, but if you are using a graph it is much easier to use the graph lines.

If you record a new macro, and select the data then insert a graph of the required type that will automatically scale the chart limits, just as it does when you create a graph manually.

If you really want to work with drawn lines rather than chart lines for some reason, search my blog for drawing with VBA and you should find something on plotting lines (and other shapes) from coordinates.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

HI,

Doug made some good points. May I add an additional comment. Your table of source data appears to have no headings. Headings help to give your data context. In addition, I'd suggest using the Structured Table feature that Excel acquired in version 2007. If you link your chart to data in a Structured Table, then whenever you add or delete rows of data, your chart will respond accordingly.

To convert a table to a Structured Table: Insert > Tables > Table

Your Structured Table can be returned to a normal table at any time. Notice the context sensitive pop up in the Ribbon when you select in a Structured Table.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]