Function kicking error with two workbooks

[sassriverrat]

I have two workbooks identically named- one is the master and one a working copy. If I open both, the second one opening kicks an 9- out of script error. What causes this?

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

 

[Joe4]

When you hit "Debug", which line of code is highlighted?

Where and how is this Function being used?
In VBA, or in a formula on the sheet?
Please post the code/formula that uses it.

 

[mikerickson]

It looks like you need to fully qualify this line

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

 

[sassriverrat]

So to answer your question, @Joe4, it's used as formula within sheets. I have code that creates sheets, formats, and then fills in formulas into cells (so I guess it's technically in the code as well).

For code, it's listed as such below:

Range("D8").FormulaR1C1 = "=IF(R[-4]C[2]=""No Data Input"",0,IF(R[18]C[19]="""",((INT(ABS((((((R[-4]C+((IF(R[-3]C[-2]=""-"",TIME(R[-3]C[-1],0,0),-TIME(R[-3]C[-1],0,0)))))+R[-4]C[2])-((PrevSheet(R[-4]C)+((IF(PrevSheet(R[-3]C[-2])=""-"",TIME(PrevSheet(R[-3]C[-1]),0,0),-TIME(PrevSheet(R[-3]C[-1]),0,0)))))+PrevSheet(R[-4]C[2]))))))))*24)+(HOUR(ABS((((((R[-4]C+((IF(R[-3]C[-2]=""-"",TIME(R[-3]C[-1]" & _        ",0,0),-TIME(R[-3]C[-1],0,0)))))+R[-4]C[2])-((PrevSheet(R[-4]C)+(IF(PrevSheet(R[-3]C[-2])=""-"",TIME(PrevSheet(R[-3]C[-1]),0,0),-TIME(PrevSheet(R[-3]C[-1]),0,0))))+PrevSheet(R[-4]C[2])))))))),R[18]C[19]))" & _
        ""

and that's just one instance of a formula that uses it. Now that function, used in the formula, just references a specified cell on the previous sheet, regardless of what the previous sheet is.

For @MikeErickson I'll give that a try and see what happens. To answer my lack of knowledge, what did that do (versus what I had)?

After correcting that, I got an error on another section of coding within excel when opening up a workbook next to this one (I'm using my "helper" lines to identify which sections of coding go off). Also Error 9- subscript out of range.

Private Sub CommandButton1_Click()
'On Error GoTo Helper
    
    Sheets("Voyage Specifics").Select (error'd line)
    Unload Me
    Application.Visible = True
'Error Clearing Code
Exit Sub
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 [1034] 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
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

Continuing to play with it, it seems that opening two instances of this workbook, meaning the same workbook but differing by name (Master Voyage vs Current Voyage), let's say, causes the above error and a whole slew of others to go off....

 

[sassriverrat]

Correction: Opening the workbook with apparently any other excel document already open kicks off a stream of errors. Here's another example of one that kicked:

Sub VoyageSpecifics()

'Begins Error Handling Code
'On Error GoTo Helper


' VoyageSpecifics Macro
' This controls a page that has all of the Voyage Specifics saved on it
'Speeds up the formating part
Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
'Error Checks for a Voyage Specifics Sheet already existing
    Dim name As String
    name = Sheets("Notes").Range("N4") '(error'd line)

    If SheetExists("Voyage Specifics") Then
        VS = MsgBox("Voyage Specifics Sheet already exists, would you like to delete it and start over?", vbYesNoCancel, name)
        If VS = vbYes Then
            Sheets("Voyage Specifics").Delete
            Call VoyageSpecificsCreate
        ElseIf vbNo Then
            Sheets("VoyageSpecifics").Select
            Exit Sub
        End If
    Else: Call VoyageSpecificsCreate
    End If
    'Speeds up the formating part
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
'Error Clearing Code
Exit Sub
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 [1163] 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
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

Where "Notes" "N4" is a name....

Again getting a "9- out of range" error

Or this one:

Private Sub CommandButton1_Click()'On Error GoTo Helper
    Call VoyageSpecifics
    Unload Me
    UserForm4.Show '(error'd line)
'Error Clearing Code
Exit Sub
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 [1023] 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
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
End Sub

 

[sassriverrat]

bump