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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,949
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
Winner = "Eliza"
Games(Winner) = Games(Winner) + 1
Games.Winner = Games.Winner + 1

Thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,949
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,949
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,048
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Did you set the reference to the Microsoft Scripting Runtime?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,949
Office Version
  1. 365
Platform
  1. Windows
The dictionary variables are working great. Thanks for that tip. Here's a section of that code.
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.
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:
Code:
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:
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?
 

Forum statistics

Threads
1,171,225
Messages
5,874,464
Members
433,054
Latest member
Agridoc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top