Click here to Skip to main content
12,503,676 members (32,221 online)
Click here to Skip to main content
Add your own
alternative version

Stats

2.5K views
Posted

Microsoft Excel: How to Comment Cells

, 21 May 2015 CPOL
Rate this:
Please Sign up or sign in to 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:

  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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Alexey Shtykov
Software Developer (Senior) NetCracker
Russian Federation Russian Federation
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160919.1 | Last Updated 21 May 2015
Article Copyright 2015 by Alexey Shtykov
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid