PDA

View Full Version : Solved: recurring date - wildcard ?



bdsii
01-13-2010, 06:47 AM
I would like to use code in an If statement to perform an action on a specific date each year if the workbook is open on that date.

I would use the systemdate but cannot figure out a way to have the code run each year. Is there a wildcard I could place in the year to allow it to run each year ?


Also, is there a way to code it so the code would run between two dates each year ? For example, between Jan 30 and Feb 5 each year ?



Sub DateCheck()
Dim currentdate As Date
currentdate = Date

If currentdate > "1/30/2010" Then
MsgBox "This is where code goes"
Exit Sub
Else
' Do Nothing - runs without code being activated
End If

End Sub


thanks in advance!

mdmackillop
01-13-2010, 07:57 AM
Also, is there a way to code it so the code would run between two dates each year ? For example, between Jan 30 and Feb 5 each year ?
Can you clarify further?

Option Explicit

Private Sub Workbook_Open()
DateCheck
End Sub

Private Sub DateCheck()
If Day(Date) = 30 And Month(Date) = 1 Then
MsgBox "This is where code goes"
Else
' Do Nothing - runs without code being activated
End If
End Sub

bdsii
01-13-2010, 09:02 AM
Thanks for the info mdmackillop....to clarify, if I wanted the code to be triggered by any date between Jan 30 and Feb 5 each year how would that be handled ?

If the dates were in the same month, you may be able to hande it by Day(Date) > 4 AND Day(Date) < 10 and using the same month code as above but I am not sure what you would in my example of triggering the code if the system date is between Jan 30 and Feb 5 each year.

Ideas ?

GTO
01-13-2010, 12:42 PM
Greetings,

Try:

Option Explicit

Sub exa()
Dim lCurDayOfYear As Long

'// Day of the year as of today //
lCurDayOfYear = CLng(Date) - CLng(DateSerial(Year(Date), 1, 1)) + 1

'// Jan 30 will be either 364th or 365th day of year, Feb 5 stays 36th //
If lCurDayOfYear >= 364 Or lCurDayOfYear <= 36 Then

MsgBox "Do stuff here"
End If
End Sub

Hope that helps,

Mark

GTO
01-13-2010, 12:44 PM
ACK! - Forgot to mention, above is inclusive of start/stopdates.

mikerickson
01-13-2010, 11:31 PM
The OnTime method can be specify the date and time that a proceedure is run. By setting EarliestTime and LatestTime variables, one can include a whole day.

mdmackillop
01-14-2010, 08:04 AM
This will carry out the "inner" code only on specific dates, but the code will run each time on workbook opening. It doesn't make things "more efficient".
Private Sub DateCheck()
If Date >= DateSerial(Year(Date), 1, 30) And Date <= DateSerial(Year(Date), 2, 5) Then
If Day(Date) = 30 And Month(Date) = 1 Then
MsgBox "This is where code goes"
Else
' Do Nothing - runs without code being activated
End If
End If
End Sub

bdsii
01-14-2010, 08:38 AM
thanks for all the help, everyone ! Marking it solved :-)