Coding Custom Date Functions in Excel
Hi George,
Try the code below however, if entering the function on a worksheet with the
find date as a cell reference then enter it this way:-
=GetPublicHoliday(C1) (where C1 contains a valid date)
If entering the date directly in the formula then this way:-
=GetPublicHoliday(DATEVALUE("25/4/09"))
Note the comments in the code.
Function GetPublicHoliday _
(InternalDate As Date) As Boolean
'Force Worksheet function updates automaticaly
Application.Volatile
Dim publicHolidayRange1 As Range
Dim findRange
Dim dateToFind As String
'Edit format to suit your Locale date format.
'Use same format for PublicHoliday range
dateToFind = Format(InternalDate, "dd/mm/yyyy")
Set publicHolidayRange1 = _
Worksheets("PublicHoliday").Range _
("PublicHolidayRange") 'the range I wish to query
Set findRange = publicHolidayRange1. _
Find(What:=dateToFind, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
GetPublicHoliday = Not (findRange Is Nothing)
End Function
--
Regards,
OssieMac
|