weeknumber function in vba(excel) doesn't work

[Guest]

How to get a proper weeknum function in vba working?
I'm familiar with the weeknum function in the ordinary worksheet, but in vba
it's a different story.
Has anyone suggestions how to generate the weeknumber by the means of a
function in vba?

Many thanks in advance.

 

[Guest]

Use the date part function

DatePart(Interval, Date

DatePart("ww",date) will produce the week number

 

[Ron de Bruin]

Hi

See this article with two VBA functions
http://msdn2.microsoft.com/en-us/library/bb277364.aspx

See also
http://www.rondebruin.nl/weeknumber.htm

 

[Guest]

Hey Ron, thanks for the excellent information.

 

[Ron Rosenfeld]

How to get a proper weeknum function in vba working?
I'm familiar with the weeknum function in the ordinary worksheet, but in vba
it's a different story.
Has anyone suggestions how to generate the weeknumber by the means of a
function in vba?

Many thanks in advance.

To mimic the Excel worksheet function WEEKNUM, you may use this UDF:

===========================
Function VBWeeknum(dt As Date, _
Optional FirstDayOfWeek As Integer = 1)

'VB Replacement for WEEKNUM worksheet function

Select Case FirstDayOfWeek
Case 1 To 2
VBWeeknum = DatePart("ww", dt, FirstDayOfWeek, vbFirstJan1)
Case Else
VBWeeknum = CVErr(xlErrNum)
End Select
End Function
=================================

To generate a weeknumber in accordance with ISO standards, you may use this
UDF:

=========================================
Function ISOWeeknum(d1 As Date) As Integer
' Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
ISOWeeknum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function
=============================
--ron

 

[Guest]

Thanx Ron, the first UDF did the trick!

 

[Ron Rosenfeld]

Thanx Ron, the first UDF did the trick!

Glad to help. Thanks for the feedback.
--ron