[Solved] VBA: Weekday function

  • I am using the Weekday function in VBA to find the calendar date of the most recent Sunday as follows:

    SundayDate = Date
    While Weekday(SundayDate) <> 1
    SundayDate = SundayDate - 1

    For some reason I suddenly began getting an error when running this code, saying

    "Compile Error: Wrong number of arguments or invalid property assignment."

    Now, as far as I know the syntax here is correct, and no more than one argument is required. The strange this is, I created a brand new Excel file and copied the code, and it worked the way it was supposed to with no error message. After programming for some time on this new sheet (and saving several times, etc.) the code began to give me the error message again. Yet again I created a new file, copied the code, and it worked fine. I am pulling my hair out b/c I have spent several days programming only to have this code suddenly give me errors again. Please let me know if anyone has experienced this problem before.

  • Hi red,

    You've got a sub in Module1 that you defined with the name Weekday - the Worksheet_open module is trying to run that instead of the built-in function.

    Change the name of your sub and it should be OK


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!