Cannot write to cell

[smhodge]

This is totally baffling me, and should be incredibly simple. I have this test function:

Function TestFunction(arg)
Range("Q17").Value = arg ' Does not work
TestFunction = arg
End Function

If I execute it in a cell with the "Range" line commented out it works fine and places "arg" into the cell where the function was executed from. However, if I uncomment the "Range" line (as shown), "arg" does not get placed in cell "Q17" and the function returns with #VALUE ! in the cell where the function was executed from.

What I am missing here? Thanks

 

[Marcelo Branco]

Welcome to Mr Excel

User Defined Functions have some limitations. Among them: cannot change another cell's value.

Take a loot at:
https://support.microsoft.com/en-nz...n-of-limitations-of-custom-functions-in-excel

M.

 

[Marcelo Branco]

To complement my previous post
Observe in the Summary of the article (link above) :
"... when called by a formula in a worksheet cell."

M.

 

[smhodge]

Thanks. Is the work around to simply make it a subroutine and then call that from the function?

 

[Marcelo Branco]

Thanks. Is the work around to simply make it a subroutine and then call that from the function?

We need more details.
What exactly are you trying to do?
Why use a function to change the value of cell Q17 instead of doing this in a Sub?

M.

 

[Jonmo1]

Thanks. Is the work around to simply make it a subroutine and then call that from the function?

There is no workaround.

A formula written in A1 (whether using standard functions or UDFs) can ONLY return a value to A1. It CANNOT modify any other cell.

 

[Marcelo Branco]

There is no workaround.

A formula written in A1 (whether using standard functions or UDFs) can ONLY return a value to A1. It CANNOT modify any other cell.

Perfect!

M.