Can't set cell value with VBA

[Danyboy]

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

 

[Guest]

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.

 

[Jim Rech]

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
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?action=getinfo&userid=26093
| View this thread: http://www.excelforum.com/showthread.php?threadid=394221
|

 

[Danyboy]

Thanks, Jim!

This helped me off the wrong track, now I just have to figure out th
right way of getting the results I want... a good puzzle for this nigh

 

[STEVE BELL]

Check into Event macros (behind the individual worksheets, or in
ThisWorkBook module)

 

[Danyboy]

Thanks for the tip.
I might use a normal macro though; the calculation takes some time s
I'd rather not use a worksheet-change event or something like that. I
seems to me that it would make using the workbook bothersome