Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word97/VBA "Count occurrences"?

    Is there an inbuilt function to count occurrences of one string in another?

    I know of MID and INSTR, but I'd like to test a string of characters as follows:

    <pre>If CharCount(strSource, strDelimiter) > 1 then
    msgbox "Too many delimiters, sorry!"
    Else
    ' Go ahead, the strSource is valid
    Endif
    </pre>


  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word97/VBA "Count occurrences"?

    Not that I know of, but the following will do it:

    <pre>Public Function CountOccur(strSearch As String, strSource As String) As Long
    Dim lPos As Long, lNext As Long, lCount As Long
    lCount = 0
    lNext = 1
    Do
    lPos = InStr(lNext, strSource, strSearch)
    If lPos > 0 Then
    lCount = lCount + 1
    lNext = lPos + 1
    End If
    Loop While lPos > 0
    CountOccur = lCount
    End Function
    </pre>

    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Word97/VBA "Count occurrences"?

    Another approach would be to use the Split function to split the string into a zero-based array using the specified delimited. Then you could use the Ubound property of the array + 1 to tell you how many "words" you had, which would tell you how many instances of the delimiter you had.
    Charlotte

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Word97/VBA

    Being newly enamored of the Split function, I like this approach. However, (1) it requires VB6/Office2000, and (2) you do not need to add 1 to UBound(strArray()), since there should always be one more array element than delimiter. (I haven't tested what happens if the delimiter is in the first or last position, but I think then the 0th or nth element is a vbNullString.)

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word97/VBA "Count occurrences"?

    Does that work if the delimiter is more than one character? Just curious.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word97/VBA "Count occurrences"?

    Thanks Legare, I was afraid I'd missed out on a supplied function.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Word97/VBA

    It should. I popped that air code out of my memory (which is getting shaky these days[img]/S/granny.gif[/img]), and I can't remember whether I've tested that or not. I suspect it uses an algorithm that includes the equivalent of InStr to find the delimiters anyhow.
    Charlotte

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word97/VBA

    I was curious because the help files says that the delimiter parameter is a character which led me to think that maybe it would not. If I really want to know lbad enough, I guess I can try it.
    Legare Coleman

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Word97/VBA

    Works with a string (<A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=out&Number=20971>exampl e</A>) or a constant like vbCrLf. I love it.

Posting Permissions

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