[VBA] Compile Error - Expected Function or Variable

[psycoperl]

I have been getting a Microsoft Visual Basic for Application: Compile error: Expected Function or Variable message when trying to write and test a new module that I am working on.

The Module has 1 function and 1 sub (listed below) . When I try to run the sub (doArchive()) I get the Compile Error. When I run the Function in the immediate window it works, no errors. Can someone please help me figure out why.

Thanks

Option Compare Database
Option Explicit

Public Function NeedArchive() As Boolean

    Dim CurrentDateTimeStamp As Date
    CurrentDateTimeStamp = Now
    
    Dim MaxDateTimeStamp As Date
    MaxDateTimeStamp = Nz(DLookup("Max([ArchivalTimeStamp])", QUICK_Archive_Location, "[IncomingTermCode]='" & quick_termcode & "'"), #1/1/1900 12:00:01 AM#)
    
    Debug.Print CurrentDateTimeStamp
    Debug.Print MaxDateTimeStamp
    Debug.Print DateAdd("n", QUICK_Archive_Threshold, MaxDateTimeStamp)
    
    NeedArchive = DateAdd("n", QUICK_Archive_Threshold, MaxDateTimeStamp) < CurrentDateTimeStamp

End Function

Public Sub doArchive()
  MsgBox "Got Here"
End Sub

 

[Grumpy Old DBA]

Trying Debug | Compile on your code yields the message you cite, and highlights QUICK_Archive_Location.
Excel is compiling the whole module.
If you just try to run doArchive, then only that part of the code is compiled and it works.

 

[psycoperl]

Thnank you.