469,362 Members | 2,302 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,362 developers. It's quick & easy.

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

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.
Dec 2 '10 #1

✓ answered by 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.

11 3184
mshmyob
904 Expert 512MB
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,
Dec 2 '10 #2
hype261
207 100+
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.
Dec 3 '10 #3
mshmyob
904 Expert 512MB
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.
Dec 3 '10 #4
NeoPa
32,185 Expert Mod 16PB
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.
Dec 5 '10 #5
ADezii
8,800 Expert 8TB
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).
Dec 5 '10 #6
mshmyob
904 Expert 512MB
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,
Dec 5 '10 #7
ADezii
8,800 Expert 8TB
Yep, hype261's more efficient approach took an Average of 15.5 Milliseconds as opposed to 22 over 10 Trial Runs.
Dec 5 '10 #8
NeoPa
32,185 Expert Mod 16PB
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.
Dec 5 '10 #9
NeoPa
32,185 Expert Mod 16PB
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
Dec 5 '10 #10
ADezii
8,800 Expert 8TB
The really funny part, is that I do much of my posting while at work! (LOL).
Dec 5 '10 #11
NeoPa
32,185 Expert Mod 16PB
You're just too prolific ADezii my friend :-)
Dec 5 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by phforum | last post: by
2 posts views Thread by shapper | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.