"Subscript out of range", in VBA function?

343GS

New Member
Joined
Jun 29, 2017
Messages
14
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!
 

Some videos you may like

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
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 11, 2010
Messages
11,003
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
Joined
Jun 29, 2017
Messages
14
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
Joined
Jun 29, 2017
Messages
14

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 11, 2010
Messages
11,003
pretend a deck of cards is the numbers 1 to 9
put a random number against each card
10.507108
20.909449
30.906568
40.612766
50.190419
60.315807
70.755895
80.852927
90.804632
now sort the table by the right hand column
50.190419
60.315807
10.507108
40.612766
70.755895
90.804632
80.852927
30.906568
20.909449
the deck is now shuffled

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

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,726
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top