I have an Excel worksheet that contains a candlestick chart - but for the purpose of this question it might be a regular 2D-line chart, or any chart that has a horizontal axis.
The data source for the chart is not a math function; instead it's arbitrary data representing prices, read from columns of another worksheet.
My chart is many times wider than the worksheet window - and than the Excel window itself. I browse the chart horizontally by simply using the normal horizontal scrollbar of the worksheet, which scrolls the worksheet including the chart in it.
I placed buttons in the same worksheet to manually adjust through VBA code the vertical scale of the chart (Y-axis), because when scrolling horizontally, the new chart values that become visible might require different minimum and maximum Y-axis values to be displayed in an optimal way.
I would like to replace that manual scaling with automatic scaling: after each scroll, I need to know what is the leftmost visible chart point and the rightmost - that is the first and the last visible points. From that I will calculate the best Y-scale and set it into the chart.
So I'm trying to write the VBA code to detect the first and last visible points. The Chart object doesn't seem to me to have related methods or properties, and so far I couldn't find ways to do that. So that's my question.
The Chart.AutoScaling property won't help as it affects the whole chart, while my goal is to set a vertical scale which is good only for the currently visible portion of the chart. So I need to find the first and last visible points of the chart.
The users of this chart might have an Excel version as old as Excel 2007, so a solution that works with versions that old would be optimal, but solutions that require newer Excel versions would be great as well.
Answer
I rarely post another answer, so this has taken some thinking.
Consider a dynamic chart that shows only a select part of the data, instead of a static chart that shows all data. Consider that instead of building a chart that stretches across multiple screen widths, you could have a single chart that fits the screen perfectly, but the user could determine what they want to see in that chart.
Then give the user a few control cells where they can enter the starting point of the chart data and another control where they can determine how many candles/bars/points they want to see in the chart.
With that information provided by the user, you can apply established dynamic charting techniques like named formulas for chart ranges. Then sprinkle a little VBA on top to determine the min and max of the selected charting range.
Let me know if you want to see a sample of that technique and I'll add a link. It's really not all that hard to do and a much better user experience than scrolling across a chart that disappears off the left and right of the window.
Edit: I created a sample file that you can download with this Dropbox link
You need to enable macros for this file.
The top chart shows all data.
The chart in row 26 uses dynamic ranges. The user can manipulate F17 and F18 to select a starting point and the number of rows to plot. The chart in row 26 then uses the Excel defaults to appropriate the value axis with some padding, as Excel does.
The chart in row 42 (love that number) overrides the Exel defaults and uses the values in F23 and F24 for minimum and maximum values respectively. This is done with a Worksheet Change event macro that runs whenever any cell in the sheet is changed.
There's gazillion ways to fine-tune and improve this approach, like, only fire the macro when the chart parameters in F23 or F24 change, but that's not the point.
The point is that you can use a dynamic range name to show a selection of your data in a chart. Without any code, but letting Excel decide what the Y axis min and max should be. Like the chart in D26.
If you want more control over the min and max of the Y axis, use the code version and the chart in D42.
Happy to take questions.
No comments:
Post a Comment