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 does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
558
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,966
Messages
5,856,563
Members
431,819
Latest member
agungrtz

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