How to return a sum in a VBA function
I am creating a function that takes a cell and a range of cells to create an array. The first four numbers of this array are used to perform some calculations. At the end, I want to return the sum of these to the cells. On the debug, I add a watch to the sum A+B+C+D and the result show up perfectly. However, it does not return the value to the cell when I call the function in the worksheet. Why is this?
Public Function getFone(temp As Long, coeffs As Range) As Double
Dim coeffArray As Variant
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
coeffArray = coeffs.Value
A = ((coeffArray(1, 1)) * (temp))
B = ((coeffArray(1, 2) / 2) * (temp * temp))
C = ((coeffArray(1, 3) / 3) * (temp * temp * temp))
D = ((coeffArray(1, 4) / 4) * (temp * temp * temp * temp))
getFone = (A + B + C + D)
End Function
excel vba excel-vba
edited Nov 16 '18 at 11:03
Pᴇʜ
24.3k63052
asked Nov 16 '18 at 1:52
Carlos Rios NavasCarlos Rios Navas
62
add a comment |
I am creating a function that takes a cell and a range of cells to create an array. The first four numbers of this array are used to perform some calculations. At the end, I want to return the sum of these to the cells. On the debug, I add a watch to the sum A+B+C+D and the result show up perfectly. However, it does not return the value to the cell when I call the function in the worksheet. Why is this?
Public Function getFone(temp As Long, coeffs As Range) As Double
Dim coeffArray As Variant
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
coeffArray = coeffs.Value
A = ((coeffArray(1, 1)) * (temp))
B = ((coeffArray(1, 2) / 2) * (temp * temp))
C = ((coeffArray(1, 3) / 3) * (temp * temp * temp))
D = ((coeffArray(1, 4) / 4) * (temp * temp * temp * temp))
getFone = (A + B + C + D)
End Function
excel vba excel-vba
edited Nov 16 '18 at 11:03
Pᴇʜ
24.3k63052
asked Nov 16 '18 at 1:52
Carlos Rios NavasCarlos Rios Navas
62
Consider using Application.WorksheetFunction.Sum and make sure the variables are values. You can use Val to convert from string to value
– YasserKhalil
Nov 16 '18 at 2:08
add a comment |
I am creating a function that takes a cell and a range of cells to create an array. The first four numbers of this array are used to perform some calculations. At the end, I want to return the sum of these to the cells. On the debug, I add a watch to the sum A+B+C+D and the result show up perfectly. However, it does not return the value to the cell when I call the function in the worksheet. Why is this?
Public Function getFone(temp As Long, coeffs As Range) As Double
Dim coeffArray As Variant
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
coeffArray = coeffs.Value
A = ((coeffArray(1, 1)) * (temp))
B = ((coeffArray(1, 2) / 2) * (temp * temp))
C = ((coeffArray(1, 3) / 3) * (temp * temp * temp))
D = ((coeffArray(1, 4) / 4) * (temp * temp * temp * temp))
getFone = (A + B + C + D)
End Function
excel vba excel-vba
edited Nov 16 '18 at 11:03
Pᴇʜ
24.3k63052
asked Nov 16 '18 at 1:52
Carlos Rios NavasCarlos Rios Navas
62
I am creating a function that takes a cell and a range of cells to create an array. The first four numbers of this array are used to perform some calculations. At the end, I want to return the sum of these to the cells. On the debug, I add a watch to the sum A+B+C+D and the result show up perfectly. However, it does not return the value to the cell when I call the function in the worksheet. Why is this?
Public Function getFone(temp As Long, coeffs As Range) As Double
Dim coeffArray As Variant
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
coeffArray = coeffs.Value
A = ((coeffArray(1, 1)) * (temp))
B = ((coeffArray(1, 2) / 2) * (temp * temp))
C = ((coeffArray(1, 3) / 3) * (temp * temp * temp))
D = ((coeffArray(1, 4) / 4) * (temp * temp * temp * temp))
getFone = (A + B + C + D)
End Function
excel vba excel-vba
excel vba excel-vba
edited Nov 16 '18 at 11:03
Pᴇʜ
24.3k63052
asked Nov 16 '18 at 1:52
Carlos Rios NavasCarlos Rios Navas
62
edited Nov 16 '18 at 11:03
Pᴇʜ
24.3k63052
asked Nov 16 '18 at 1:52
Carlos Rios NavasCarlos Rios Navas
62
edited Nov 16 '18 at 11:03
Pᴇʜ
24.3k63052
edited Nov 16 '18 at 11:03
Pᴇʜ
24.3k63052
edited Nov 16 '18 at 11:03
Pᴇʜ
24.3k63052
24.3k63052
asked Nov 16 '18 at 1:52
Carlos Rios NavasCarlos Rios Navas
62
asked Nov 16 '18 at 1:52
Carlos Rios NavasCarlos Rios Navas
62
asked Nov 16 '18 at 1:52
Carlos Rios NavasCarlos Rios Navas
62
62
Consider using Application.WorksheetFunction.Sum and make sure the variables are values. You can use Val to convert from string to value
– YasserKhalil
Nov 16 '18 at 2:08
add a comment |
Consider using Application.WorksheetFunction.Sum and make sure the variables are values. You can use Val to convert from string to value
– YasserKhalil
Nov 16 '18 at 2:08
Consider using Application.WorksheetFunction.Sum and make sure the variables are values. You can use Val to convert from string to value
– YasserKhalil
Nov 16 '18 at 2:08
Consider using Application.WorksheetFunction.Sum and make sure the variables are values. You can use Val to convert from string to value
– YasserKhalil
Nov 16 '18 at 2:08
add a comment |
1 Answer
1
active
oldest
votes
The code works if the range is four horizontal cells and the temp parameter and the range contain numerics. So the potential issues that might trigger a #Value result are:
- The parameters (temp and values in the range) are not numeric
- The calculated value exceeds the size of a double
- The range is not a horizontal range of four cells
answered Nov 16 '18 at 2:15
Techno DabblerTechno Dabbler
395210
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%2f53330364%2fhow-to-return-a-sum-in-a-vba-function%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
The code works if the range is four horizontal cells and the temp parameter and the range contain numerics. So the potential issues that might trigger a #Value result are:
- The parameters (temp and values in the range) are not numeric
- The calculated value exceeds the size of a double
- The range is not a horizontal range of four cells
answered Nov 16 '18 at 2:15
Techno DabblerTechno Dabbler
395210
add a comment |
The code works if the range is four horizontal cells and the temp parameter and the range contain numerics. So the potential issues that might trigger a #Value result are:
- The parameters (temp and values in the range) are not numeric
- The calculated value exceeds the size of a double
- The range is not a horizontal range of four cells
answered Nov 16 '18 at 2:15
Techno DabblerTechno Dabbler
395210
add a comment |
The code works if the range is four horizontal cells and the temp parameter and the range contain numerics. So the potential issues that might trigger a #Value result are:
- The parameters (temp and values in the range) are not numeric
- The calculated value exceeds the size of a double
- The range is not a horizontal range of four cells
answered Nov 16 '18 at 2:15
Techno DabblerTechno Dabbler
395210
The code works if the range is four horizontal cells and the temp parameter and the range contain numerics. So the potential issues that might trigger a #Value result are:
- The parameters (temp and values in the range) are not numeric
- The calculated value exceeds the size of a double
- The range is not a horizontal range of four cells
answered Nov 16 '18 at 2:15
Techno DabblerTechno Dabbler
395210
answered Nov 16 '18 at 2:15
Techno DabblerTechno Dabbler
395210
answered Nov 16 '18 at 2:15
Techno DabblerTechno Dabbler
395210
answered Nov 16 '18 at 2:15
Techno DabblerTechno Dabbler
395210
395210
add a comment |
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%2f53330364%2fhow-to-return-a-sum-in-a-vba-function%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
Consider using Application.WorksheetFunction.Sum and make sure the variables are values. You can use Val to convert from string to value
– YasserKhalil
Nov 16 '18 at 2:08