My first UDF!!

UDF's are one of those things you know about but, as a 'decent novice developer', you need a reason to try.
I finally had a need and did the deed, and if anyone has an idea to make it faster please say:

In the business we refer to dates a lot as year-week combos YYYYWW, but there doesn't appear to be an existing function to evaluate the difference between two year-week dates and also take the entry of two week numbers (WW only) to be in the same year. Obviously regular maths won't work as the weeks have to be between 1 and 52.

So I came up with this:

Function YearWeekDiff(YrWk1 As Single, YrWk2 As Single)
Application.Volatile True

Dim Yr1 As Integer
Dim Wk1 As Integer
Dim Yr2 As Integer
Dim Wk2 As Integer

    Yr1 = Int(YrWk1 / 100)
    Wk1 = YrWk1 Mod 100
    Yr2 = Int(YrWk2 / 100)
    Wk2 = YrWk2 Mod 100
   
    If Wk1 < 1 Or Wk1 > 52 Then GoTo ErrHandler
    If Wk1 < 2 Or Wk2 > 52 Then GoTo ErrHandler

    YearWeekDiff = Yr2 - Yr1
    YearWeekDiff = YearWeekDiff * 52
    YearWeekDiff = YearWeekDiff + (Wk2 - Wk1)

wayout:
    Exit Function

ErrHandler:
    YearWeekDiff = CVErr(xlErrValue)
    GoTo wayout

End Function

I looked at trapping options but apart from ensuring the week part is between 1 and 52, it naturally errors if invalid criteria are used.
Entering two 'WW' values calculate as if same year - good.
Entering a 'WW' and a 'YYYYWW' creates a #VALUE error - good.

It looks good to me however it appears to be quite slow - about 160 microsecs - to calculate. Any suggestions?
I've also noticed that it doesn't appear to calculate automatically when the workbook is opened. Could this be something to do with the UDF being in said workbbok instead of separate.

A few thoughts.

You shoud have variant arguments, so it can accept ranges or numbers.

Use Long not Integer, Integer is converetd to Long by the OS, then back again.

VBA has an intere divide operator.

You test for Wk1 < 2, I think it should be Wk2 <1.

Shouldn't you test for YrWk1 being Not > YrWk2.

No need for the final Goto (or any really)

Function YearWeekDiff(YrWk1 As Variant, YrWk2 As Variant)
Dim Yr1 As Long, Wk1 As Long
Dim Yr2 As Long, Wk2 As Long

    Application.Volatile True

    Yr1 = YrWk1 \ 100
    Wk1 = YrWk1 Mod 100
    Yr2 = YrWk2 \ 100
    Wk2 = YrWk2 Mod 100

    If YrWk1 &gt; YrWk2 Then GoTo ErrHandler
    If Wk1 &lt; 1 Or Wk1 &gt; 52 Then GoTo ErrHandler
    If Wk2 &lt; 1 Or Wk2 &gt; 52 Then GoTo ErrHandler

    YearWeekDiff = Yr2 - Yr1 * 52 + (Wk2 - Wk1)

wayout:
    Exit Function

ErrHandler:
    YearWeekDiff = CVErr(xlErrValue)
End Function

 

BTW, you can do it in Excel

=(INT(H1/100)-INT(G1/100))*52-(MOD(G1,100)-MOD(H1,100))

 

 

Migt be quicker to do the final math in one.

Thanks Bob - food for thought here:

You shoud have variant arguments, so it can accept ranges or numbers.

 
Can you elaborate on the range option, not sure how that would apply here as it's just designed to compare 2 values and I'm a touch grey over the squiggly bracket { } array formulas

Use Long not Integer, Integer is converetd to Long by the OS, then back again.

 
Interesting - so is there any point in using Integer at all?

You test for Wk1 < 2, I think it should be Wk2 <1.

 
Yep - think at one speed - retype at another

Shouldn't you test for YrWk1 being Not > YrWk2.

 
Not here - a negative result would indicate a date coming forward.

No need for the final Goto (or any really)

 
True - it's a habit of always going back to the same exit point - and now missing the time it's not needed.

BTW, you can do it in Excel
=(INT(H1/100)-INT(G1/100))*52-(MOD(G1,100)-MOD(H1,100))

 
'tis true but the UDF happily generates the errors for mismatched or incorrect data which would need trapping before applying the formula.
I'm leaving the user the option of entering just the week numbers (ie dates in the same year) or year-week numbers but not allowing a mix of the two.

By making the arguments variant, on the worksheet you ceould us

=YearWeekDiff(2000810,200832)

or you could put those values in cells, say A1 and A2 and use

=YearWeekDiff(A1,A2)

nothing to do with arrays.

And with regard to Long/Integer, no, no point in using Integer except for some APIs which require integers.

 

 Thanks for your help Bob.

Nett result about 20% faster.

Function YearWeekDiff(YrWk1 As Variant, YrWk2 As Variant)
Application.Volatile True

Dim Yr1 As Long, Wk1 As Long
Dim Yr2 As Long, Wk2 As Long

    Yr1 = Int(YrWk1 / 100)
    Wk1 = YrWk1 Mod 100
    Yr2 = Int(YrWk2 / 100)
    Wk2 = YrWk2 Mod 100
   
    If Wk1 < 1 Or Wk1 > 52 Then GoTo ErrHandler
    If Wk1 < 2 Or Wk2 > 52 Then GoTo ErrHandler

    YearWeekDiff = (Yr2 - Yr1) * 52 + (Wk2 - Wk1)

wayout:
    Exit Function

ErrHandler:
    YearWeekDiff = CVErr(xlErrValue)

End Function

 Might be good to check out this article:

http://www.dailydoseofexcel.com/archives/2008/06/02/fixing-links-to-udfs-in-addins/

or (direct link to my site):

www.jkp-ads.com/articles/fixlinks2udf.asp