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.

Use the date part function

DatePart(Interval, Date

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


"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.

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"vba_kraker" wrote in message ...
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.

Hey Ron, thanks for the excellent information.
--
Kevin Backmann


"Ron de Bruin" wrote:

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"vba_kraker" wrote in message ...
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.

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

Thanx Ron, the first UDF did the trick!


--
Regards,

Adrian


"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.

On Thu, 20 Sep 2007 01:30:01 -0700, vba_kraker
wrote:

Thanx Ron, the first UDF did the trick!

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