I’m attempting to developed a program that dynamically assigns tennis players to a court based on how frequently they’ve played, who they previously played against, etc. In this program, I am currently using a Dictionary and I’ve also tried using a Collection to see which may be more efficient. I've been able to get the program to work In both cases, but the time to process court time for one week took approx 14+ seconds which is only for 8 players (70 combinations) and for two courts.

I don't know if the problem resides with how I'm utilizing the Dictionary or if it is simply the inefficiency of my code. I would like to try and provide a little background then then show a chunk of my code which may very will be dragging the time out with hopes that someone may be able to find a more efficient way of performing the same function. Since I would eventually would like to expand this out to three courts and 12 players, improved efficiency is critical and aany help or guidance would be appreciated.

This all starts with the following:

Subroutine # 1

Subroutine #2

I hope this make sense and someone is able to provide some guidance on how to improve the efficiency of this program. Thanks for any thoughts you may pass along this way.

I don't know if the problem resides with how I'm utilizing the Dictionary or if it is simply the inefficiency of my code. I would like to try and provide a little background then then show a chunk of my code which may very will be dragging the time out with hopes that someone may be able to find a more efficient way of performing the same function. Since I would eventually would like to expand this out to three courts and 12 players, improved efficiency is critical and aany help or guidance would be appreciated.

This all starts with the following:

Subroutine # 1

- A list of 8 player numbers from Sheets("CommonData").Range("$E$25:$L$25").Value2. These are simply 1, 2, 4, 5, 6, 7, 9, 10 (or any combination from 1 to 11)
- This range of numbers is placed in a Dictionary for future manipulation using

VBA Code:

```
For Each c In rngDic
dicPrimary1.Add Key:=c.Value, item:=c.Value
Next
```

- an array (called lNumbers) which includes a list of 70 combination of 8 player numbers (listed above) taken 4 at a time.
- Then I begin to loop thru each one of the combinations in lNumbers using For i = 1 To UBound(lNumbers)
- lNumbers contains the first potential 4 players and removed them from the Dictionary thus leaving me with the remaining four players to consider. Let’s say players 1, 2, 4 and 5 were first on the list.

VBA Code:

```
If NumCourts > 1 Then 'If working on filling more than one court
dicPrimary1.Remove Key:=lNumbers(i, 1)
dicPrimary1.Remove Key:=lNumbers(i, 2)
dicPrimary1.Remove Key:=lNumbers(i, 3)
dicPrimary1.Remove Key:=lNumbers(i, 4)
Call Subroutine2
End If
```

Subroutine #2

- List of players is now down to 4 from Sheets("CommonData").Range("$E$25:$H$25").Value2. Based on above example, players 6, 7, 9, and 10 remain.
- The four player numbers from Subroutine 1 is passed along to Subroutine 2 via Global Variables called Player1, Player2, Player3 and Player 4
- This following may be one location which may cause the system to bog down.
- Notes: FirstCourtT = a Gloval Variable which will contain a calculated value associated with the four player numbers being forwarded from Subroutine 1.
- lNumber(1,5) = a value associated with the remaining four player numbers for Subroutine 2 to examine

VBA Code:

```
If NumCourts = 2 Then 'Determines if we are dealing with only 2 courts
If nFirstTime1 = 1 Then 'First time thru, sets values to arrTestCombo()
arrTestCombo(1) = FirstCourtT + lNumbers(1, 5)
arrTestCombo(2) = Player1 'Player 1 from Court 1, etc.
arrTestCombo(3) = Player2
arrTestCombo(4) = Player3
arrTestCombo(5) = Player4
arrTestCombo(6) = lNumbers(1, 1) 'Player 1 from Court 2, etc.
arrTestCombo(7) = lNumbers(1, 2)
arrTestCombo(8) = lNumbers(1, 3)
arrTestCombo(9) = lNumbers(1, 4)
nFirstTime1 = 2
FirstCourtT = 0
GoTo NextTeam
ElseIf arrTestCombo(1) < (FirstCourtT + lNumbers(1, 5)) Then
arrTestCombo(1) = FirstCourtT + lNumbers(1, 5)
arrTestCombo(2) = Player1
arrTestCombo(3) = Player2
arrTestCombo(4) = Player3
arrTestCombo(5) = Player4
arrTestCombo(6) = lNumbers(1, 1)
arrTestCombo(7) = lNumbers(1, 2)
arrTestCombo(8) = lNumbers(1, 3)
arrTestCombo(9) = lNumbers(1, 4)
EndIf
End if
NextTeam:
```

I hope this make sense and someone is able to provide some guidance on how to improve the efficiency of this program. Thanks for any thoughts you may pass along this way.

Last edited: