VBA Command for Complex Select Case?

[JMongi]

Is there a VBA command that functions similarly to Select Case but without the simple individual source? A normal Select Case is like:

Select Case MyNum
    Case 0 : MsgBox "The number is 0"
    Case 1 : MsgBox "The number is 1"
    Case > 1: MsgBox "The number is greater than 1"
    Else: MsgBox "Why did you enter a negative number you negative nelly?"
End Select

Where MyNum is compared to each expression.

I want to set up something similar but just with expressions.

Select Case
    Case Me.UnitNumber = ""
        'Do something
    Case Len(Me.UnitNumber) <> 7
        'Do something
    Case IsNum(Left(Me.UnitNumber, 2)) = False
        'Do something
    Case IsNum(Right(Me.UnitNumber, 4)) = False
        'Do something
    Case InStr(1,Me.UnitNumber, "-") <> 3
        'Do something
End Select

I can structure this using If/Then but I find this fake structure much easier to read and discern what's going on. Is there VBA function that might work this way?

 

[Isaac]

Unfortunately the VBA Case is not nearly as awesome as the tsql Case - wherein you could do that sort of thing, and more.
I think the If statements are the way to go -

But, if desired for clarity, you could set up a bunch of Boolean (or anything, really) variables .... Assign all their values appropriately....make sure they are EITHER mutually exclusive, or, sequenced just so, and then do a Case on the variables. Or maybe a single variable with a descriptive string value assigned to it - then a Case on that

 

[JMongi]

That's a bummer. I like the limited execution idea of the case statement too, i.e. ending the code block when an expression evaluates as true.

Trying my hand at some input validation on an AfterUpdate event.

 

[theDBguy]

Hi. Have you tried?

Select Case True
    Case Me.Textbox=1
        'do something
    Case Weekday(Date)=2
        'do something
    Case DCount("*","TableName")>0
        'do something
    etc.
End Select

 

[Pat Hartman]

You can use the Case as you want but ONLY if the conditions are mutually exclusive and it doesn't look like they are.

 

[Pat Hartman]

theDBguy showed you how but unless the conditions are mutually exclusive, execution will stop at the first true and skip the other tests.

 

[JMongi]

@theDBguy - No I haven't. Does that work? That would be cool, that's what I'm looking for. I did a lot of googling and couldn't find a single example with that structure. I thought I'd seen it before (probably here) but couldn't hit on it on my own.
@Pat Hartman - Yes, I'm looking for that type of functionality.

 

[theDBguy]

theDBguy showed you how but unless the conditions are mutually exclusive, execution will stop at the first true and skip the other tests.

Some more silly samples:

Select Case True
    Case Today is Monday AND I already had coffee
        'do something mundane
    Case Today is Tuesday AND it's raining AND I don't have an umbrella
        'go back to bed
    Case Today is my birthday OR Today is my wife's birthday
        'blow out the candle
End Select

 

[JMongi]

A little mini rant here...
Why, why, why are Microsoft's sample code blocks so lame and uninformative in their VBA support section? The samples given were so basic and practically out of a programming 101 textbook. I realize that can be very useful for the beginner. However, even one more complicated example tends to illustrate the syntax and usefulness of a command much better than a trivial example. Unless I missed it there is no example of using "Select Case True" which seems incredibly useful to know.

Ok, just needed to get that off my chest after many months of reading through their sometimes very helpful but often very unhelpful support section.

 

[Isaac]

@theDBguy that's awesome
But it's depressing not to include Friday

 

[Isaac]

Yeah ... it's like they depend on the Dev Community to do their real documentation for them..

 

[Pat Hartman]

Why, why, why are Microsoft's sample code blocks so lame and uninformative in their VBA support section? T

For the same reason that their sample databases are lame. They don't actually use the product.