Thursday 20 December 2018

Is there a way to derive the chart from an axis in Excel VBA?

I have a number of generic VBA macros for manipulating Excel charts (e.g. overlaying one chart on top of the other; rescaling the axes; or adding a custom curve such as "y = x^2 - 1" to a chart by typing the formula in a text box). These macros are subs which don't take any argument, I store them in a .xlam add-in and run them from linked buttons on the Excel ribbon. In order to run the macro on a specific chart, you select the chart and then click the button on the ribbon.




In order for the macros to know which chart they are operating on, I have a function like this:



Function chart_from_selection() As Chart

If TypeName(Selection) = "ChartArea" Or TypeName(Selection) = "PlotArea" Then
Set chart_from_selection = Selection.Parent
ElseIf TypeName(Selection) = "Series" Then
Set chart_from_selection = Selection.Parent.Parent
Else
MsgBox ("Select a chart!")

End If

End Function


So the first couple of lines in each macro are



Dim cht As Chart
Set cht = chart_from_selection()



and the macro identifies the chart whether you have selected its chart area, plot area or one of its series.



I would also like it to work if you have selected one of the chart axes, but the problem is that the parent of the axis object is the worksheet not the chart. Does anyone know how to derive the chart object itself from one of its axes? The only way I can think of is by recording the position of the axis and then comparing it against the positions of all the charts in the worksheet until you find and overlap, but that seems quite convoluted and I'm wondering if I'm overlooking a simpler way ...

No comments:

Post a Comment

php - file_get_contents shows unexpected output while reading a file

I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...