Today's Week Number, *Custom Ribbon UI* [VBA]

[Martin_H]

Hi team,

I am building a custom ribbon UI Workbook.

I am using this (see below) to get Today's Date.

Function mydate()
mydate = Date
End Function

+

Sub DATE(control As IRibbonControl, ByRef label)
label = "" & mydate & ""
End Sub

Then I am using this (see below) to get Today's Weekday Name.

Function myweekdayname()
myweekdayname = WeekdayName(1, False, vbMonday)
End Function

+

Sub WeekdayName(control As IRibbonControl, ByRef label)
label = "" & myweekdayname & ""
End Sub

Now I would like to get Today's Weeknumber.

Normally I am using this formula =WEEKNUM(TODAY(),21) to get the right Weeknumber (mind the 21 at the end of the formula).

I would like to implement it the same way as the codes above.


Thank you.
Much appreciated.

 

[Dan_W]

One quick and easy way of using worksheet functions in VBA is to put WorksheetFunction. infront of it. Try:

label = WorksheetFunction.WeekNum(Now, 21)

 

[Martin_H]

One quick and easy way of using worksheet functions in VBA is to put WorksheetFunction. infront of it. Try:

label = WorksheetFunction.WeekNum(Now, 21)

Thank you Dan.
Much appreciated.

 

[Dan_W]

You're very welcome.