excel - Why am I getting Compile Error: Expected End Sub

Question

I have the following vba code and when I try to run it, it says: Compile Error: Expected End Sub.

Anyone know what I'm doing wrong? I know NOTHING about vba. I want this to check for the last modified file and then tell me if it's more than 5 years old in a msgbox.

Sub LastModifiedFile()

Function FileLastModified(strFullFileName As String)
    Dim fs As Object, f As Object, s As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(strFullFileName)

    s = UCase(strFullFileName) & vbCrLf
    s = s & "Last Modified: " & f.DateLastModified
    FileLastModified = s

    Set fs = Nothing: Set f = Nothing

If FileExists(strFullName) Then
        MsgBox FileLastModified(strFullName)
    Else
        MsgBox "File Older than 5 Years : " & vbNewLine & strFullName
    End If

End Function

End Sub

Answer 1

That code won't work as it is. You have the code from a function and code from a procedure intertwined with each other.

The first line defines your procedure:

  Sub LastModifiedFile()

You then have code relevant to the function.
This code should find out the last modified date of each file name passed to it and return that date to the main procedure... you can see the second from last line where it says FileLastModified = s. That's the line where it passes the value back to the calling procedure.

Function FileLastModified(strFullFileName As String)
    Dim fs As Object, f As Object, s As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(strFullFileName)

    s = UCase(strFullFileName) & vbCrLf
    s = s & "Last Modified: " & f.DateLastModified
    FileLastModified = s

    Set fs = Nothing: Set f = Nothing

Next you have some code from the main procedure again.
You can see on the second line where it's asking the FileLastModified function to look at the path of whichever file you pass to it.
There's a problem here - the first line is calling for a function called FileExists(strFullName). This is passing the file path to a function that you haven't posted asking if the file exists in the first place - it should return TRUE or FALSE.
Another problem here is that if it can't find the file it says it's older than five years, rather than saying it doesn't exist at all.

If FileExists(strFullName) Then
        MsgBox FileLastModified(strFullName)
    Else
        MsgBox "File Older than 5 Years : " & vbNewLine & strFullName
    End If

After this bit of code there two lines - one ends the function and the other ends the sub.

End Function

End Sub  

Your code should look more like this:

Sub LastModifiedFile()

    'Check if File Exists using `FileExists` function.
    If FileExists(strFullName) Then
        'If it does exist then pass the path to see when it was last modified.
        MsgBox FileLastModified(strFullName)
    Else
        'If it doesn't exist then say it's older than 5 years.
        MsgBox "File Older than 5 Years : " & vbNewLine & strFullName
    End If

End Sub

Function FileLastModified(strFullFileName As String)

    Dim fs As Object, f As Object, s As String

    Set fs = CreateObject("Scripting.FileSystemObject")

    'Returns a file object.
    Set f = fs.GetFile(strFullFileName)

    s = UCase(strFullFileName) & vbCrLf

    'f.DateLastModified returns the modified date of the file object.
    s = s & "Last Modified: " & f.DateLastModified

    'Pass the string variable back to whatever called it.
    FileLastModified = s

    Set fs = Nothing: Set f = Nothing

End Function  

You'll still need the code for the FileExists function though.