Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Counting comments (Excel 2000)

    Hi all,

    I have a range of cells with comments, what I would like to do is count the number comments on a worksheet., and also count the string inside the comment.

    Can someone get me started?

    Thanks Darryl.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting comments (Excel 2000)

    To count all comments on a worksheet, you can use VBA:

    ActiveSheet.Comments.Count

    for the currently actice sheet, or

    Worksheets("MySheet").Comments.Count

    for an arbitrary sheet. By "count the string", do you mean the number of characters, or the number of words? And do you want to add them together for all comments?

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Counting comments (Excel 2000)

    I am referring to a string in the comment, the comment itself would have no more than 21 characters in the comment. I would like it if I could take, extract a particular string and count it. E.g. "Edit,DT, AD,Henry's,0AA0" I would like to count how many times Edit appears in the comments on a sheet.

    Can this be done?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting comments (Excel 2000)

    Here is a VBA function to do this:

    Function CountStringInComments(strText As String, Optional strSheet As String) As Long
    Dim c As Comment
    Dim n As Long
    If strSheet = "" Then
    strSheet = ActiveSheet.Name
    End If
    For Each c In Worksheets(strSheet).Comments
    n = n - (InStr(1, c.Text, strText, vbTextCompare) > 0)
    Next c
    CountStringInComments = n
    Set c = Nothing
    End Function

    The function takes two arguments:
    strText is the string whose number of occurrences you want to count.
    strSheet is the name of the sheet to use; if you omit this, the currently active sheet is used.

    Notes:
    - This version is case insensitive: if you supply "this" as strText, "This" and "THIS" will also be counted.
    - If you want the function to be case sensitive, change vbTextCompare to vbBinaryCompare or omit it.
    - This version counts in how many comments the string occurs, if the string occurs more than once in a comment, it is counted only once.

    Examples of use:

    In VBA:
    MsgBox CountStringInComments("Edit", "Sheet3")

    In a formula in a cell:
    =CountStringInComments("Edit")

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Counting comments (Excel 2000)

    Thank you Hans

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting comments (Excel 2000)

    How about something like this?

    <pre>Option Explicit
    Function CountInComments(sSearch As String)
    Dim AWF As WorksheetFunction
    Dim sText As String
    Dim sTemp As String
    Dim wks As Worksheet
    Dim lCount As Long
    Dim cmt As Comment
    Set AWF = Application.WorksheetFunction
    lCount = 0
    For Each wks In Worksheets
    For Each cmt In wks.Comments
    sText = cmt.Text
    sTemp = AWF.Substitute(UCase(sText), UCase(sSearch), "")
    lCount = lCount + (Len(sText) - Len(sTemp)) / Len(sSearch)
    Next
    Next
    CountInComments = lCount
    End Function</pre>


    Call it with something like this
    <pre>Sub test()
    Dim sSearch As String
    sSearch = "Edit"
    MsgBox "'" & sSearch & "' appears " & _
    CountInComments(sSearch) & " Times in the comments"

    End Sub</pre>


  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Counting comments (Excel 2000)

    FYI, I would add <pre>Application.Volatile</pre>

    to the start of either Hans' or Steve's functions. Adding a comment will not trigger an update of either function, but adding the Volatile statement should cause the function to update whenever another cell gets recalculated. (Obviously this only applies if you need the function to be dynamic.)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Counting comments (Excel 2000)

    Hans,

    If you have a second I would like to ask you about the some of this code that you wrote. It works great by the way.

    Here's my question,

    n = n - (InStr(1, c.Text, strText, vbTextCompare) > 0) ' Question 1 - why the " -" sign in the code?

    Set c = Nothing -Question 2 why set c to nothing? what is the purpose of that?

    If you have the time


    Function CountStringInComments(strText As String, Optional strSheet As String) As Long
    Dim c As Comment
    Dim n As Long
    If strSheet = "" Then
    strSheet = ActiveSheet.Name
    End If
    For Each c In Worksheets(strSheet).Comments
    n = n - (InStr(1, c.Text, strText, vbTextCompare) > 0)
    Next c
    CountStringInComments = n
    Set c = Nothing
    End Function


    Thanks, Darryl

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting comments (Excel 2000)

    Hi Darryl,

    The InStr function returns either 0, if the string is not found, or a positive number that indicates the position where the (first occurrence of the) string is found.

    The expression (InStr(...) > 0) is True if InStr is positive, i.e. if the string is found, False otherwise. In VBA, True = -1 and False = 0. Each time the string is found, we want to add 1 to the running count; this is done by subtracting the -1.

    In general, it is a good idea to set all object variables to Nothing when they are no longer needed. Object variables (the type you assign a value to by using Set varname = value) can take up a lot of memory. By setting an object to Nothing, the object is destroyed and the memory it occupied is released.

    Note: in most cases, it isn't dramatic if you forget to set an object to Nothing. But in some cases it may have a negative effect on performance.

  10. #10
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Counting comments (Excel 2000)

    Thanks Hans,

    I understand

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •