Solved: recurring date - wildcard ?

[bdsii]

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 ?


[VBA]
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
[/VBA]

thanks in advance!

[mdmackillop]

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?
[vba]
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

[/vba]

[bdsii]

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]

Greetings,

Try:
[vba]
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
[/vba]
Hope that helps,

Mark

[GTO]

ACK! - Forgot to mention, above is inclusive of start/stopdates.

[mikerickson]

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]

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".
[vba]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[/vba]

[bdsii]

thanks for all the help, everyone ! Marking it solved :-)