Weeks in a given month

[engineer69]

I have been unable to get VBA (not Excel formulas) on how to find the amount of weeks in a given month. Example: for the date of 1/1/2018 there are 5 weeks and for 2/1/2018 there are 4 weeks.

This Excel formula seems to work "=4+(DAY(A21-DAY(A21)+35)< WEEKDAY(A21-DAY(A21)-3))", but I need it in VBA. Has anybody found a way to do this?

 

[bs0d]

Sheets("Sheet1").Range("A1").Formula = "=4+(DAY(A21-DAY(A21)+35)< WEEKDAY(A21-DAY(A21)-3))"

 

[engineer69]

Yes, but I am trying to get this number in a variable in VBA.

 

[bs0d]

Sub test()
Dim myVar As Long
Dim refCell As Date

refCell = Sheets("Sheet1").Range("A21").Value

myVar = 4 + ((Day(refCell - Day(refCell) + 35)) < Weekday(refCell - Day(refCell) - 3))

MsgBox myVar

End Sub

 

[engineer69]

Perfect! I was incorrectly declaring variables (i.e. As Long and As Date). Many thanks!!!

 

[engineer69]

Now this is getting weird. If I use the formula in a cell 1/1/2018 returns 5 weeks, which is correct. If I use a variable I get 3 weeks???

 

[mse330]

If you want to use it in your VBA code without referencing to your sheet, this is a UDF

Sub test()

Dim Wks As Long
Wks = No_Of_Weeks(#1/12/2017#)

Debug.Print Wks

End Sub

Function No_Of_Weeks(dDate As Date) As Byte

No_Of_Weeks = IIf((Day(dDate - Day(dDate) + 35)) < WorksheetFunction.Weekday(dDate - Day(dDate) - 3), 5, 4)

End Function

 

[engineer69]

Works! I need to learn a bit more . . . why this works in a UDF? Thanks for the quick post!

 

[bs0d]

The formula in my code was incorrect. Also, it doesn't have to be a UDF, but makes it easier to use in your Sheet(s) if you so desire.

 

[mse330]

As bs0d said, it doesn’t have to be a UDF to work but I thought this is what you were after to use in VBA code. You can still use the same line below in any code without a UDF & should get the same results

‘ Just define the dDate & assing a value to it
No_Of_Weeks = IIf((Day(dDate - Day(dDate) + 35)) < WorksheetFunction.Weekday(dDate - Day(dDate) - 3), 5, 4)

Glad to help