Generate variable number of strings (error message "Constant expression required")

0
votes

I am creating a probability macro which has user enter an integer greater than zero. This integer then creates the same number of strings as its value (if integer is 5, 5 strings are created, then the strings "Player1", "Player2"..."Player5") are created. What is the easiest way to do this?

Right now I am using:

Sub FunctionPlayersCount()
a = 1
Dim Player(1 To Player)
Do Until a = Players
    a = a + 1 ' add 1 each time, approaching Players value
    Player(Player) = Player & a
    MsgBox Player & a
End Sub

When I run this function I get:

Compile error:

Constant expression required

3
vbaexcelexcel-2013
Which Player is which? This is extremely poor code, reminiscent of George Foreman naming all of his male children George. When the wife says "Hey, George!", which one of them is she addressing? Her husband, her oldest child, her youngest child, or one of the several in between oldest and youngest? When you refer to Player in Player(Player) = Player, which Player is which? Choose better variable names to use, such as Dim Players(1 to NumPlayers), try again, and then come back if you have problems. You also can't reference an array using a string + a numeral. - Ken White
Dim Player ( 1 to Player) ... oh my god! - Philippe Grondier

3 Answers

0
votes

You can't Dim 1 to n, but you can Redim :

Something like : 

   Dim Player(1 To 15) 
   Dim intMaxPlayer as Integer
   intMaxPlayer = 10
    ...
   ReDim player(1 to intMaxPlayer)

Also, Players is not defined in your Do Until loop

0
votes

I may be missing the point here, but I don't see why you need an array. This will create x strings "Player1" "Player2" ... "Playerx":

Sub FunctionPlayersCount()

Dim a As Integer
a = 1

Do While a <= NumPlayers

    MsgBox "Player" & CStr(a)
    a = a + 1 ' add 1 each time, approaching NumPlayers value

Loop

End Sub
0
votes

  1. Create an array based on the number of players. You'll need to use ReDim in order to use a variable for the array size:

    intPlayers = 5
ReDim Players(1 To intPlayers) As String

  2. Use a For loop to populate the array...

    For i = 1 To UBound(Players)
    Players(i) = "Player" & i
Next

Now you have a five-element array named Players that contains your five "PlayerX" strings.