Is it possible to update cells from within a function? I know you can read from them but updating gives the following (really unhelpful) error:

Run-time error '1004': Application-defined or object-defined error

Here is the code for the function:

Public Function Test() As Integer
Range("A2").Value = "Hello"
End Function

The formula from the cell I am calling it from is "=Test()".

NOTE: The error message does not actually pop up when run. You get #VALUE! in the calling cell and the value Hello is not written. What I did for the error was put a break-point on the Range line, then execute the line in the Immediate window.

Jason,

With functions, you can only read in information and write back an answer.
You cannot change cells (other than writing back an answer), change display
settings or whatever else. You were attempting to change A2. If you are in
A3 and using a function, you cannot change A2. You can only write an answer
to A3.

If you code was like this....

Public Function Test() As String
Test = "Hello"
End Function

Then you could write in any cell =Test() and get back "Hello".

Note that the function is a STRING and not an INTEGER (see yours below) as
"Hello" is a string.

Regards,
Kevin




"Jason Callas" wrote in message
...
Is it possible to update cells from within a function? I know you can read
from them but updating gives the following (really unhelpful) error:

Run-time error '1004': Application-defined or object-defined error

Here is the code for the function:

Public Function Test() As Integer
Range("A2").Value = "Hello"
End Function

The formula from the cell I am calling it from is "=Test()".

NOTE: The error message does not actually pop up when run. You get #VALUE!
in the calling cell and the value Hello is not written. What I did for the
error was put a break-point on the Range line, then execute the line in the
Immediate window.

Hi Jason:

A worksheet function can only return a value to the calling cell, it cannot
modify any other cells.

Regards,

Vasant.

"Jason Callas" wrote in message
...
Is it possible to update cells from within a function? I know you can read
from them but updating gives the following (really unhelpful) error:

Run-time error '1004': Application-defined or object-defined error

Here is the code for the function:

Public Function Test() As Integer
Range("A2").Value = "Hello"
End Function

The formula from the cell I am calling it from is "=Test()".

NOTE: The error message does not actually pop up when run. You get #VALUE!
in the calling cell and the value Hello is not written. What I did for the
error was put a break-point on the Range line, then execute the line in the
Immediate window.

I was the answer I was thinking. Just wanted a confirmation. Thanks.

- Jason

"Kevin Stecyk" wrote in message
...
Jason,

With functions, you can only read in information and write back an answer.
You cannot change cells (other than writing back an answer), change
display
settings or whatever else. You were attempting to change A2. If you are
in
A3 and using a function, you cannot change A2. You can only write an
answer
to A3.

If you code was like this....

Public Function Test() As String
Test = "Hello"
End Function

Then you could write in any cell =Test() and get back "Hello".

Note that the function is a STRING and not an INTEGER (see yours below) as
"Hello" is a string.

Regards,
Kevin




"Jason Callas" wrote in message
...
Is it possible to update cells from within a function? I know you can read
from them but updating gives the following (really unhelpful) error:

Run-time error '1004': Application-defined or object-defined error

Here is the code for the function:

Public Function Test() As Integer
Range("A2").Value = "Hello"
End Function

The formula from the cell I am calling it from is "=Test()".

NOTE: The error message does not actually pop up when run. You get #VALUE!
in the calling cell and the value Hello is not written. What I did for the
error was put a break-point on the Range line, then execute the line in
the
Immediate window.