Somewhere in a redesign of my project I decided that I wanted to chang
all cell values at once. Didn't work. Simplifying led me to th
following problem:

New workbook, new function:


Code
-------------------
Function testThisThen()

Worksheets("sheets1").Range("B5").Value = 20

end Functio
-------------------

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 th
.Value = 20 statement.
This code:

Code
-------------------
Function testThisThen()

On Error Resume Next
Worksheets("sheets1").Range("B5").Value = 20
testThisThen = 12

end Functio
-------------------

will change the value of the cell which calls the function to 12, bu
won't change the value of B5.

Is it possible to change the value of a cell this way? I require som
additional arguments to be used in the function, hence the desire t
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 m
apologies if this has been answered once already

--
Danybo
-----------------------------------------------------------------------
Danyboy's Profile: http://www.excelforum.com/member.php...fo&userid=2609
View this thread: http://www.excelforum.com/showthread.php?threadid=39422

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

Is it possible to change the value of a cell this way?

No. A UDF (a call to a VB function from a worksheet formula) can only
return a value to the cell it is in. It cannot affect other cells, changes
formats, print or go get pizza (shame)!


--
Jim
"Danyboy" wrote in
message ...
|
| 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
|