function to modify other cells

[rat59man]

I am trying to write a function to modify a group of cells. As an example in
cell C1 I insert "=myfunction(1)" and in cells A1, B1, D1, E1 I want the
values of 3, 5, 7, and 11 placed.

At the assignment I get a 1004 error number returned.

My thought is that functions are not allowed to modify cells directly. Is
this correct?

 

[Chip Pearson]

A function called from a worksheet cell cannot change the value of any
other cell. It can only return a value to the cell(s) from which it
was called. The reason for this limitation is that Excel must keep
track of which cells are dependent on which other cells so it can
calculate in the proper order. Excel can't determine what VBA code
might do, so it disallows code from changing other cells.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jim Thomlinson]

Just a quick note. Even if you could write a function that modified other
cell it would be a really bad idea. Lets assume that we could. In Cell A1 you
have the value 3. Did someone input that value there? Did a function put it
there? If so which function? If 2 functions can modify the same cell which
one put a value there last? Any spreadsheet with those types of functions
would be impossible to debug.

 

[PBezucha]

Another quick note. Your function can, of course, "modify" (better to say
"put new values" to) a contigues range of cells. This is a way called "array
function" – find out the Help for it. In your case you would have to use your
wit with it, i.e. to place the argument at the end – to E1 cell instead of
C1, or to create a pair of functions - for the left and right ranges
alongside the argument cell. The best way, with the same function algorithm –
as I take it from your example - is to propagate "one cell" function by
copying. When even this is out of your intentions, the only resting method is
creating a subroutine. By means of it you can do almost everything; you must
resign yourself only to the instant response to the argument change that the
function provides.

Regards