Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions excel 2003 | Excel Worksheet Functions | |||
Using custom functions within custom validation | Excel Discussion (Misc queries) | |||
color coding custom number formats | Excel Discussion (Misc queries) | |||
color coding custom number formats | Excel Discussion (Misc queries) | |||
Custom Functions | Excel Worksheet Functions |