2

I'll try to be very precise with my problem but if you have any questions, please ask!

So basicelly I have a column A full of dates (from line 1 to line 80) and a column B full of values (stock prices, and also with the same range). I have created a chart with this code :

Sub Chart()
   Range(Cells(1, 1), Cells(80, 1))Select
   Range(Selection, Selection.Offset(0, 1)).Select
   Set rSource=selection
   ActiveChart.SetSourceData Source:=rSource, PlotBy:=xlColumns
   ActiveChart.Location Where:=xlLocationAsNewSheet
end sub

So this is not a chart object but a new chart on a new sheet (I say that because all the things I've read on this subject is dealing with chart objects and not with chart alone in a sheet). Now what I need is to draw an horizontal line between two dates (a support line). For example, I need to draw a line between 01/01/2013 (line 1) and 06/25/2013 (line 80). The line has to stay at the same level on the y-axis (that's what we call an horizontal line :) ) at the level of the stock price of 01/01/2013.

I've tried that :

Sub supportline()
Dim CoordA As Variant
CoordA = [a1:b80]
ActiveChart.Shapes.AddLine(CoordA(1, 1), CoordA(1, 2), CoordA(80, 1), CoordA(80, 2)).Select
end sub

But the line isn't drawn where I want. I think there's a problem of scale but I can't figure out how to have the accurate coordinates.

Thank you very much for your time and your help

EDIT (06/11/2013) I've simplified the data base to illustrate my problem : http://cjoint.com/?CFksHluf0VD I'm trying to make a line between the two dates (01/01/2013 and 16/01/2013)

If you download this file, you'll see in the VBA part : 1/ Test1 : I've tried to make a line to link 2 points of the chart : if you launch the sub you'll see that the line is drawn but at the wrong scale and wrong place 2/ Test 2: I've tried to make an horizontal line between two points but that doesn't work at all

Test 2 is what I need to do for my original project (an horizontal line which goes from one date to another with the same y-value)

user2460449
  • 65
  • 2
  • 9
  • No it was just an example, but I'll need to draw lines that goes from two dates that aren't the first and the last datas of the serie. And i have to do it with a VBA code. – user2460449 Jun 10 '13 at 13:41
  • excuse me, i didn't answered to your second question. When i say sheet I mean my chart is alone in a Chart (collection). If you don't understand you can download the file on my last post – user2460449 Jun 10 '13 at 17:13

1 Answers1

3
ActiveChart.SeriesCollection(1).Select

With Selection.Format.Line

.Visible = msoTrue
//add line style etc

End with

Then use your select statement to choose which points you want joined.

If you look at this link, I'll think you'll find it most useful

VBA-Controlled Conditional Formatting of Line Chart Lines

http://peltiertech.com/WordPress/conditional-formatting-of-lines-in-an-excel-line-chart-using-vba/

Another question on Stack Overflow

Excel VBA - How do you set line style for chart series?

In response to your comments, please see the answer to this question. It offers some good explanation of using charts (in this case vb.net)

Chart: Show more value descriptions on X-Axis

And a link to MSDN re this

http://msdn.microsoft.com/en-us/library/system.windows.forms.datavisualization.charting.chart.aspx

Community
  • 1
  • 1
  • I understand what you mean but unfortunately this doesn't work : "addline" applies to "shapes" ( http://msdn.microsoft.com/en-us/library/office/aa221724(v=office.11).aspx ) But "shapes" can't be applied to "points" ( http://msdn.microsoft.com/en-us/library/office/aa174305(v=office.11).aspx ) So I wrote ActiveChart.SeriesCollection(1).points(1).shapes.AddLine(CoordA(1, 1), CoordA(1, 2), CoordA(80, 1), CoordA(80, 2)).Select But it can't work – user2460449 Jun 11 '13 at 08:16
  • Yes i would like some help to write the code please because I can't figure out how to do I'm sorry The line chart has to be horizontal The much straightforward solution is to create a new graph with a 3rd series of data? So basically, if we take back my old example, I have to write the value 20 from line 1 to 16 in the column C? then select the range("A1:C20") and create a new graph? – user2460449 Jun 11 '13 at 11:42
  • 1
    In fact, it's working like that !! I just have to record the macro and it'll work with VBA!! Thanks a lot for your help – user2460449 Jun 11 '13 at 11:43
  • 1
    i can't upvote because i don't have enough reputation! thanks ! :) – user2460449 Jun 11 '13 at 12:34