write value to a range in a user defined function

[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!

 

[SkipVought]

Hi,

A function returns one value to a cell, and ONLY the cell in which the function resides. It cannot return a value to any other range. Your function bombs on the COMMENT statement.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[electricpete]

It is true a user defined function cannot alter a spreadsheet except to return a value into the cell(s) in which the row is entered.

I am pretty sure that user-defined functions can be set up to return values into more than one cell... you would need to enter the function as an array function (control-shift-enter)

 

[electricpete]

sorry, I was typing without a brain..
"....in which the row is entered"
should have been
"....in which the function is entered"

 

[combo]

You have limited possibilities to change worksheet outside cell with UDF. In fact, you cannot add comment, but you can change its text if comment exists:

Function SetComment(rComment As Range, sComment As String)
rComment.Comment.Text Text:=sComment
End Function

Moreover, you can change shape:

Function SetShape(rComment As Range, iCommentShape As Integer)
rComment.Comment.Shape.AutoShapeType = iCommentShape
End Function

,
change colour:

Function SetColour(rComment As Range, iColorShape As Integer)
rComment.Comment.Shape.Fill.ForeColor.SchemeColor = iColorShape
End Function

or change visibility (best for regular shapes):

Function SetVisible(rComment As Range, bVisible As Boolean)
rComment.Comment.Shape.Visible = bVisible
End Function

In case of regular shapes they can be called by name or index.


combo

 

[electricpete]

Here is function which appears to accomplish your taks, by using an array funciton. It is a little cumbersome to enter. (highight two cells, type =arrayfn(0) and press ctrl-shift-enter)

Option Base 1
Function addonearrayfn(myinteger As Integer)
Dim temp(2, 1) As Variant
temp(1, 1) = "Add One:"
temp(2, 1) = myinteger + 1
addonearrayfn = temp
' To use this function:
' highlight 2 cells, type =arrayfn(0) and press ctrl-shift-enter
End Function

 

[electricpete]

Correction:
To use this function:
highight two cells (one above the other), type =addonearrayfn(0) and press ctrl-shift-enter

 

[tt9527]

Hi All,

Thanks for response.

electricpete's code is working fine. A slight issue is when the upper cell is not empty, the array function will delete the value in upper cell.

Combo, thanks for sharing the comment properties. The "AddComment" is actually working fine in my code. The only problem is this part: Range(Application.Caller.Address).Offset(-1, 0).Value = "AddOne:"

I also try to use two functions. I tried calling one function in another function, but also failed. Still trying...Coding can be hard for new starters.

 

[combo]

As Skip wrote, UDF cannot change cell other than it resides in. Most of my code works for shapes (a part of comment is shape), you only need to change path (Worksheet.Shape.ShapeProperties).

combo

 

[electricpete]

As Skip wrote, UDF cannot change cell other than it resides in.

Just to clarify: What you said is true, BUT, a UDF can reside in more than one cell (array function) and so can change more than one cell.