Count all Comma In Selection or selected text

Question

I want to count all Commas "," that occur only in selected text after that I will use Count as Integer to run the loop

My question is how do i Count , as following Image shows:

I Don't know how to use split and ubound. what is wrong with following code?

Sub CountComma()
Dim x As String, count As Integer, myRange As Range

Set myRange = ActiveDocument.Range(Selection.Range.Start, Selection.Range.End)

 x = Split(myRange, ",")
  count = UBound(x)

  Debug.Print count

End Sub

Answer 1

A simple split will work.

x = Split("XXX,XXX,XXX,XXX,XX,XX", ",")
  Count = UBound(x)
  Debug.Print Count

B/c the array starts at zero you can take to Ubound number as is.

EDIT: To use a range .

x = Split(Range("A1").Value, ",")

To break down the code.

Split("A string value","Delimiter to split the string by")

And if you want a single line of code than,

x = UBound(Split(myRange, ","))

Answer 2

Thanks to KyloRen and Cindy Meister, Now I can use split and Ubound for Counting , in selection.text.

Following is working Code:

    Sub Count_Words()
        Dim WrdArray() As String, myRange As String

        myRange = ActiveDocument.Range(Selection.Range.Start, Selection.Range.End)

        WrdArray() = Split(myRange, ", ")

        MsgBox ("Total , in the string : " & UBound(WrdArray()))

    End Sub

Answer 3

your code is wrong in the initial declaration statement of x variable as of string type , since in the subsequent statement

with x = Split(myRange, ",")

you'd want x hold the return value of Split() function which is an array (see here), thus of Variant type

so you have to use

Dim x As Variant

But you can simplify your code as follows

Option Explicit

Sub CountComma()
  Dim count As Integer

  count = UBound(Split(Selection, ","))
  Debug.Print count   

End Sub

since:

• you don't need any Range type variable to store Selection object into, being Selection the selected range already (see here)

• you don't need the x Variant variable neither, feeding UBound()function (which expects an array as its first argument) directly with the Split() function which, as we saw above, returns just an array!

Finally I'd give out an alternative method of counting commas in a range

Sub CountComma()
  Dim countAs Integer
  count = Len(Selection) - Len(Replace(Selection, ",", ""))

  Debug.Print count
End Sub