How to draw lines on an excel chart programmatically

[ExcelBoy90]

I have an excel chart (Stock candlestick chart) which I created manually.
I draw lines on the chart (say a horizontal line from (x1=1 to x2=4 along y = 27)
You can see the red horizontal line in the screen shot below
Candle.png

Though for a quick screenshot purpose, I have shown a column chart above, I actually want to do it on a stock-candlestick chart.

I am comfortable with writing excel functions which do other stuff. I am also able to call these functions from a cell =MyFunction(10,20). But unable to figure out how to do this for a sub/function/macro which draws a line on the chart.

From the docs, it seems I need a sub/function which does something like this

Sub DrawLine()
ActiveSheets.ChartObjects(1).Shapes.AddLine(1, 27, 4, 27)
End Sub

But I have a few problems

1) I want to the co-ordinates of the lines to be points on the chart (x & y co-ordinates as per the chart axis) - however AddLine seems to be absolute geometric points on the chart rather than points which can be represented by x,y co-ordinates as per the chart axes.

2) I am unable to figure how to call this DrawLine so as to draw the line. If I call it from the Run Menu of the VBAProject, I get a

Run Time error 24 - object required.

So how do I call this? In the future, I will also pass parameters to the sub so as to draw multiple unconnected lines which don't pass through or connect the candle itself. How do I do this? I am experienced programmer but only have a basic experience with MS Office programming.

[Joske920]

Hi,

Drawing with extra shapes ontop of the chart is never going to go right
just add a series to your data and make that the line on your chart

Grtz

[ExcelBoy90]

Hi,
just add a series to your data and make that the line on your chart

What do you mean? Could elaborate?

Since this is a stock/candlestick chart, I cannot make a combo chart.

[Glenn Kennedy]

How is this? I added a second dataset, added a trendline and then set dataset 2 to have no fill & no border

[ExcelBoy90]

How is this? I added a second dataset, added a trendline and then set dataset 2 to have no fill & no border

But your primary chart is not a stock/candlestick chart. As I have mentioned in my original question, my chart is a stock/candlestick chart - Excel doesn't allow you to combine it with a different kind of chart. If I add a second series to my chart, excel changes the type of my original chart.

[Glenn Kennedy]

Post your sheet. It was not especially helpful to post a non-editable picture of the wrong sort of chart!!! I'll be honest and admit to not having read all the blah blah that was in your post! So it may be possible, it may not be possible.

Please post an Excel sheet containing representative data. Guidelines for posting a shhet can be found in the yellow banner (top of page).

[MrShorty]

my chart is a stock/candlestick chart - Excel doesn't allow you to combine it with a different kind of chart. If I add a second series to my chart, excel changes the type of my original chart.

You are correct, Excel does not allow you to combine "specialty" charts (like stock charts) with other charts. However, most of the specialty charts (like stock candlestick charts) can easily be created from normal chart types, which can easily be combined with other chart types. A candlestick chart is essentially a box and whisker plot and a box and whisker plot is easily created as a stacked column chart, which can then be easily combined with a line/scatter chart to get what you want. Here's a tutorial that shows how to build a box and whisker plot complete with an additional line/scatter series: https://peltiertech.com/excel-box-an...ams-box-plots/ Go through the tutorial to understand how it all works, then adapt to your stock data.

[ExcelBoy90]

Please post an Excel sheet containing representative data. Guidelines for posting a shhet can be found in the yellow banner (top of page).

I am not doing this here because this thread was about doing it using VBA. I created a separate thread in the Charts sub-forum about this & I have my issue solved for the manual approach - so that part is figured out. I am still interested in the programmatic way of drawing shapes on top of charts in this thread - if at all it's possible.

[Andy Pope]

It's possible to add shapes to a chart using VBA. The difficult part is calculating the position in relation to the source data and scaling being used.