VBA To return week number?

  • hi all,


    Has anyone any idea how to get VBA to give the current week number?


    I know i can use the analysis toolpak in excel but i need this form I'm creating to give me the correct week number even if the analysis toolpak isn't installed on a machine. does anyone know of a workaround?


    Regards


    Grasshoppa
    :thanx:

  • Re: VBA To return week number?



    Same as WEEKNUM function, not ISO?


    =1+INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)

  • Re: VBA To return week number?


    Is there a typo in that post?


    VBA kicks it out as "expected )".
    I'm not familar with these expressions!!!!!!!!


    :thanx: anyhow.

  • Re: VBA To return week number?


    Quote from Grasshoppa

    Is there a typo in that post?


    VBA kicks it out as "expected )".
    I'm not familar with these expressions!!!!!!!!


    :thanx: anyhow.

    Worked fine for me. Maybe there was a pasting error!!!!!!!!!!!!!!!!!!

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: VBA To return week number?


    Quote from royUK

    Bob's solution works OK, but it's a formula not VBA, which is what Grasshoppa was looking for.


    Ah so, Grasshoppa which is why pasting the Excel formula into VBA wouldn't work. :)


    Guess that old saying, "two wrongs don't make a right" is true. Nice link though.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!