On Wed, 19 Sep 2007 07:26:02 -0700, vba_kraker
wrote:

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