"Subscript out of range", in VBA function?

343GS

New Member
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):

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

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Peter_SSs

MrExcel MVP, Moderator
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

Last edited:

oldbrewer

Board Regular
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

New Member
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

New Member

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

MrExcel MVP, Moderator
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.
Code:
``````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``````

Last edited:

oldbrewer

Board Regular
IT SHUFFLES the pack of 40 cards

oldbrewer

Board Regular
 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>

Replies
1
Views
46
Replies
5
Views
45
Replies
13
Views
246
Replies
4
Views
59
Replies
0
Views
35