weeknum formula in VBA script

[annupojupradeep]

Hi I am Pradeep. I am trying to write a weeknum formula in VBA script bur it not recognise in VBA script while I run the macro.


I am trying as =weeknum() which is not recognise in the excel macro.

Is there any VBA script for this Weeknum formula in excel macro?


Could you please help....

 

[Luke M]

Here's one way of doing it:

[pre]

Sub FindWeeknum()
Dim xDate As Double
Dim ReturnType As Integer
Dim xOutput As Integer

'What type of return do you want?
ReturnType = 1
'Set the date you want. I'm using today's date
xDate = Date

'Calculate
xOutput = Application.Evaluate("=WEEKNUM(" & xDate & "," & ReturnType & ")")

MsgBox "Weeknum is " & xOutput
End Sub

[/pre]

 

[NARAYANK991]

Hi Pradeep ,


Whenever you need to use an Excel worksheet function in VBA , the syntax is :


Application.WorksheetFunction.Excel_Worksheet_Function(...)


Thus , if you use =WEEKNUM(...) within your worksheet , in VBA , you would use :


Application.WorksheetFunction.WeekNum(...)


Check out the Excel VBA help on WorksheetFunction. WeekNum is one of the methods of this object.


Narayan

 

[SirJB7]

Hi, annupojupradeep!

If you want a clean and simple piece of VBA code, try this:

iWeek=DATEPART("ww",dDate)

With iWeek as integer and dDate as date or variant with a valid date.

Just one line, one function, no object references.

Hope it works for you.

Regards!