# Formula -> Macro -> UDF

##### Board Regular
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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Insert the following code into a regular module:
Code:
``````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.

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

Code:
``````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.

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

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

Replies
17
Views
585
Replies
24
Views
763
Replies
4
Views
231
Replies
1
Views
627
Replies
1
Views
82

1,220,009
Messages
6,151,441
Members
451,028
Latest member
greekness1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back