Function Based on Chart Data Won't Recalculate

[ericsh]

I'm using the following function to detect the size of the Y-axis in a
chart that is autoscaled by Excel:

Public Function MaxChartY()
MaxChartY =
Worksheets("CTest").ChartObjects.Item(1).Chart.Axes(xlValue,
xlPrimary).MaximumScale
End Function

Then, in a cell on the "CTest" sheet, I enter:

=MaxChartY()

When first entered, the formula correctly shows the Y-axis maximum
value, but when data for the chart is changed and the Y-axis autoscales
accordingly, my MaxChartY formula does not update.

How can I get my formula to update automatically when the Y-axis
autoscales because of a change in the data feeding the chart?

 

[Greg Wilson]

I suggest you instead paste this to the worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
With Me.ChartObjects(1).Chart
Range("C25").Value = .Axes(xlValue, xlPrimary).MaximumScale
End With
End Sub

Functions (including UDFs) only update when cell values listed as arguments
to the function change. Your formula does not directly reference any cell
value but references the chart instead. If you use the statement

Application.Volatile True

within the UDF code structure then it will update whenever ANY cells change.
However, the function unfortunately updates before the chart and
consequentially is not in sync with the chart and returns the chart's old
y-axis maximum scale value. For example, the following will return the old
maximum scale value. Do not run it at the same time as the above
Worksheet_Change event code or it will conflict:

Function MaxChartY()
Application.Volatile True
MaxChartY = ActiveSheet.ChartObjects(1).Chart. _
Axes(xlValue, xlPrimary).MaximumScale
End Function

Regards,
Greg