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

 

[jasonb75]

The first one is most likely one of the following:-
-The procedure named Error_Handle() is not available to be called.
-One of the arguments of error handle is an undeclared variable, or the variable is of an incorrect type.
-The Error_Handle() procedure is not valid to be called from a function.

For the second one, the PrevSheet function appears to return the value of a cell, where as PreveSheet.Range() requires an object.
Something like

Form2 = FormulaR1C1 = Sheets(PrevSheet([COLOR="#FF0000"]rng[/COLOR])).Range("D8")

Where rng refers to a cell that the PrevSheet function can refer to, might work.

If you need modifications to the code, then maybe, as suggested in the code, you would be better off contacting the developer who wrote the code originally. My coding skills are not great, but I can see that this is tangled up like a very large bowl of spaghetti.

 

[sassriverrat]

Well here's the caveat- I am the developer.....this workbook clearly isn't perfected yet and I've learned most of my VBA building this massive workbook....but the releases I've had of it have bugged a few users at various times. The error coding there was so that I could attempt to find the bugs from users quickly, so most of the time they just close everything, reset, and tell me it locked up- at which point I don't know where to start looking....
As you can see in the error coding, it gives me a [###] which I've assigned

So a few things/questions:

1. Prevsheet- yes, the function is designed to call the value from a specified cell on the previous page- so if A1 on the previous sheet in the workbook had a value of 39 then =prevsheet(A1) on the activesheet returns the value from A1 on the previous sheet (therefore it equals 39). So you're saying add ".rng" into the middle of my formula?

2. Error handle procedure- here it is. I think it's had a various specification issue since the beginning because I've popped variable not specified errors before but I'm not sure what exactly they should be specified as.

Option Explicit
 
Enum W32_Window_State
    Show_Normal = 1
    Show_Minimized = 2
    Show_Maximized = 3
    Show_Min_No_Active = 7
    Show_Default = 10
End Enum
 
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hWnd As Long, _
ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
 
Function OpenURL(URL As String, WindowState As W32_Window_State) As Boolean
     
    'Opens passed URL with default application, or Error Code (<32) upon error
     
    Dim lngHWnd As Long
    Dim lngReturn As Long
     
    lngReturn = ShellExecute(lngHWnd, "open", URL, vbNullString, _
    vbNullString, WindowState)
     
    OpenURL = (lngReturn > 32)
     
End Function


Sub Open_error_log()
Dim name As String
    name = Sheets("Notes").Range("N4")
Dim path1 As String
Dim path2 As String
Dim strfilepath1 As String
    With Sheets("Developer")
        path1 = .Range("E44")
        path2 = .Range("J44")
    End With
    strfilepath1 = path1 & "\" & path2 & ".txt"
If Dir(strfilepath1) <> "" Then
    OpenURL (strfilepath1), Show_Maximized
Else: MsgBox "An Error Log currently does not exist", vbOKOnly, name
End If
End Sub


Public Sub Error_Handle(ByVal sRoutineName As String, _
                         ByVal sErrorNo As String, _
                         ByVal sErrorDescription As String)
Dim sMessage As String
   sMessage = sErrorNo & " - " & sErrorDescription
   'Call MsgBox(sMessage, vbCritical, sRoutineName & " - Error")
   With UserForm18
        .Label4.Caption = sRoutineName & " [" & sMessage & "]"
        .Show
    End With
   Call LogFile_WriteError(sRoutineName, sMessage)
End Sub



Public Function LogFile_WriteError(ByVal sRoutineName As String, _
                             ByVal sMessage As String)

Dim g_objFSO As Object
Dim g_scrText As Object
Set g_objFSO = CreateObject("Scripting.FileSystemObject")
Dim sText As String
Dim errdrive As String
Dim erraddress As String
errdrive = Sheets("Developer").Range("E44")
erraddress = Sheets("Developer").Range("J44")
Dim name As String
    name = Sheets("Notes").Range("N4")
Dim errfile As String
errfile = errdrive & "\" & erraddress & ".txt"
   'On Error GoTo ErrorHandler
   
   If (g_scrText Is Nothing) Then
      If (g_objFSO.FileExists(errfile) = False) Then
         Set g_scrText = g_objFSO.OpenTextFile(errfile, 2, True)
      Else
         Set g_scrText = g_objFSO.OpenTextFile(errfile, 8)
      End If
   End If
   sText = sText & "" & vbCrLf
   sText = sText & Format(Date, "dd MMM yyyy") & "-" & Time() & vbCrLf
   sText = sText & " " & sRoutineName & vbCrLf
   sText = sText & " " & sMessage & vbCrLf
   g_scrText.WriteLine sText
   g_scrText.Close
   Set g_scrText = Nothing
   Exit Function
'ErrorHandler:
   Set g_scrText = Nothing
   Call MsgBox("Unable to write to log file", vbCritical, name)
End Function

 

[Rick Rothstein]

The first error appears to be a syntax error... you have one opening parenthesis and two closing parentheses. Try removing one of the closing parentheses.

The second error appears to be because you used the function's return as if it were a Worksheet object, but your code does not seem to make the function return one of those. Although I have to admit I am not understanding what this line of code is actually supposed to be doing...

Form2 = FormulaR1C1 = PrevSheet.Range("D8")

 

[sassriverrat]

Hey @Rick Rothstein

So first error- I'll remove one of those parenthesis.

Second Error- if you're asking what the Form2 line is supposed to be doing, prevsheet is a function that just pulls a specified-cell's value from the previous worksheet. I use it in the workbook to specify the previous workbook's page because the of all of the sheets in the workbook, only three of them are constant. The others are added and deleted via vba all the time, so some of these formulas allow for dynamically creating these sheets without issues of changing references all the time. Hopefully that's what you were asking and that makes sense? If you would like, I can send you the workbook via email and you could see what I'm talking about if you would like.

Thanks

 

[jasonb75]

Your code is too messy to try and make any logical sense out of, I only had a very quick glance at it previously and missed at least 2 errors, the extra closing paretheses that Rick has pointed out, along with uncontrolled use of = twice in one line (more than once). Although not really an error, undeclard variables appear quite common.

With how little you give us to work with, we have very little hope of figuring out what you actually need.

One more guess

Form2 =Sheets(Range("D8").Worksheet.Index-1).Range("D8").FormulaR1C1

 

[sassriverrat]

I apologize for it being messy. I've constructed learned/constructed all of it in free time in this workbook. I know it's anything from perfect, and I'm not entirely sure when/how/which variables to define, despite limited reading about them. I've never taken a coding course of any sort.


Ok, looking at your Form2 code there, that references "D8" of the previous worksheet as a formula, correct?
Because that's what my mine is supposed to be doing- it's supposed to be inputting this into a sheet as a formula. It's worked as I've had it, just pulled a random syntax error once.

 

[sassriverrat]

So would my Form2 be correct if it was??

 form2 = prevsheet.range("D8").FormulaR1C1

Also- what brought this up was that if I hit compile workbookname, I get "Argument not optional" and "Prevsheet" in form2 is highlighted. Not sure what that means.

 

[jasonb75]

As far as variable declaration goes, type

Option Explicit

at the top of every module. That way, you will get a warning for anything that is not declared.
Use Long instead of Integer (it works the same but can cope with much bigger numbers). Double for decimals, String for text, Range and Worksheet are self explanatory. There are many more depending on the ultimate purpose of the variable.

Your code might appear to be working, but there is no valid syntax for it to work, my guess would be that the time the error occured was the only time that the line in question was executed.
You declared Form1 and Form2 as double. Double only supports numbers not formulas, so that would be an instant error.

The code sample that I provided for you will get the sheet index of the currect sheet (in vba this would be the active sheet unless specified different). It then looks at the previous sheet and assigns the formula from D8 of that sheet to the Form2 variable.

 

[jasonb75]

So would my Form2 be correct if it was

 form2 = prevsheet.range("D8").FormulaR1C1

That would not be correct. PrevSheet does not return a valid sheet object. It will not work how you want it to regardless of how you write the code. To make it work in that way you would need to re-write the whole function.