# Thread: A Better Way (XP)

1. ## A Better Way (XP)

I test a variable to determine the day of the week and use code similar to:

If oType = "SUN" or oType = "Mon" or oType = "TUE" ...... Then blah blah blah

Is there a better way to write the if statement so I don't have to declare "oType" for each day of the week?

Thanks,
John

2. ## Re: A Better Way (XP)

Not sure what you are after completely.
You could use a select case

you could test the weekday value (1-7) so you could use ranges (instead of Ors)

If this is not what you are looking for, could you elaborate?

Steve

3. ## Re: A Better Way (XP)

Steve,

My goal is to write a more efficient line of code; the less line the better.

Select Case looks like a better way. I'll me fiddle with it and let you know.

Thanks,
John

4. ## Re: A Better Way (XP)

You may want to use CHOOSE function? Example: Col A is a series of dates, columns B thru D use these formulas:

A2: 5/14/2004 (constant)
B2: =WEEKDAY(A2,1) (returns 6)
C2: =TEXT(B2,"ddd") (returns "Fri")
D2: =CHOOSE(WEEKDAY(A2,1),"Sunday","I Don't Like Mondays","Tuesday PM","Wednesday AM","Thursday","TGIF Friday","Sat Nite Fever") (returns "TGIF Friday")

You can test this by autofilling A22 down a few dozen rows & checking results. Column D should display the text indicated in formula for the day of week for date in Col A. Note that the 2nd arg for WEEKDAY, Return_Type, determines which day of week is defined as "1"; 1 means week starts with Sunday (default in VBA). TEXT function used for test purposes to display day of week as text rather than number. To start week with a day other than Sunday, modify formulas accordingly.

HTH

5. ## Re: A Better Way (XP)

Are you doing this in VBA? I obviously wasn't paying attention. Anyway, the formula would translate to VBA like this example:

? Choose(Weekday(Date(),1),"Sunday","I Don't Like Mondays","Tuesday PM","Wednesday AM","Thursday","TGIF Friday","Sat Nite Fever")
TGIF Friday

? Choose(Weekday(#5/17/2004#,1),"Sunday","I Don't Like Mondays","Tuesday PM","Wednesday AM","Thursday","TGIF Friday","Sat Nite Fever")
I Don't Like Mondays

The VBA Choose & Weekday functions function pretty much the same way as the equivalent workbook functions. The 2nd arg for Weekday function, 1, also denotes Sunday is first day of week (default). Refer to VBA "Help" for more detail on Choose function.

HTH

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•