Solved: User Defined Function

[Digita]

Hi everyone,

I’m working on a VBA function to calculate the number of days between 2 dates (eg: how many Mondays, Tuesdays, Sat & Sun, public holidays, weekends (including Sat & Sun) and public holidays.

The syntax is howmanydays(“end date”, “start date”, sw)

Sw = 0 to 9
Sw = 0 (to calc number of the Sat & Sun
Sw = 1 (Sun)
Sw = 2 – 7 (Mon - Sat)
Sw = 8 (to calc the number of public holidays
Sw = 9 (to work out how many public holidays + Sat + Sun

Currently, the function only gives me correct answers on the number of Sat & Sun (sw = 0). If I use other numbers (1 – 9) the function gives me incorrect results.

Also, if I delete a holiday within the range (between start date and end date) the function does not refresh its calculation.

Can someone please help me find out where I went wrong?

I enclose a sample workbook for your reference. Thanks for your kind assistance.

Regards

KP

[JimmyTheHand]

Hi Digita

There's only one little error in your code. Note the parentheses in red bold:
[vba]ElseIf Sw = 9 And (WorksheetFunction.CountIf(Range("A2:A11"), EndDate - i) > 0 Or _
(Weekday(EndDate - i) = 1 Or Weekday(EndDate - i) = 7)) Then[/vba]
As you might know, IF conditions are processed in a special order. AND goes before OR. Without the parentheses I applied, your code was like this:

IF
Sw = 9 And WorksheetFunction.CountIf(Range("A2:A11"), EndDate - i) > 0
OR
(Weekday(EndDate - i) = 1
OR
Weekday(EndDate - i) = 7
THEN

So there were 3 ways to pass the test, instead of only 1, which resulted in more hits than expected.

Cheers,

Jimmy

Edited 8-Jun-07 by geekgirlau. Reason: insert line breaks

[Digita]

Hi Jimmy,

Thanks for the magic fix. The other question I was wondering was:

If my start date is 2 Jan 07 and end date 30 Apr 07, I use this UDF to find out how many public holidays between those dates. The answer the function return is 4 which is correct. Now a hypothetical scenario, if I delete 1 public holiday entry being 6 Apr 07 from column A, the UDF does not refresh the calculation to adjust the value from 4 to 3.

Would you be able to shed some light on this?

Thanks for your help.

Regards


KP

[JimmyTheHand]

if I delete 1 public holiday entry being 6 Apr 07 from column A, the UDF does not refresh the calculation to adjust the value from 4 to 3.

That's because UDF's are only recalculated if/when their argument ranges change. Range("A2:A11") is not passed on as an argument, it's hardcoded. You might want to add another argument to the function, then use it in the code, like this:

[vba]
Function HowManyDays(EndDate As Date, StartDate As Date, Sw As Integer, _
RangeOfHolidays As Range) As Integer
'etc.
ElseIf Sw = 8 And WorksheetFunction.CountIf(RangeOfHolidays, EndDate - i) > 0 Then
'etc.
[/vba] If you make this modification, you'll need to edit the formulas in the cells as well. For example, for example, formula in G4 becomes
=howmanydays(D2,C2,2,A2:A11)


Also, I found another error. In the 3rd line of the code you use a variable called DayName which, I presume, was a previous version, and had been replaced by Sw, everywhere but here. Also, it should be checked against negative numbers. Something like this:
[vba]If EndDate < StartDate Or Sw > 9 Or Sw < 0 Then[/vba]
Jimmy

Edited 8-Jun-07 by geekgirlau. Reason: insert line breaks

[Digita]

Jimmy,

It works perfectly. Thank you a thousand times for your superb advice and attention to details.

Best regards

KP

[JimmyTheHand]

Jimmy,

It works perfectly. Thank you a thousand times for your superb advice and attention to details.

Best regards

KP

You are very welcome
Please, mark the thread as solved, if you think it is.

Cheers,

Jimmy