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
