Syntax errors

[sassriverrat]

Good Morning,

Trying to compile a workbook, I keep getting a few errors. Here are the first two:

Syntax Error:

Function TotalAdder(RCell As Range)


'Begins Error Handling Code
On Error GoTo Helper


Dim xIndex As Long
Application.Volatile
xIndex = RCell.Worksheet.Index
nsheet = Left(Tname, 4) - "Noon"
If xIndex > 1 Then
TotalAdder = Worksheets(nsheet + 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 [1143] 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 Function
        ElseIf resp = vbCancel Then
            Exit Function
        End If
        
End Function

Argument not Optional (Prevsheet is a function)
Sub Formula_Chooser()


'Begins Error Handling Code
On Error GoTo Helper


Dim Form1 As Double
Dim Form2 As Double
Dim ws As Worksheet


Form1 = FormulaR1C1 = Sheets("Voyage Specifics").Range("D8")
Form2 = FormulaR1C1 = PrevSheet.Range("D8")
Set ws = ActiveSheet.Previous


If ws.name Like "Noon*" Then
    ActiveSheet.Range("D9") = Form2
Else: ActiveSheet.Range("D9") = Form1
End If


'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 [1171] 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 


Prevsheet function has been fine thus far:
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)
 PrevSheet = RCell.Parent.Parent.Worksheets(xIndex - 1).Range(RCell.Address).Value
    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
            Call Error_Handle(sprocname, Err.Number, Err.Description)
        ElseIf resp = vbNo Then
            Exit Function
        ElseIf resp = vbCancel Then
            Exit Function
        End If
End Function

 

[sassriverrat]

Ok, that's kind of what I thought but just wanted to confirm.

So what becomes the difference your code and my function (here's the actual prevsheet function)?

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)
 PrevSheet = RCell.Parent.Parent.Worksheets(xIndex - 1).Range(RCell.Address).Value
    End If
    
'Error Clearing Code
Exit Function

 

[sassriverrat]

Also side note, have I dim'd WS_Count correctly and what would you dim "Eqat1" as?

    Dim WS_Count As Integer
    WS_Count = ActiveWorkbook.Worksheets.Count
    Eqat1 = "="
    nooncnt = 0
        For i = 1 To WS_Count
             Tname = ActiveWorkbook.Worksheets(i).name
            If Left(Tname, 4) = "Noon" Then
             Eqat1 = Eqat1 & "+" & Tname & "!N12"
             nooncnt = nooncnt + 1
            End If
        Next i
        If nooncnt > 0 Then
        Range("N10").Formula = Eqat1 & "+R17"
    End If

 

[jasonb75]

Here's an error free re-write of your function

=PrevSheet(D8) in a worksheet will function exactly as before. If the formula is entered into the first sheet then it will show a #REF! error in the cell without tripping a vba error.
It can also be used in vba as

Form2 = prevsheet(Range("D8"),1)

noting the additional second argument.
0 or omitted will return the value of the cell in the previous sheet.
1 will return the formula from the cell in R1C1 format.
2 will return the name of the previous sheet. (this you will probably not need, but I've added it in to show the simple flexiblility of Select Case.

Also please remember my earlier comment that an unqualified range in vba will relate to the active sheet at the time the code is executed. With that in mind, PrevSheet used in vba might not behave as expected.

Option Explicit
Function PrevSheet(RCell As Range, Optional rType As Long = 0) As Variant
Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then
        Select Case rType
            Case 0
                PrevSheet = Sheets(xIndex - 1).Range(RCell.Address).Value
            Case 1
                PrevSheet = Sheets(xIndex - 1).Range(RCell.Address).FormulaR1C1
            Case 2
                PrevSheet = Sheets(xIndex - 1).Name
        End Select
    Else
        PrevSheet = CVErr(xlErrRef)
    End If
End Function

 

[sassriverrat]

Thank you very much!

 

[jasonb75]

WS_Count could be declared as Integer or Long, either will work correctly. Long can do everything that Integer can. Integer is practically a redundant data type now. There may be times when it is needed, but I can't think of any off hand.

Eqat1 should be declared as String (text), nooncnt should be declared as Long (or Integer).

Declaring all of your variables and using option explicit is one of the best ways to eliminate errors quickly.

If you start with nooncnt then mistype it as noncnt or noonct elsewhere then things are not going to work properly.

By declaring your variables properly and using option explicit, any such errors will be brought to your attention the first time you try to run the code, even if that variable is going to be bypassed by an If statement. Note that it only works on the code that you are trying to run, or any subsequent code being called. Unrelated procedures will not be checked.
Option Explicit needs to be entered at the top of every module that conatins code for the variables in it to be checked.

 

[sassriverrat]

Then you would declare tname (in that same function) as a string

This is excellent. That stuff makes sense.

 

[sassriverrat]

So two things have happened here:

1. I've learned the power of "option explicit" and let's just say it wasn't pretty...but 35 minutes of defining variables has done wonders.

2. My knowledge of variables, although miniscule, is far broader than it ever was.


Thank you