Insert comment via Function

Question

How to insert a comment with the text of a cell through a Function?

Answer 1

Code of the UDF

This is the code of the function to add comment and optionally, calculates something in the cell.

Option Explicit
Public Function AdicionarComentario(celula As Range, Optional Calcs)
    On Error GoTo ErrHandler:
    If TypeOf Application.Caller Is Range Then
        Dim Caller As Range
        Set Caller = Application.Caller
        Caller.ClearComments
        Caller.AddComment CStr(celula.Value)
    End If
    If Not IsMissing(Calcs) Then
        AdicionarComentario = Calcs
    Else
        AdicionarComentario = ""
    End If
    Exit Function
ErrHandler:
    ' error handling code
    AdicionarComentario = CVErr(xlErrNA)
    On Error GoTo 0
End Function

Upshot

Inserting the formula =AdicionarComentario(D1) in E1, adds a comment with the cell text D1 in the cell E1.

Inserting the formula =AdicionarComentario(D2;SOMA(E3;E4)) in E2, adds a comment with the cell text D2 in the cell E2. And uses the optional field with SOMA(E3;E4), where it performs the sum of E3+E4 and returns the value of this calculation.

Optional - Description

The description can be added by running this code once.

Sub DescreverFunction()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 2) As String

   FuncName = "AdicionarComentario"
   FuncDesc = "Adiciona comentário na célula e cálculos opcionalmente."
   Category = 14
   ArgDesc(1) = "Célula com o texto do comentário"
   ArgDesc(2) = "Cálculo realizado na célula"

   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub

With this, the window to insert the arguments of the function will have the description to facilitate the user’s life.