VBA Command for Complex Select Case? (1 Viewer)

JMongi

Active member
Local time
Today, 12:39
Joined
Jan 6, 2021
Messages
802
Is there a VBA command that functions similarly to Select Case but without the simple individual source? A normal Select Case is like:

C-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.

Code:
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

Lifelong Learner
Local time
Today, 09:39
Joined
Mar 14, 2017
Messages
8,738
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

Active member
Local time
Today, 12:39
Joined
Jan 6, 2021
Messages
802
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

I’m here to help
Staff member
Local time
Today, 09:39
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried?
Code:
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

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2002
Messages
42,971
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

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2002
Messages
42,971
theDBguy showed you how but unless the conditions are mutually exclusive, execution will stop at the first true and skip the other tests.
 

JMongi

Active member
Local time
Today, 12:39
Joined
Jan 6, 2021
Messages
802
@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

I’m here to help
Staff member
Local time
Today, 09:39
Joined
Oct 29, 2018
Messages
21,358
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:
Code:
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

Active member
Local time
Today, 12:39
Joined
Jan 6, 2021
Messages
802
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

Lifelong Learner
Local time
Today, 09:39
Joined
Mar 14, 2017
Messages
8,738
Yeah ... it's like they depend on the Dev Community to do their real documentation for them..
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2002
Messages
42,971
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.
 

Users who are viewing this thread

Top Bottom