VBA: Creating cell comment within user defined function

Question

I have a UDF that makes a calculation based on a bunch of input variables. I am trying to make the UDF create a cell comment that contains information about those variables, but I am just getting the #VALUE! error.

This is what I am trying to do:

Function profit(income As Single, loss As Single) As Single
Dim cell As Range
cell = ActiveCell

profit = income - loss

call create_comment(cell, income, loss)

End function

Calling the sub:

Private Sub create_comment(cell As Range, income As Single, loss As Single)

cell.ClearComments
cell.Addcomment "income =" & income & "loss =" & loss

End Sub

All help appreciated!

Have a look at the limitations of an UDF. And then you can have a look at It can be done. But if you do that then you are probably on your own :-) — Storax
– Storax, Commented Oct 18, 2018 at 9:05
Thanks, I have. I am unsure whether creating a cell comment counts a formatting. If it doesn't, it should be possible. — Peter Nicolai Knudsen
– Peter Nicolai Knudsen, Commented Oct 18, 2018 at 9:09
Comments are no formatting but you can also not add a shape with a UDF. — Storax
– Storax, Commented Oct 18, 2018 at 9:11
Note that I am trying to add the comment to the same cell as the function is in. I am not trying to change the value of another cell. Thanks — Peter Nicolai Knudsen
– Peter Nicolai Knudsen, Commented Oct 18, 2018 at 9:13
I have to correct my comment from above. You cann indeed add a shape with a UDF. I did not expect that. — Storax
– Storax, Commented Oct 18, 2018 at 9:14

Storax · Accepted Answer · 2018-10-18 09:20:02Z

0

I changed your function like that

Function profit(income As Single, loss As Single, cell As Range) As Single
'Dim cell As Range
'cell = ActiveCell

profit = income - loss

Call create_comment(cell, income, loss)

End Function

And you enter the active cell in the call of the function like that

=profit(1000,10,H10)

answered Oct 18, 2018 at 9:20

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter Nicolai Knudsen Over a year ago

Did this work out for you? I am now getting the #NAME? error

Storax Over a year ago

Yes, this worked for me. Maybe you just add the code to an empty sheet or press F9 to re-calculate. I work with Office/Excel 365.

Peter Nicolai Knudsen Over a year ago

Thanks!! You are a hero!!

Storax Over a year ago

Glad I could help. I also learned something ;-) Please mark the question as answered.

Collectives™ on Stack Overflow

VBA: Creating cell comment within user defined function

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related