Microsoft Excel: How to Comment Cells

Introduction

This text has been written when the guy at the next desk asked me to help him comment some Excel cells that contain data which cannot be automatically filled with the right values. This guy knew something about VBA and he managed to write some code which finds these wrong cells by himself, but he couldn't comment these cells.

Background

All you need is Microsoft Office Excel with development feature installed.

Using the Code

This code consists of two parts:

1. Number converter
2. Comment setter

You can copy number converter in your code, but you have to copy all the code if you want to use comment setter. This code can also be saved as a standalone file, which can be loaded by using "Import module" command from VBA file menu, whenever you need it. Do not copy the first line into your code. Here it is:

Attribute VB_Name = "xlUtilComments"
Const sLetters As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Function col2letter(ByVal colNo As Long) As String
Dim s1 As String
Dim m As Long
Dim i As Long

    s1 = ""
    m = Len(sLetters)
    i = colNo
    While i > 0
        n = i Mod m ' the remainder of the division by
        i = i \ m ' integer division
        If n = 0 Then ' the remainder of the division is 0
            n = 26 ' using last letter instead
            If i = 1 Then ' the quotient of dividing is 1
                i = 0 ' stop dividing
            End If
        End If
        s1 = Mid(sLetters, n, 1) + s1
    Wend
    col2letter = s1
End Function

Sub addComment(ByRef ws As Worksheet, lRow As Long, lCol As Long, sComment As String)
Dim s1 As String
Dim s2 As String
Dim r As Range
    On Error GoTo e_add_comment
    s1 = col2letter(lCol)
    Set r = ws.Range(s1 & CStr(lRow))
    If IsNull(r.Comment) Or r.Comment Is Nothing Then
        s2 = "" ' if no comments - use empty string
    Else
        s2 = r.Comment.Text & "" ' copy comment text
    End If
    If Len(s2) > 0 Then ' there is a comment text
        ' use restriction below only when you want unique comments
        If InStr(1, s2, sComment) < 1 Then ' which is not included our comment
            r.ClearComments ' clear comments
            r.addComment (s2 & Chr(10) & sComment) ' append old comment with new
        End If
    Else
        r.addComment sComment ' just add new comment
    End If
    Exit Sub
e_add_comment: ' just for fun. delete row below if you are not sure that you need it
    Debug.Print "cell(" & Str(lRow) & ":" & Str(lCol) & ")= " & Err.Description
End Sub

Actually, you can use col2letter function as a universal converter from decimal into a system with an arbitrary base. Just change sLetters constant to whenever you need.

Points of Interest

While I'm writing this tip, this guy finds another solution:

ws.Cells(lRow, lCol).Select
sAddrLoc = ActiveCell.AddressLocal
Range(CStr(sAddrLoc)).Addcomment (sComment)

History

• Written on May 21, 2015 12:25 A.M.