I have a VBA function that outputs the trendline equation from a chart in the ActiveSheet
. I use this function as an Add-in across multiple sheets though. In order to get the function to calculate, when I first open the workbook, I hit CTRL-ALT-F9. When I do this, the function calculates for the ActiveSheet
, hence if I have used the function in multiple sheets, it is doing the calculation for whichever sheet is active, not the sheet in which the function is located.
Ideally, I want the function to refer to whichever sheet it is located in, for that discrete instance. Since it should be broadly applicable to multiple sheets, I want to get away from calling out a specific sheet name.
The current reference is: ActiveSheet.ChartObjects(1).Chart
I tried Worksheet.ChartObjects(1).Chart
, but that didn't compile.
Thank you for any help/guidance.
Full code:
Function TrendLineValue(x) As Double
Dim c As Chart
Dim t As Trendline
Dim e As String
' Get the trend line object for activesheet
Set c = ActiveSheet.ChartObjects(1).Chart
Set t = c.SeriesCollection(1).Trendlines(1)
' Display Equation
t.DisplayRSquared = False
t.DisplayEquation = True
'Number format for accuracy
t.DataLabel.NumberFormat = "0.0000E+00"
' Get equation
e = t.DataLabel.Text
' Create equation for use in cell
e = Replace(e, "y =", "")
e = Replace(e, "x6", "x^6")
e = Replace(e, "x5", "x^5")
e = Replace(e, "x4", "x^4")
e = Replace(e, "x3", "x^3")
e = Replace(e, "x2", "x^2")
e = Replace(e, "x", " * " & x & " ")
' Evaluate
TrendLineValue = Evaluate(e)
End Function
Sounds like you could use Application.Caller
. Since this is a custom function entered in a cell, Application.Caller
returns "a Range
object specifying that cell." The Parent
of that Range
is the worksheet in question.
Change
Set c = ActiveSheet.ChartObjects(1).Chart
to
Set c = Application.Caller.Parent.ChartObjects(1).Chart
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With