Setting Range.Value causes #VALUE! in Excel VBA, and also resubmitting a function causes all other functions...
I apologise for how trivial this must be for VBA developers, but I have done some digging and cannot fathom why the following code, when assigned to a cell and executed, results in #VALUE!
Function foo()
Range("A1:B3").Value = 10
End Function
The excel sheet:
I initially tried
ActiveSheet.Range("A1").Value = "abc"
but that didn't work either. What very simple thing am I doing wrong here?
Also, why, when I try to re-execute the function (using F2 and then Enter), does excel resubmit other functions that are in the same worksheet? This is truly maddening. I have hit F2 and Enter, so why would Excel think that I want to resubmit all other functions, and how can this be prevented?
Thanks very much.
excel vba
asked Nov 22 '18 at 19:55
JL_SOJL_SO
313110
|
show 1 more comment
I apologise for how trivial this must be for VBA developers, but I have done some digging and cannot fathom why the following code, when assigned to a cell and executed, results in #VALUE!
Function foo()
Range("A1:B3").Value = 10
End Function
The excel sheet:
I initially tried
ActiveSheet.Range("A1").Value = "abc"
but that didn't work either. What very simple thing am I doing wrong here?
Also, why, when I try to re-execute the function (using F2 and then Enter), does excel resubmit other functions that are in the same worksheet? This is truly maddening. I have hit F2 and Enter, so why would Excel think that I want to resubmit all other functions, and how can this be prevented?
Thanks very much.
excel vba
asked Nov 22 '18 at 19:55
JL_SOJL_SO
313110
2
foois a UDF (user-defined function). UDFs take parameters and return a value to the cell that invoked it. They don't (can't) alter any other cell's value, or alter any application state. This#VALUE!error means "You're trying to make a UDF do something it's not allowed to do".
– Mathieu Guindon
Nov 22 '18 at 20:13
1
support.microsoft.com/en-ca/help/170787/…
– Mathieu Guindon
Nov 22 '18 at 20:15
I see. So how is this supposed to be done? OK I can create it as a procedure (using Sub .. End Sub) instead of a function but how then is that supposed to be invoked? I have tried to create it as a sub and then call the sub from the function but that doesn't work either (unsurprisingly). None of the examples that I've seen on the web say how this is supposed to be done. They just say eg Range("A1").Value=10 and it's supposed to just "work" but clearly it's not as simple as that. Surely I don't have to create a new macro every time I want to do something like this?
– JL_SO
Nov 22 '18 at 20:24
1
Public parameterlessSubprocedures can be attached to any shape or "forms" control, or invoked from any worksheet or workbook or ActiveX control event handler. Depends what you're actually trying to achieve.
– Mathieu Guindon
Nov 22 '18 at 20:26
1
MakefooaSubprocedure, then draw some rectangle shape; right-click it, select "attach macro", select "foo", done.
– Mathieu Guindon
Nov 22 '18 at 20:27
|
show 1 more comment
I apologise for how trivial this must be for VBA developers, but I have done some digging and cannot fathom why the following code, when assigned to a cell and executed, results in #VALUE!
Function foo()
Range("A1:B3").Value = 10
End Function
The excel sheet:
I initially tried
ActiveSheet.Range("A1").Value = "abc"
but that didn't work either. What very simple thing am I doing wrong here?
Also, why, when I try to re-execute the function (using F2 and then Enter), does excel resubmit other functions that are in the same worksheet? This is truly maddening. I have hit F2 and Enter, so why would Excel think that I want to resubmit all other functions, and how can this be prevented?
Thanks very much.
excel vba
asked Nov 22 '18 at 19:55
JL_SOJL_SO
313110
I apologise for how trivial this must be for VBA developers, but I have done some digging and cannot fathom why the following code, when assigned to a cell and executed, results in #VALUE!
Function foo()
Range("A1:B3").Value = 10
End Function
The excel sheet:
I initially tried
ActiveSheet.Range("A1").Value = "abc"
but that didn't work either. What very simple thing am I doing wrong here?
Also, why, when I try to re-execute the function (using F2 and then Enter), does excel resubmit other functions that are in the same worksheet? This is truly maddening. I have hit F2 and Enter, so why would Excel think that I want to resubmit all other functions, and how can this be prevented?
Thanks very much.
excel vba
excel vba
asked Nov 22 '18 at 19:55
JL_SOJL_SO
313110
asked Nov 22 '18 at 19:55
JL_SOJL_SO
313110
edited Nov 22 '18 at 20:08
JL_SO
asked Nov 22 '18 at 19:55
JL_SOJL_SO
313110
asked Nov 22 '18 at 19:55
JL_SOJL_SO
313110
asked Nov 22 '18 at 19:55
JL_SOJL_SO
313110
313110
2
foois a UDF (user-defined function). UDFs take parameters and return a value to the cell that invoked it. They don't (can't) alter any other cell's value, or alter any application state. This#VALUE!error means "You're trying to make a UDF do something it's not allowed to do".
– Mathieu Guindon
Nov 22 '18 at 20:13
1
support.microsoft.com/en-ca/help/170787/…
– Mathieu Guindon
Nov 22 '18 at 20:15
I see. So how is this supposed to be done? OK I can create it as a procedure (using Sub .. End Sub) instead of a function but how then is that supposed to be invoked? I have tried to create it as a sub and then call the sub from the function but that doesn't work either (unsurprisingly). None of the examples that I've seen on the web say how this is supposed to be done. They just say eg Range("A1").Value=10 and it's supposed to just "work" but clearly it's not as simple as that. Surely I don't have to create a new macro every time I want to do something like this?
– JL_SO
Nov 22 '18 at 20:24
1
Public parameterlessSubprocedures can be attached to any shape or "forms" control, or invoked from any worksheet or workbook or ActiveX control event handler. Depends what you're actually trying to achieve.
– Mathieu Guindon
Nov 22 '18 at 20:26
1
MakefooaSubprocedure, then draw some rectangle shape; right-click it, select "attach macro", select "foo", done.
– Mathieu Guindon
Nov 22 '18 at 20:27
|
show 1 more comment
2
foois a UDF (user-defined function). UDFs take parameters and return a value to the cell that invoked it. They don't (can't) alter any other cell's value, or alter any application state. This#VALUE!error means "You're trying to make a UDF do something it's not allowed to do".
– Mathieu Guindon
Nov 22 '18 at 20:13
1
support.microsoft.com/en-ca/help/170787/…
– Mathieu Guindon
Nov 22 '18 at 20:15
I see. So how is this supposed to be done? OK I can create it as a procedure (using Sub .. End Sub) instead of a function but how then is that supposed to be invoked? I have tried to create it as a sub and then call the sub from the function but that doesn't work either (unsurprisingly). None of the examples that I've seen on the web say how this is supposed to be done. They just say eg Range("A1").Value=10 and it's supposed to just "work" but clearly it's not as simple as that. Surely I don't have to create a new macro every time I want to do something like this?
– JL_SO
Nov 22 '18 at 20:24
1
Public parameterlessSubprocedures can be attached to any shape or "forms" control, or invoked from any worksheet or workbook or ActiveX control event handler. Depends what you're actually trying to achieve.
– Mathieu Guindon
Nov 22 '18 at 20:26
1
MakefooaSubprocedure, then draw some rectangle shape; right-click it, select "attach macro", select "foo", done.
– Mathieu Guindon
Nov 22 '18 at 20:27
2
2
foo is a UDF (user-defined function). UDFs take parameters and return a value to the cell that invoked it. They don't (can't) alter any other cell's value, or alter any application state. This #VALUE! error means "You're trying to make a UDF do something it's not allowed to do".– Mathieu Guindon
Nov 22 '18 at 20:13
foo is a UDF (user-defined function). UDFs take parameters and return a value to the cell that invoked it. They don't (can't) alter any other cell's value, or alter any application state. This #VALUE! error means "You're trying to make a UDF do something it's not allowed to do".– Mathieu Guindon
Nov 22 '18 at 20:13
1
1
support.microsoft.com/en-ca/help/170787/…
– Mathieu Guindon
Nov 22 '18 at 20:15
support.microsoft.com/en-ca/help/170787/…
– Mathieu Guindon
Nov 22 '18 at 20:15
I see. So how is this supposed to be done? OK I can create it as a procedure (using Sub .. End Sub) instead of a function but how then is that supposed to be invoked? I have tried to create it as a sub and then call the sub from the function but that doesn't work either (unsurprisingly). None of the examples that I've seen on the web say how this is supposed to be done. They just say eg Range("A1").Value=10 and it's supposed to just "work" but clearly it's not as simple as that. Surely I don't have to create a new macro every time I want to do something like this?
– JL_SO
Nov 22 '18 at 20:24
I see. So how is this supposed to be done? OK I can create it as a procedure (using Sub .. End Sub) instead of a function but how then is that supposed to be invoked? I have tried to create it as a sub and then call the sub from the function but that doesn't work either (unsurprisingly). None of the examples that I've seen on the web say how this is supposed to be done. They just say eg Range("A1").Value=10 and it's supposed to just "work" but clearly it's not as simple as that. Surely I don't have to create a new macro every time I want to do something like this?
– JL_SO
Nov 22 '18 at 20:24
1
1
Public parameterless
Sub procedures can be attached to any shape or "forms" control, or invoked from any worksheet or workbook or ActiveX control event handler. Depends what you're actually trying to achieve.– Mathieu Guindon
Nov 22 '18 at 20:26
Public parameterless
Sub procedures can be attached to any shape or "forms" control, or invoked from any worksheet or workbook or ActiveX control event handler. Depends what you're actually trying to achieve.– Mathieu Guindon
Nov 22 '18 at 20:26
1
1
Make
foo a Sub procedure, then draw some rectangle shape; right-click it, select "attach macro", select "foo", done.– Mathieu Guindon
Nov 22 '18 at 20:27
Make
foo a Sub procedure, then draw some rectangle shape; right-click it, select "attach macro", select "foo", done.– Mathieu Guindon
Nov 22 '18 at 20:27
|
show 1 more comment
1 Answer
1
active
oldest
votes
Edit: You tried to use UDF for changing some fields and consistent about the use of some kind of event modification instead of firing your code from a button in your comments with @Mathieu Guindon. Since UDF fire the code only when some cells are recalculated, it is assumed that your requirement is to fire the code when some cells in the sheet get changed either manually or through some formula. The solutions offered below run the code when value of cell F1 change. You may please modify it according to your need.
Old Post: It is not a clean way but may be a called dirty workaround idea. Supposing you want to any change in cell F1 value to execute the code, may try
Public F1Val As Variant
Private Sub Worksheet_Activate()
F1Val = Range("F1").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If F1Val <> Range("F1").Value Then
Range("A1:B3").Value = 10
F1Val = Range("F1").Value
End If
End Sub
Caution:Certainly it will backfire if cell F1 is linked with formula with the cells getting changed.
Alternately if only manual change of cell F1 is suffice for code to execute then may simply try
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
Range("A1:B3").Value = 10
End If
End Sub
answered Nov 23 '18 at 4:01
Ahmed AUAhmed AU
77028
Hi, I'm not sure I understand it, but thank you.
– JL_SO
Nov 23 '18 at 21:37
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%2f53437364%2fsetting-range-value-causes-value-in-excel-vba-and-also-resubmitting-a-functio%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
Edit: You tried to use UDF for changing some fields and consistent about the use of some kind of event modification instead of firing your code from a button in your comments with @Mathieu Guindon. Since UDF fire the code only when some cells are recalculated, it is assumed that your requirement is to fire the code when some cells in the sheet get changed either manually or through some formula. The solutions offered below run the code when value of cell F1 change. You may please modify it according to your need.
Old Post: It is not a clean way but may be a called dirty workaround idea. Supposing you want to any change in cell F1 value to execute the code, may try
Public F1Val As Variant
Private Sub Worksheet_Activate()
F1Val = Range("F1").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If F1Val <> Range("F1").Value Then
Range("A1:B3").Value = 10
F1Val = Range("F1").Value
End If
End Sub
Caution:Certainly it will backfire if cell F1 is linked with formula with the cells getting changed.
Alternately if only manual change of cell F1 is suffice for code to execute then may simply try
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
Range("A1:B3").Value = 10
End If
End Sub
answered Nov 23 '18 at 4:01
Ahmed AUAhmed AU
77028
Hi, I'm not sure I understand it, but thank you.
– JL_SO
Nov 23 '18 at 21:37
add a comment |
Edit: You tried to use UDF for changing some fields and consistent about the use of some kind of event modification instead of firing your code from a button in your comments with @Mathieu Guindon. Since UDF fire the code only when some cells are recalculated, it is assumed that your requirement is to fire the code when some cells in the sheet get changed either manually or through some formula. The solutions offered below run the code when value of cell F1 change. You may please modify it according to your need.
Old Post: It is not a clean way but may be a called dirty workaround idea. Supposing you want to any change in cell F1 value to execute the code, may try
Public F1Val As Variant
Private Sub Worksheet_Activate()
F1Val = Range("F1").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If F1Val <> Range("F1").Value Then
Range("A1:B3").Value = 10
F1Val = Range("F1").Value
End If
End Sub
Caution:Certainly it will backfire if cell F1 is linked with formula with the cells getting changed.
Alternately if only manual change of cell F1 is suffice for code to execute then may simply try
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
Range("A1:B3").Value = 10
End If
End Sub
answered Nov 23 '18 at 4:01
Ahmed AUAhmed AU
77028
Hi, I'm not sure I understand it, but thank you.
– JL_SO
Nov 23 '18 at 21:37
add a comment |
Edit: You tried to use UDF for changing some fields and consistent about the use of some kind of event modification instead of firing your code from a button in your comments with @Mathieu Guindon. Since UDF fire the code only when some cells are recalculated, it is assumed that your requirement is to fire the code when some cells in the sheet get changed either manually or through some formula. The solutions offered below run the code when value of cell F1 change. You may please modify it according to your need.
Old Post: It is not a clean way but may be a called dirty workaround idea. Supposing you want to any change in cell F1 value to execute the code, may try
Public F1Val As Variant
Private Sub Worksheet_Activate()
F1Val = Range("F1").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If F1Val <> Range("F1").Value Then
Range("A1:B3").Value = 10
F1Val = Range("F1").Value
End If
End Sub
Caution:Certainly it will backfire if cell F1 is linked with formula with the cells getting changed.
Alternately if only manual change of cell F1 is suffice for code to execute then may simply try
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
Range("A1:B3").Value = 10
End If
End Sub
answered Nov 23 '18 at 4:01
Ahmed AUAhmed AU
77028
Edit: You tried to use UDF for changing some fields and consistent about the use of some kind of event modification instead of firing your code from a button in your comments with @Mathieu Guindon. Since UDF fire the code only when some cells are recalculated, it is assumed that your requirement is to fire the code when some cells in the sheet get changed either manually or through some formula. The solutions offered below run the code when value of cell F1 change. You may please modify it according to your need.
Old Post: It is not a clean way but may be a called dirty workaround idea. Supposing you want to any change in cell F1 value to execute the code, may try
Public F1Val As Variant
Private Sub Worksheet_Activate()
F1Val = Range("F1").Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If F1Val <> Range("F1").Value Then
Range("A1:B3").Value = 10
F1Val = Range("F1").Value
End If
End Sub
Caution:Certainly it will backfire if cell F1 is linked with formula with the cells getting changed.
Alternately if only manual change of cell F1 is suffice for code to execute then may simply try
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
Range("A1:B3").Value = 10
End If
End Sub
answered Nov 23 '18 at 4:01
Ahmed AUAhmed AU
77028
edited Nov 24 '18 at 1:10
answered Nov 23 '18 at 4:01
Ahmed AUAhmed AU
77028
answered Nov 23 '18 at 4:01
Ahmed AUAhmed AU
77028
answered Nov 23 '18 at 4:01
Ahmed AUAhmed AU
77028
77028
Hi, I'm not sure I understand it, but thank you.
– JL_SO
Nov 23 '18 at 21:37
add a comment |
Hi, I'm not sure I understand it, but thank you.
– JL_SO
Nov 23 '18 at 21:37
Hi, I'm not sure I understand it, but thank you.
– JL_SO
Nov 23 '18 at 21:37
Hi, I'm not sure I understand it, but thank you.
– JL_SO
Nov 23 '18 at 21:37
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%2f53437364%2fsetting-range-value-causes-value-in-excel-vba-and-also-resubmitting-a-functio%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
2
foois a UDF (user-defined function). UDFs take parameters and return a value to the cell that invoked it. They don't (can't) alter any other cell's value, or alter any application state. This#VALUE!error means "You're trying to make a UDF do something it's not allowed to do".– Mathieu Guindon
Nov 22 '18 at 20:13
1
support.microsoft.com/en-ca/help/170787/…
– Mathieu Guindon
Nov 22 '18 at 20:15
I see. So how is this supposed to be done? OK I can create it as a procedure (using Sub .. End Sub) instead of a function but how then is that supposed to be invoked? I have tried to create it as a sub and then call the sub from the function but that doesn't work either (unsurprisingly). None of the examples that I've seen on the web say how this is supposed to be done. They just say eg Range("A1").Value=10 and it's supposed to just "work" but clearly it's not as simple as that. Surely I don't have to create a new macro every time I want to do something like this?
– JL_SO
Nov 22 '18 at 20:24
1
Public parameterless
Subprocedures can be attached to any shape or "forms" control, or invoked from any worksheet or workbook or ActiveX control event handler. Depends what you're actually trying to achieve.– Mathieu Guindon
Nov 22 '18 at 20:26
1
Make
fooaSubprocedure, then draw some rectangle shape; right-click it, select "attach macro", select "foo", done.– Mathieu Guindon
Nov 22 '18 at 20:27