UserForm code to cycle through prefilled cells on a worksheet encounters error after reaching the last cell of that sheet upon clicking "Next"

Question

I've got a vba program that issues an exam/test to the user that I'm developing. I've created the userform to loop through the questions in a worksheet database, and the following is the code of the UserForm. It loops through the questions great and inputs the information from my data array in each label that I've created but there's one issue:

When I get to the final question, if I click 'Next', it returns a "Subscript out of range" error. When I look at that section of the code in Sub RangeRow (byVal Direction As Long) section, when the r variable increases to a number greater than the lastrow of the test sheet, it should default to the last row (essentially keeping the question on the last question). I think it does that, but it still returns this Subscript out of range error. I've tried changing this if statement to read things like If r > Lastrow Then r = LastRow - 1 and other variations, but i think the issue lies when the array becomes nonexistant (via the LastRow declaration up top). I'm baffled on how to fix this.

Dim LastRow As Long
Dim r As Long
Dim Data As Variant

Private Sub Userform_Initialize()

    Dim ws As Worksheet
        
    Set ws = Sheets("Test")
    
    With ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Data = .Range("A3:D" & LastRow).Value
        
    End With
    
    r = Range("A3").Row - 1
    
    RangeRow r
    
End Sub

Private Sub NextRecord_Click()
    
    RangeRow xlNext
   
    
End Sub

Private Sub PreviousRecord_Click()

    RangeRow xlPrevious
    
End Sub

Sub RangeRow(ByVal Direction As Long)


    r = IIf(Direction = xlPrevious, r - 1, r + 1)
    If r < 1 Then r = 1
    If r > LastRow Then r = LastRow
    
    With Me

        .Label5.Caption = "Question ID: " & Data(r, 1)
        .Label6.Caption = "STS: " & Data(r, 2)
        .Label7.Caption = Sheets("Test").Cells(1, 3).Value
        .Label8.Caption = Data(r, 3)
        .txtAns.Text = Data(r, 4)

    End With
    
End Sub

FunThomas · Answer

You need to distinguish between row number in your Excel sheet and the row index in your array.
Your statement Data = .Range("A3:D" & LastRow).Value will create a 2-dimensional array, however, the index for both dimensions start at 1. So your data for row 3 (which is your first data row) is in index 1, for row 4 in index 2 and for LastRow it is in index LastRow - 2.

Change the RangeRow-routine to take that into account. Maybe

Sub RangeRow(ByVal Direction As Long)
    Const RowOffset = 2
    Dim lastIndex as Long
    lastIndex = LastRow - RowOffset   
    r = IIf(Direction = xlPrevious, r - 1, r + 1)
    If r < 1 Then r = 1
    If r > lastIndex Then r = lastIndex 
    (...)
End Sub

N.B. In your Initialize-Routine, you probably want to set r = 1.

UserForm code to cycle through prefilled cells on a worksheet encounters error after reaching the last cell of that sheet upon clicking "Next"

1 Answers