How to code a VBA procedure that outputs the date of every Monday of the year?

[mrzebo1]

I need a vba procedure that outputs the the date of every monday of the year. The output would look like

01/03/2011
01/10/2011
01/17/2011
.
.
.
12/26/2011

Any help would be greatly appreciated.

[mshmyob]

What have you tried so far?

A hint if you haven't even attempted anything is to create a loop through all days starting with Jan 1, 2011 and ending on Dec 31, 2011. As you loop through then use the WEEKDAY function on each date.

Try it and post back your code if you have any problems.

cheers,

[hype261]

A more efficient method would be to start on 01 January of what ever year and find the first Monday of the year. All the other Mondays are going to be 7 days for there.

[mshmyob]

I can't really see there being much if any of a performance (ie: efficiency) difference between using the Weekday function over the Dateadd function but each to there own.


cheers,

PS. - Now that I think about it you may be right. Both solutions would need to use the Dateadd function but mine would also add the step of using the Weekday function each iteration where yours would use it for a maximum of 7 iterations. Even though the performance issue would not be noticable on just 365 iterations I believe yours should be the way to go.

[NeoPa]

More than that we won't do for you until you show that you have attempted this yourself first. We are not a free coding service. We are here to help with answers to questions - not to do your work for you.

[ADezii]

I think the whole efficiency angle is a mute point, since, using a combination of the Weekday() and DateAdd() Functions, over 10 trials, took an Average of 22 Milliseconds to execute (extracting Mondays between the Range 1/1/2011 and 12/31/2011.

P.S. - I know, too much time on my hands! (LO(L).

[mshmyob]

That is what I was saying about there being no performance difference but I will concede the point that hype's method might be slightly better in the sense of coding effieciency.

cheers,

[ADezii]

Yep, hype261's more efficient approach took an Average of 15.5 Milliseconds as opposed to 22 over 10 Trial Runs.

[NeoPa]

This is actually a good point (It deserves consideration). Both points in fact.

1. It is important when looking at a project to bear in mind the real impact of a decision. 22 ms (or about 1/500 second) will not be noticeable to anybody using a project.
2. Efficiency of code is always worth striving for. Even when the difference isn't noticeable. Getting an understanding of what is efficient and what is untidy is always worth having. It is particularly important when the same code is run over and again within other code. A routine taking 20 ms (1/500 second) extra won't have too much of an effect when run once, but when it is run for every record of a 500 thousand recordset, then it starts to be significant (1,000 seconds = 16 minutes).

So, know how much of an impact something makes, but also try to code as cleanly and efficiently as possible anyway. The attitude will probably pay dividends later on in situations where it may make more of a difference.

[NeoPa]

ADezii:
Yep, hype261's more efficient approach took an Average of 15.5 Milliseconds as opposed to 22 over 10 Trial Runs.

Far, FAR, too much time on your hands clearly :-D