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 i = i \ m If n = 0 Then n = 26 If i = 1 Then i = 0 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 = "" Else
s2 = r.Comment.Text & "" End If
If Len(s2) > 0 Then If InStr(1, s2, sComment) < 1 Then r.ClearComments r.addComment (s2 & Chr(10) & sComment) End If
Else
r.addComment sComment End If
Exit Sub
e_add_comment: 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.