1

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!

Peter Nicolai Knudsen
  • 37
  • 5
  • Have a look at the [limitations](https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel) of an UDF. And then you can have a look at _[It can be done](https://stackoverflow.com/questions/9476282/cannot-vba-write-data-to-cells-in-excel-2007-2010-within-a-function)_. But if you do that then you are probably on your own :-) – Storax Oct 18 '18 at 09: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 Oct 18 '18 at 09:09
  • Comments are no formatting but you can also not add a shape with a UDF. – Storax Oct 18 '18 at 09: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 Oct 18 '18 at 09:13
  • I have to correct my comment from above. You cann indeed add a shape with a UDF. I did not expect that. – Storax Oct 18 '18 at 09:14
  • Thanks! I suspect excel interpret the "call sub" as formatting or altering of other cells. – Peter Nicolai Knudsen Oct 18 '18 at 09:20
  • I posted an answer. I did not expect that you could add a shape resp. comment via an UDF. – Storax Oct 18 '18 at 09:21

1 Answers1

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)
Storax
  • 11,158
  • 3
  • 16
  • 33