Workbook Coding Effecting other workbooks

[sassriverrat]

I have a workbook that seems to cause errors with other workbooks. It seems random but usually the error is 9- subscript out of range

Ideas on this? Here's an example of code that kicked when I opened another workbook

Function PrevSheet(RCell As Range)

'Begins Error Handling Code
On Error GoTo Helper


    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
    End If
    
'Error Clearing Code
Exit Function
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1141] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            UserForm18.Show
            'MsgBox ("Success")
        ElseIf resp = vbNo Then
            Exit Function
        ElseIf resp = vbCancel Then
            Exit Function
        End If
End Function

 

[mikerickson]

My guess is that Worksheets(xIndex-1) isn't qualified.

Try

PrevSheet = RCell.Parent.Parent.Worksheets(xIndex-1).Range(RCell.Address).Value

 

[sassriverrat]

I’ll give that a shot. I was working with another member in the forum on this and didn’t mean to repeat this post, but the other suggestion hadn’t worked.

If you have a second, purely for my learning, what does it mean above to not be qualified?

 

[mikerickson]

The OP code does not specify which workbook's Worksheets collection is to be used. This is called "unqualified".By default, the ActiveWorkbook is used.

PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)

The code in post #2 specifies that the Worksheets collection of the same workbook as RCell is to be used. This is called "qualifying" one's code.

PrevSheet = RCell.Parent.Parent.Worksheets(xIndex-1).Range(RCell.Address).Value

 

[sassriverrat]

The OP code does not specify which workbook's Worksheets collection is to be used. This is called "unqualified".By default, the ActiveWorkbook is used.

PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)

The code in post #2 specifies that the Worksheets collection of the same workbook as RCell is to be used. This is called "qualifying" one's code.

PrevSheet = RCell.Parent.Parent.Worksheets(xIndex-1).Range(RCell.Address).Value

Excellent. That makes sense. I'll play with it and see if any errors are induced!