Formula -> Macro -> UDF

adam_neb

Board Regular
Joined
Jun 9, 2002
Messages
101
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
536
Office Version
  1. 2013
Platform
  1. Windows
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.
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
adam.

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.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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 :cool:
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top