Excel VBA referencing local sheet name

Question

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

[Don't use `ActiveSheet`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Refer to the specific worksheet, either with a loop (`For each ws in ThisWorkbook.Worksheets // ws.ChartObjects(1).Chart ...do whatever... // end With`) or just `Worksheets("SheetName").ChartObjects()...`. It's hard to be more specific, without a [mcve]. — BruceWayne, Nov 27 '18 at 22:03
BruceWayne Thank you, but I don't think either of those will be ideal. Ideally, I just want to enter the function in a cell and have it calculate the value for the chart in that specific sheet. I will update the post to include the full code. — JDugdale, Nov 27 '18 at 22:12

score 9 · Accepted Answer · answered Nov 27 '18 at 22:36

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

Excel VBA referencing local sheet name

1 Answers1