View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] george.mitri@gmail.com is offline
external usenet poster
 
Posts: 1
Default Coding Custom Date Functions in Excel

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