"Subscript out of range", in VBA function?

[343GS]

Happy sunday everybody!
I am new to vba and I am trying to create a deck of 40 cards (mazzo2), with suits (seme) and values (valore):

Function NtoCarta40() As String
Dim seme(1 To 40) As String, valore(1 To 40) As Long, i As Long, mazzo2(1 To 40) As String

For i = 1 To 40

Select Case i
    Case i = 1 To 10
    seme(i) = "C"
    Case i = 11 To 20
    seme(i) = "P"
    Case i = 21 To 30
    seme(i) = "Q"
    Case i = 31 To 40
    seme(i) = "F"
End Select
Next

For i = 1 To 40
Select Case i
    Case i = 1 To 10
    valore(i) = i
    Case i = 11 To 20
    valore(i) = i - 10
    Case i = 21 To 30
    valore(i) = i - 20
    Case i = 31 To 40
    valore(i) = i - 30
End Select
Next
 
For i = 1 To 40
    mazzo2(i) = valore(i) & " " & seme(i)
Next
NtoCarta40 = mazzo2(i)

End Function

The problem is, after the last "Next" instruction I get error "Subscript out of range"......can someone help me to understand why?

Thank you!

 

[Peter_SSs]

The problem is, after the last "Next" instruction I get error "Subscript out of range"......can someone help me to understand why?

Yes, that final 'Next' increases i to 41 which is beyond the upper bound of mazzo2 which has an upper bound of 40

 

[oldbrewer]

why not list all cards in the deck in column A and a set of random numbers in column B, then sort ascending on column B ?

 

[343GS]

Yes, that final 'Next' increases i to 41 which is beyond the upper bound of mazzo2 which has an upper bound of 40

Thank you for the suggestion! but why does he do it.....? I mean I set i=1 to 40.....

 

[343GS]

why not list all cards in the deck in column A and a set of random numbers in column B, then sort ascending on column B ?

Thank you for the suggestion, but I don't understand the purpose exactly. Could you explain better please?

 

[Peter_SSs]

but why does he do it.....? I mean I set i=1 to 40.....

That is what happens in all loops. In your example, i eventually gets increased to 41 & when vba checks that against the For i = 1 to 40 it determines that i is now too large and does not return to the start of the loop.

Test by running this code & check the results.

Sub ForNextEndValues()
  Dim i As Long, k As Long, Z As Long
  
  For i = 1 To 5
    'Do nothing
  Next i
  For k = 17 To 11 Step -2
    'Do nothing
  Next k
  For Z = 5 To 100
    'Do nothing
  Next Z
  MsgBox "i = " & i & vbLf & "k = " & k & vbLf & "Z = " & Z
End Sub

 

[oldbrewer]

IT SHUFFLES the pack of 40 cards

 

[oldbrewer]

		pretend a deck of cards is the numbers 1 to 9
		put a random number against each card
		1	0.507108
		2	0.909449
		3	0.906568
		4	0.612766
		5	0.190419
		6	0.315807
		7	0.755895
		8	0.852927
		9	0.804632
		now sort the table by the right hand column
		5	0.190419
		6	0.315807
		1	0.507108
		4	0.612766
		7	0.755895
		9	0.804632
		8	0.852927
		3	0.906568
		2	0.909449
		the deck is now shuffled

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>