Functions primarily return values. Anything that they change are essentially
side effect (which in
VB you normally want to avoid). That being said if you
call the function from within code then the line
Worksheets("sheets1").Range("B5").Value = 20
will change the value of B5. Called directly from a worksheet however the
value of B5 will not change. Side effects are not permitted in functions
called directly from a worksheet. In the grander scheme of things this is a
good thing as debugging a spreadsheet would be near impossible if side
effects were permitted.
To do something like that you need to place the code in a Sub and call the
procedure using either direct interaction from the user of using an event
procedure.
--
HTH...
Jim Thomlinson
"Danyboy" wrote:
Somewhere in a redesign of my project I decided that I wanted to change
all cell values at once. Didn't work. Simplifying led me to the
following problem:
New workbook, new function:
Code:
--------------------
Function testThisThen()
Worksheets("sheets1").Range("B5").Value = 20
end Function
--------------------
Now I want to execute this code from a cell like so:
=testThisThen()
Won't work.
As I just tested this I discovered the 'play button' in the VB editor.
Pressing it will properly execute the code.
Calling the function from the cell, however, leads to problems with the
.Value = 20 statement.
This code:
Code:
--------------------
Function testThisThen()
On Error Resume Next
Worksheets("sheets1").Range("B5").Value = 20
testThisThen = 12
end Function
--------------------
will change the value of the cell which calls the function to 12, but
won't change the value of B5.
Is it possible to change the value of a cell this way? I require some
additional arguments to be used in the function, hence the desire to
use a formula in a cell.
I was unable to find an answer to this question; searching for '.Value'
and 'doens't work' will not give very good searchresults though, so my
apologies if this has been answered once already.
--
Danyboy
------------------------------------------------------------------------
Danyboy's Profile: http://www.excelforum.com/member.php...o&userid=26093
View this thread: http://www.excelforum.com/showthread...hreadid=394221