Formula -> Macro -> UDF

[adam_neb]

I have a complex formula (thanks to barry houdini!) that I use often. The problem is, it's complex!
I created a Macro by painstakingly entering the formula, but to use it I have to assign an alt-key or navigate to the macro Tools - Macro - Macros - Name of Macro.
I would rather have a User Defined Function where I could enter
=aown(CellReference) and get my results.

In "VBA and Macros for Microsoft Excel" Chapter 4 User-Defined Functions give examples of UDF's but I'm not sure how to enter my formula or get the formula into a UDF. Anyone point me in the right direction?


Sub aown()
'
' aown Macro
' Macro recorded 9/7/2006 by ao
'

'
ActiveCell.FormulaR1C1 = _
"=YEAR(RC[-1]-WEEKDAY(RC[-1])+4)&TEXT(INT((RC[-1]-WEEKDAY(RC[-1])-DATE(YEAR(RC[-1]-WEEKDAY(RC[-1])+4),1,4))/7)+2,""-00"")"
Range("J11").Select
End Sub

 

[dcardno]

Insert the following code into a regular module:

Function YrMo(rg As Range) As String

Dim iWday As Integer

iWday = Weekday(rg)

YrMo = CStr(Year(rg - iWday + 4)) & " - "
YrMo = YrMo & Int((rg - iWday - DateSerial(Year(rg - iWday + 4), 1, 4)) / 7) + 2

End Function

As you surmised, you reference this function just like any other Excel function, in some cell enter:
=yrmo(A3)
to return the year and week number of the date in A3. It isn't necessary to assign the "iWday" variable - it is just to make the expressions a little shorter since it is used a few times. It probably sacrifices a little speed for (a very little) convenience...

I didn't bother to force the week number to two digits, and I used an extra space in the seperator - those are sylistic issues, and you should adjust them to your preferences or needs.

 

[sweater_vests_rock]

adam.

try this out. for add'l functionality, you can now reference this to any cell (no reference defaults to left adjacent column).

Function WeekCount(Optional rngDate As Range) As String

    Dim dtStart As Date
    Dim strYear As String
    
'   Check for reference
    If rngDate Is Nothing Then Set rngDate = ActiveCell.Offset(0, -1)

'   Get year of comparison date
    strYear = Format(rngDate.Value, "yyyy")

'   Get the first day in the comparison year
    dtStart = CDate("1/1/" & strYear)
    
'   Return the comparison year and week # of the date in rngDate
    WeekCount = strYear & "-" & Int((rngDate.Value - dtStart) / 7) + 1
    
End Function

cheers. ben.

 

[barry houdini]

Hi ben,

VBA isn't my forte but your code doesn't give quite the right result here. My formula gives the week number based on the European ISO standard which means that week 1 may start in the previous year (or, indeed, start after 1st January). E.g. for all dates 28th December 2003 to 3rd January 2004 inclusive the result should be

2004-01

I believe dean's UDF gives the correct results

 

[sweater_vests_rock]

hi barry.

thanks for the background info. as you suspect, this code won't quite fit the problem -- unfortunately, my limited testing didn't pick up on this issue (i should have tried more years!)

regards. ben.

Hi ben,

VBA isn't my forte but your code doesn't give quite the right result here. My formula gives the week number based on the European ISO standard which means that week 1 may start in the previous year (or, indeed, start after 1st January). E.g. for all dates 28th December 2003 to 3rd January 2004 inclusive the result should be

2004-01

I believe dean's UDF gives the correct results