Code efficiency - Checking FY range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like some help with some efficient VBA code to check to see if a date
that is being input (wkMonth) is outside of the fiscal year (July-June) of
the minimum date that has already been inputted (minMonth). I have some code
that works but I have to repeat a block of code 4 times so I am hoping there
is a more efficient way to code this where I will only have to repeat my
result block once or twice.

Thanks!

BEGIN CODE-----------------------
Select Case Month(wkMonth)
Case 1 To 6
If Month(minMonth) > 6 Then
If Year(wkMonth) <> Year(minMonth) + 1 Then
MsgBox "You can't do this"
Exit Sub
End If
ElseIf Year(wkMonth) <> Year(minMonth) Then
MsgBox "You can't do this"
Exit Sub
End If
Case 7 To 12
If Month(minMonth) < 7 Then
If Year(wkMonth) <> Year(minMonth) - 1 Then
MsgBox "You can't do this"
Exit Sub
End If
ElseIf Year(wkMonth) <> Year(minMonth) Then
MsgBox "You can't do this"
Exit Sub
End If
End Select
END CODE------------------------------------
 
As i understand you - you can try to use following function:

Public Function IsOverlapping(ByVal Per1Beg As Date, _
ByVal Per1End As Date, _
ByVal Per2Beg As Date, _
ByVal Per2End As Date) As Boolean
If Per1Beg <= Per2Beg And Per1End <= Per2Beg Then Exit Function
If Per1Beg >= Per2End Then Exit Function
IsOverlapping = True
End Function

you can call it to check if date (or date range) is fall (or overlap) into
some interval.
 
Brandon R. said:
I would like some help with some efficient VBA code to check to see if a date
that is being input (wkMonth) is outside of the fiscal year (July-June) of
the minimum date that has already been inputted (minMonth). I have some code
that works but I have to repeat a block of code 4 times so I am hoping there
is a more efficient way to code this where I will only have to repeat my
result block once or twice.

BEGIN CODE-----------------------
Select Case Month(wkMonth)
Case 1 To 6
If Month(minMonth) > 6 Then
If Year(wkMonth) <> Year(minMonth) + 1 Then
MsgBox "You can't do this"
Exit Sub
End If
ElseIf Year(wkMonth) <> Year(minMonth) Then
MsgBox "You can't do this"
Exit Sub
End If
Case 7 To 12
If Month(minMonth) < 7 Then
If Year(wkMonth) <> Year(minMonth) - 1 Then
MsgBox "You can't do this"
Exit Sub
End If
ElseIf Year(wkMonth) <> Year(minMonth) Then
MsgBox "You can't do this"
Exit Sub
End If
End Select
END CODE------------------------------------
Click to expand...


If minMonth is the start of the fiscal year, I think this is
all you need:

If minMonth > wkMonth Or wkMonth >= DateAdd("yyyy", 1,
minMonth) Then
MsgBox "You can't do this"
Exit Sub
End If
 
Back
Top