VBA "subscript out of range" in loop of variable length. Cannot locate issue

Question

I am currently trying to create a program which takes a variable number of players (denoted by (NumPlayerText.Value - 1)) and has them roll a die (NumThrowText.Value - 1) times. The values for each players rolls will be summed and compared, with the highest winning.

During the function, a loop designed to simulate (NumThrowText-1) dice rolls and add the values malfunctions ONLY when the number of players is higher than the number of rolls.

Here's the two interacting functions.

Function rollsumvalue(playernum)

Dim rolls()
ReDim rolls(NumThrowText.Value - 1)
ReDim rollsum(NumPlayerText.Value - 1)

    For playernum = 0 To (NumThrowText.Value - 1)
        rolls(playernum) = rolls(playernum) + random_generator(1, 6)
    Next
    rollsum(playernum) = (WorksheetFunction.Sum(rolls(playernum)))

End Function

Private Function callrollsumvalue()

For player = 0 To (NumPlayerText.Value - 1)
    rollsumvalue (player)
    MsgBox ("Rolled a total " & rollsum(player) & " points")
Next

End Function

rollsum is a global variable different than rolls FYI.

Debug shows subscript out of range from rolls(playernum) = rolls(playernum) + random_generator(1, 6)

However I cannot see why this occurs only when # of players > # of rolls

Sorry for the long question. Any help is appreciated!

What does Debug.Print NumThrowText.Value - 1) return? What is the value of playernum? — BigBen
– BigBen, Commented Nov 4, 2021 at 19:26
First of all: You reset rollsum with each call of rollsumvalue (redim rollsum(NumPlayerText.Value - 1)); like this it doesn't make sense to have it as a global variable. Furthermore you are dimming rolls based on NumThrowText.Value - 1 but use playernum as index for adding the random value. I think it would be sufficient if you have roll as a long-variable .. — Ike
– Ike, Commented Nov 4, 2021 at 19:33
These functions don't return anything ... they should be Subs. Also "rollsum is a global variable"... red flag. — BigBen
– BigBen, Commented Nov 4, 2021 at 19:34

Ike · Accepted Answer · 2021-11-04 19:49:38Z

Please be aware that I made some changes:

I am using two consts for NumThrows and NumPlayers as I don't have your input boxes.

I switched the order of the routines - it is usual to have the calling sub above the called function.

rollSumValue now returns the value for the player which then can be added to PlayersRollsum.

Within rollSumValue I am using a long variable to store the result - there is no need to store that value in an array.

If you want to re-use PlayersRollsum then you can change playAllPlayers to a function that returns PlayersRollsum as a result - but then you should think about a new name, like e.g. getAllPlayerResults. By that you avoid the global variable.

Option Explicit


Private Const NumThrows As Long = 5
Private Const NumPlayers As Long = 10

Public Sub playAllPlayers()

Dim PlayersRollsum(NumPlayers - 1) As Long

Dim playerNum As Long
For playerNum = 0 To NumPlayers - 1
    PlayersRollsum(playerNum) = rollSumValue(playerNum)
    MsgBox "Player " & playerNum & " rolled a total of " & PlayersRollsum(playerNum) & " points"
Next

End Sub

Private Function rollSumValue(playerNum As Long) As Long

Dim rolls As Long
Dim j As Long
For j = 1 To NumThrows
    rolls = rolls + random_generator(1, 6)
Next

rollSumValue = rolls

End Function

Collectives™ on Stack Overflow

VBA "subscript out of range" in loop of variable length. Cannot locate issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related