Coding Custom Date Functions in Excel

[george.mitri]

Hi all,

I've coded up a function in Excel that works in the Immediate Debug
window, but not in Excel itself.

The function looks up a cell range to see if a date is in the cell
range specified

----
Function GetPublicHoliday(InternalDate) As Boolean

Dim publicHolidayRange1 As Range
Dim findRange
Set publicHolidayRange1 = Worksheets("PublicHoliday").Range
("PublicHolidayRange") 'the range I wish to query

Set findRange = publicHolidayRange1.Find(What:=InternalDate, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

' the above should give me a result set based on if the Find method
can locate any cells that equal InternalDate

GetPublicHoliday = Not (findRange Is Nothing) 'If there is a range
found, we return true. If not, we return false.

End Function
---
While using the debug mode, the function Debug.print(GetPublicHoliday
(DateSerial(2009,01,01))) returns TRUE.
Unfortunately, if I use it in an Excel formula, say GetPublicHoliday
(Date(2009,01,01)), it returns FALSE.

Are there any subtle pointer things I need to worry about when using
date functions in Excel?

Hope you can help,

-George

 

[OssieMac]

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