Is there any way to emulate non-numeric subscripts in VBA?

[JenniferMurphy]

I need to tally some results for tennis players. Is there any way in Excel to emulate an array with non-numeric subscripts?

If I am reading data from a table with results for "Eliza", "Susie", and "Molly", can I get the effect of an array with these names as subscripts?

I am looking for the equivalent of one of these:

Winner = "Eliza"
Games(Winner) = Games(Winner) + 1
Games.Winner = Games.Winner + 1

Thanks

 

[Marcelo Branco]

You can use a Dictionary Object
https://excelmacromastery.com/vba-dictionary/

M.

 

[JenniferMurphy]

You can use a Dictionary Object
https://excelmacromastery.com/vba-dictionary/

M.

Marcelo,

That looks like exactly what I need. Thank you.

-j

 

[Marcelo Branco]

You are welcome.

M.

 

[JenniferMurphy]

I think I followed the instructions exactly, but I am getting an error. Here's the code. The error is indicated in the comment on line 7. I then copied the exact code from the example and it also gets an error (line 6). I swear I had this working a minute ago before making a few changes.

Function TallyResults(Results As Range, Player As String, WL As String, GSM As String)

Dim rRow As Range
Dim Winner, Loser, Set1, Set2, Set3 As String
Dim iRow, PlayerNum As Integer
Dim Tally As Long
Dim dict As New Scripting.Dictionary      'Error: User-defined type not defined
Dim GamesWon As New Scripting.dictionary  'Error: User-defined type not defined
Dim GamesLost As New Scripting.dictionary
Dim WinLoss() As String

PlayerNum = 0
For iRow = 2 To Results.Rows.Count - 1
  Set rRow = Results.Rows(iRow)
  Winner = rRow.Cells(1, 1)
  If Not GamesWon.exists(Winner) Then Do
    PlayerNum = PlayerNum + 1
    GamesWon.Add Key:=Winner, item:=0
    GamesLost.Add Key:=Winner, item:=0
  End
  Loser = rRow.Cells(1, 2)
  If Not GamesWon.exists(Loser) Then Do
    PlayerNum = PlayerNum + 1
    GamesWon.Add Key:=Loser, item:=0
    GamesLost.Add Key:=Loser, item:=0
  End
  Set1 = rRow.Cells(1, 3)
    WinLoss = Split(Set1, "-")
    GamesWon(Winner) = GamesWon(Winner) + WinLoss(0)
    GamesLost(Loser) = GamesLost(Loser) + WinLoss(1)
  Set2 = rRow.Cells(1, 4)
  Set3 = rRow.Cells(1, 5)
Next iRow

TallyResults = "???"

End Function

Can anyone tell me what's wrong?

 

[Marcelo Branco]

Your code is using early binding, so you need to add a reference

See
https://excelmacromastery.com/vba-dictionary/#Creating_a_Dictionary

Alternatively you can use late binding, that is:
Dim dict as Object
Set dict = CreateObject("Scripting.Dictionary")

M.

 

[JenniferMurphy]

Your code is using early binding, so you need to add a reference

See
https://excelmacromastery.com/vba-dictionary/#Creating_a_Dictionary

Alternatively you can use late binding, that is:
Dim dict as Object
Set dict = CreateObject("Scripting.Dictionary")

M.

I copied those examples and they get errors:

Function TallyResults(Results As Range, Player As String, WL As String, GSM As String)

Dim dict As New Scripting.Dictionary  'Error

'or

Dim dict As Scripting.Dictionary      'Error
Set dict = New Scripting.Dictionary

I wanted to use early binding because it seemed like that was what they were recommending.

 

[RoryA]

Did you set the reference to the Microsoft Scripting Runtime?

 

[JenniferMurphy]

Did you set the reference to the Microsoft Scripting Runtime?

That fixed it. Thanks. I misunderstood that part of the tutorial.

 

[JenniferMurphy]

The dictionary variables are working great. Thanks for that tip. Here's a section of that code.

Function TallyResults(Results As Range, Player As String, GSM As String, WL As String)

Dim rRow As Range     'Each row of table
Dim Winner As String  'Name of winner of each match
Dim Loser As String   'Name of loser of each match

Dim GamesWon As New Scripting.Dictionary
Dim GamesLost As New Scripting.Dictionary

Dim iRow As Integer                     'Row loop index
For iRow = 2 To Results.Rows.Count - 1  'Loop through the rows (after header & before end)
  Set rRow = Results.Rows(iRow)           'Get the next row
  Winner = UCase(rRow.Cells(1, 1))        'Get the name of the winner
  Loser = UCase(rRow.Cells(1, 2))         'Get the name of the winner
  Set1 = rRow.Cells(1, 3)                 'Get the score of the first set
    WinLoss = Split(Set1, "-")
    GamesWon(Winner) = GamesWon(Winner) + WinLoss(0)
    GamesLost(Loser) = GamesLost(Loser) + WinLoss(0)
    GamesWon(Loser) = GamesWon(Loser) + WinLoss(1)
    GamesLost(Winner) = GamesLost(Winner) + WinLoss(1)
  Set2 = rRow.Cells(1, 3)                 'Get the score of the first set
    . . . replicate code above
  Set3 = rRow.Cells(1, 3)                 'Get the score of the first set
    . . . replicate code above

  . . .

Next iRow

. . .

End Function

That all worked perfectly, but the game tally code had to be replicated after each of the 3 sets scores. So I moved that code to a subfunction. Here's a snippet of that code.

. . .
Dim iRow As Integer                     'Row loop index
For iRow = 2 To Results.Rows.Count - 1  'Loop through the rows (after header & before end)
  Set rRow = Results.Rows(iRow)           'Get the next row
  Winner = UCase(rRow.Cells(1, 1))        'Get the name of the winner
  Loser = UCase(rRow.Cells(1, 2))
  TallyResults = ProcessSet(rRow.Cells(1, 3), Winner, Loser, GamesWon, GamesLost)

. . .

End Function

Function ProcessSet(SetScore As String, Winner As String, Loser As String, GamesWon, GamesLost)
Dim WinLoss() As String

WinLoss = Split(SetScore, "-")
GamesWon(Winner) = GamesWon(Winner) + WinLoss(0)
GamesLost(Loser) = GamesLost(Loser) + WinLoss(0)
GamesWon(Loser) = GamesWon(Loser) + WinLoss(1)
GamesLost(Winner) = GamesLost(Winner) + WinLoss(1)

. . .

End Function

But when I did that, the tally of games won and lost changed from a sum to a string concatenation. For example, if Player A won a match by the score of 6-4 3-6 6-2, instead of the GamesWon total being 15 (6+3+6), it was 636 and the GamesLost changed from 12 (4+6+2) to 462.

My first thought was that I didn't specify the variable type on the subfunction argument list. So I changed that to:

Function ProcessSet(SetScore As String, Winner As String, Loser As String, _
                 GamesWon As Scripting.Dictionary, GamesLost As Scripting.Dictionary)

That did not fix the problem. It continued to return the concatenation rather than the sum.

My next fix was to explicitly convert the set score numbers to integers. Here's that code:

GamesWon(Winner) = GamesWon(Winner) + CLng(WinLoss(0))
GamesLost(Loser) = GamesLost(Loser) + CLng(WinLoss(0))
GamesWon(Loser) = GamesWon(Loser) + CLng(WinLoss(1))
GamesLost(Winner) = GamesLost(Winner) + CLng(WinLoss(1))

That fixed the problem.

Here's my question: Why did the sum work without the CLng when it was in the main function code, but not when it was in the subfunction?