Count number of specific values in an array (VBA)

[selant]

I want to count how many "woman" values in my person array. Example as follows :
Can anybody share the solution for the function if there is ? Thank you.

Private Sub CommandButton1_Click()


Dim person(1 To 5) As String


person(1) = "man"
person(2) = "woman"
person(3) = "woman"
person(4) = "man"
person(5) = "man"


Label1.Caption = "There are 2 women" 'calculate how many "woman" in person array
Label2.Caption = "There are 3 men" 'calculate how many "man" in person array


End Sub

 

[Peter_SSs]

Partly to refresh my own mind, some comments about doing this without looping if you want.

1. If we know that none of the array items will be substrings of another array item. That is NOT like the given example where "man" is a substring of "woman".

Sub Ex1()
  Dim animal(1 To 5) As String
  Dim c As Long, d As Long
  
  animal(1) = "dog"
  animal(2) = ""
  animal(3) = "cat"
  animal(4) = "dog"
  animal(5) = "dog"
  
  c = UBound(Filter(animal, "cat", True, 1)) + 1
  d = UBound(Filter(animal, "dog", True, 1)) + 1
  MsgBox "Cats: " & c & vbLf & "Dogs: " & d
End Sub

2. For the OP's example where we know there are two items and one is a substring of the other.

Sub Ex2()
  Dim person(1 To 5) As String
  Dim m As Long, w As Long
  
  person(1) = "man"
  person(2) = "woman"
  person(3) = "woman"
  person(4) = "man"
  person(5) = "man"
  
  'Count the longer one first
  w = UBound(Filter(person, "woman", True, 1)) + 1
  'Use the first result to adjust the shorter count
  m = UBound(Filter(person, "man", True, 1)) + 1 - w
  MsgBox "Women: " & w & vbLf & "Men: " & m
End Sub

3. If we don't know what to expect in terms of substrings.

Sub Ex3()
  Dim person(1 To 5) As String
  Dim m As Long, w As Long
  Dim ary As Variant
  
  'Constants set to something that will not be in the array items
  Const Mkr As String = "!"
  Const Del As String = ","
  
  person(1) = "man"
  person(2) = "woman"
  person(3) = "woman"
  person(4) = "man"
  person(5) = "man"
  
  ary = Split(Mkr & Join(person, Mkr & Del & Mkr) & Mkr, Del)
  'Count the items (Surrounded by markers) directly
  w = UBound(Filter(ary, Mkr & "woman" & Mkr, True, 1)) + 1
  m = UBound(Filter(ary, Mkr & "man" & Mkr, True, 1)) + 1
  MsgBox "Women: " & w & vbLf & "Men: " & m
End Sub

 

[Scott Huish]

That's the first time I've seen Filter. That is awesome!

 

[selant]

dear Peter_SSs , i appreciate for the detailed solutions and alternatives, awesome.

 

[Peter_SSs]

That's the first time I've seen Filter. That is awesome!

Yes, Filter can be pretty handy but I do find it frustrating that it always does a partial match.
Would be much easier to use if there was an "exact" v "partial" option, or even if an exact match was the default and wildcards were possible.

Note that the third argument can be set to False to filter for "everything except" (the second argument).

My comment in the third code "Constants set to something that will not be in the array items" is not quite accurate either.
It is only the Del (Delimiter) that needs to not be in any of the array items.
I think the Mkr (Marker) could be anything. For example, in that code, Mkr could just as well be "man" or "woman".