Microsoft word VBA Expiration Date

Question

Looking to change this Microsoft Word VBA Code so that the expiration date is always every Monday of the week, not a specific date:

Sub MyMacro()

ExpirationDate = #6/1/2013#
If Now() < ExpirationDate Then

    'Rest of macro goes here

End if
End Sub

Any thoughts on how to do this would be great :)

score 2 · Answer 1 · answered Jul 26 '17 at 18:52

2

If Weekday(Date) = 2 Then ... 'Monday

answered Jul 26 '17 at 18:52

user8355222

51
1
4

this works great. What if I needed it to run at a specific time too? I'm curious as to if that is possible – MAJ10 Jul 26 '17 at 19:57
Thanks for your feedback. You can expand the if statement with a check of the time, as shown in this post: [time operations](https://stackoverflow.com/a/8924251/8355222). If you want to start the macro at a specific time, look: [how to schedule an excel macro](https://stackoverflow.com/a/8915209/8355222) – user8355222 Jul 26 '17 at 20:10

score 1 · Answer 2 · answered Jul 26 '17 at 18:56

1

Public Function FindMonday(dt As Date) As Date
  Do Until WeekdayName(Weekday(dt)) = "Monday"
    dt = DateAdd("d", 1, dt)
  Loop
  FindMonday = dt
End Function

answered Jul 26 '17 at 18:56

braX

11,506
5
20
33

score 0 · Answer 3 · answered Jul 28 '17 at 10:13

ExpirationDate = (Date + 7) - (Weekday(Date) - Weekday(vbMonday))

This formula will always return the date of next Monday, as specified by "+7" You don't need to declare the variable Expirationdate.

If Date < (Date + 7) - (Weekday(Date) - Weekday(vbMonday)) Then will do the job.

Note that Now returns a date/time value whereas Date returns a date integer. If time is of the essence you would have to add it in. Add 0.5 to the above formula to fix the expiration time on next Monday, 12 noon.

score 0 · Answer 4 · answered Jul 28 '17 at 10:25

I'd usually use this function:

Public Function PreviousMonday(CurrentDate As Date) As Date
    PreviousMonday = CurrentDate - Weekday(CurrentDate - 2)
End Function

You can then call it as:
PreviousMonday(Date()) - would return 24/07/2017 if entered today (28th). PreviousMonday(CDATE("1 July 2017")) - would return 26/06/2017
PreviousMonday(42430) would return 29/02/2016 (42430 = 1st March 2016).

Microsoft word VBA Expiration Date

4 Answers4