problem with active cell

[none]

I have written a function in VBA, Excel 2003.
In the spreadsheet I have placed it in the cell via the formula bar text
e.g. "=dostuff()"

The function works and is called whenever the cell changes or when the
spreadsheet is opened. This is great.

But if I want the function to change the contents of another cell, i.e.
not the cell that called the function then this results in an
Application defined error or object defined error.

I believe the problem may be that I can not change the active cell from
the cell that has called this function.

In summary, I have placed the function "=dostuff() in cell "A1"
I want the function to return an integer (no problem it does this)
I also want it to enter an integer value into cell "D2"

Is there a way of doing this?

Thank you for your help.

john

 

[Guest]

Hi John.
Your post is not much clear, however try to insert in your UDF:
Range("D2").Value = your integer value
Regards
Eliano

 

[Dave Peterson]

UDF's placed in a cell can return values to that same cell.

They can't change the value of other cells.

 

[Guest]

no

A function can only change the value of the cell into which it is entered.

 

[Luca Brasi]

you might want to put the udf in any "hidden" cell, adjust the vba code
so that both integer values are returned as a combined string (insert a
separator character between the two values), insert in the cells A1 and
D2 formulas to return the first/second part of the combined string and
finally convert them back to integers.