User Defined Function - Write value to a range

[tt9527]

Hi All,

I am new to VBA so my question can be really basic.

I need to create a user defined function in Excel that places a header in the cell above the function, if the cell above the function is empty. e.g. If you place =theFunction() is cell B1 and A1 is empty, it will write "The Function:" in A1 in addiction the result of theFunction in B1.

I wrote the following code:

Function AddOne(inValue As Integer)
AddOne = inValue + 1
Dim c As Comment
If IsObject(Application.Caller) = True Then
MsgBox Range(Application.Caller.Address).Offset(-1, 0).Address
c = Range(Application.Caller.Address).Offset(-1, 0).AddComment("Add One:")
Range(Application.Caller.Address).Offset(-1, 0).Value = "AddOne:"
End If
End Function


It returns "#value". I think the problem is with this but don't know how to fix it.

Range(Application.Caller.Address).Offset(-1, 0).Value = "AddOne:"

Can anyone shed some light? Thank you!

[RichardSchollar]

Hi

Functions entered in Excel cells are not meant to be able to modify the contents/format of any other cells (ie they are just meant to return a value to the cell in which they are enterd).

To modify this other cell, you would need to run an actual subprocedure (or perhaps a function executed from within VBA) so that you are not relying on the function within an actual worksheet cell to modify another cell).

Richard

[Bob Phillips]

Won't work. A UDF can only return a value to the cell it is in, it cannot write to another cell.

[Cyberdude]

XLD is correct. Believe me there is NO way to circumvent this rule. A function cannot write to a cell. Excel can detect even the most clever ways that you might use to get around this rule. The purpose of a function executed on a worksheet is to return a single value . . . better accept that fact, and you will sleep better.