Microsoft Excel: How to Comment Cells





0/5 (0 vote)
Convert column numbers to range letters and add comments
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:
- Number converter
- 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.