Excel VBA referencing local sheet name
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
excel vba
asked Nov 27 '18 at 21:55
JDugdaleJDugdale
234
add a comment |
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
excel vba
asked Nov 27 '18 at 21:55
JDugdaleJDugdale
234
Don't useActiveSheet. Refer to the specific worksheet, either with a loop (For each ws in ThisWorkbook.Worksheets // ws.ChartObjects(1).Chart ...do whatever... // end With) or justWorksheets("SheetName").ChartObjects().... It's hard to be more specific, without a Minimal, Complete, and Verifiable example.
– 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
add a comment |
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
excel vba
asked Nov 27 '18 at 21:55
JDugdaleJDugdale
234
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
excel vba
excel vba
asked Nov 27 '18 at 21:55
JDugdaleJDugdale
234
asked Nov 27 '18 at 21:55
JDugdaleJDugdale
234
edited Nov 27 '18 at 22:16
JDugdale
asked Nov 27 '18 at 21:55
JDugdaleJDugdale
234
asked Nov 27 '18 at 21:55
JDugdaleJDugdale
234
asked Nov 27 '18 at 21:55
JDugdaleJDugdale
234
234
Don't useActiveSheet. Refer to the specific worksheet, either with a loop (For each ws in ThisWorkbook.Worksheets // ws.ChartObjects(1).Chart ...do whatever... // end With) or justWorksheets("SheetName").ChartObjects().... It's hard to be more specific, without a Minimal, Complete, and Verifiable example.
– 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
add a comment |
Don't useActiveSheet. Refer to the specific worksheet, either with a loop (For each ws in ThisWorkbook.Worksheets // ws.ChartObjects(1).Chart ...do whatever... // end With) or justWorksheets("SheetName").ChartObjects().... It's hard to be more specific, without a Minimal, Complete, and Verifiable example.
– 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
Don't use
ActiveSheet. 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 Minimal, Complete, and Verifiable example.– BruceWayne
Nov 27 '18 at 22:03
Don't use
ActiveSheet. 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 Minimal, Complete, and Verifiable example.– 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
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
add a comment |
1 Answer
1
active
oldest
votes
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
answered Nov 27 '18 at 22:36
BigBenBigBen
6,4522619
Beautiful and perfect!
– JDugdale
Nov 27 '18 at 23:14
Great! Glad to help.
– BigBen
Nov 27 '18 at 23:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53508808%2fexcel-vba-referencing-local-sheet-name%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 27 '18 at 22:36
BigBenBigBen
6,4522619
Beautiful and perfect!
– JDugdale
Nov 27 '18 at 23:14
Great! Glad to help.
– BigBen
Nov 27 '18 at 23:14
add a comment |
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
answered Nov 27 '18 at 22:36
BigBenBigBen
6,4522619
Beautiful and perfect!
– JDugdale
Nov 27 '18 at 23:14
Great! Glad to help.
– BigBen
Nov 27 '18 at 23:14
add a comment |
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
answered Nov 27 '18 at 22:36
BigBenBigBen
6,4522619
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
answered Nov 27 '18 at 22:36
BigBenBigBen
6,4522619
answered Nov 27 '18 at 22:36
BigBenBigBen
6,4522619
answered Nov 27 '18 at 22:36
BigBenBigBen
6,4522619
answered Nov 27 '18 at 22:36
BigBenBigBen
6,4522619
6,4522619
Beautiful and perfect!
– JDugdale
Nov 27 '18 at 23:14
Great! Glad to help.
– BigBen
Nov 27 '18 at 23:14
add a comment |
Beautiful and perfect!
– JDugdale
Nov 27 '18 at 23:14
Great! Glad to help.
– BigBen
Nov 27 '18 at 23:14
Beautiful and perfect!
– JDugdale
Nov 27 '18 at 23:14
Beautiful and perfect!
– JDugdale
Nov 27 '18 at 23:14
Great! Glad to help.
– BigBen
Nov 27 '18 at 23:14
Great! Glad to help.
– BigBen
Nov 27 '18 at 23:14
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53508808%2fexcel-vba-referencing-local-sheet-name%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Don't use
ActiveSheet. Refer to the specific worksheet, either with a loop (For each ws in ThisWorkbook.Worksheets // ws.ChartObjects(1).Chart ...do whatever... // end With) or justWorksheets("SheetName").ChartObjects().... It's hard to be more specific, without a Minimal, Complete, and Verifiable example.– 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