I found this confusing: SELECT vs NESTED IF

[ChrisGreaves]

I think of VBA as being smart enough to evaluate various forms of data correctly, coercing them into suitable types without explicit programmer-devised coercions.

Code: Select all

Sub TESTWin7Word2003()
    Dim strDT1 As String
    strDT1 = "02/17/2020 10:42:21 AM"
    Dim strDT2 As String
    strDT2 = "02/17/2020 10:39:53 AM"
    
    Dim dt As Date
    dt = DateValue(strDT1) + TimeValue(strDT1)  ' Date/Time for this scheduled event
    Dim dtNow As Date
    dtNow = DateValue(strDT2) + TimeValue(strDT2) ' use a constant date for the select statement
    Debug.Print dt, dtNow, dt > dtNow
    Select Case dt
        Case (dt = 0) ' Nothing to do with this record; ignore it completely
            Debug.Print "Nothing to do"
        Case (dt <= dtNow) ' This record is past due; run it now
            Debug.Print "RUNning "
        Case (dt > dtNow) ' This record is immature; retain it as a Remainder
            Debug.Print "Postponed"
    End Select
End Sub

I tried the code shown above with three different data types: (1) type Date, (2) type <none> (so I think, Variant) and type Double.
I obtained the same results every time: Not one of the three CASE statements executes (I set BOTH variables to the same type each time)
I tried first without the parentheses, and then using parentheses to force an in-line calculation after finding that dt>dtnow returned the value True.

For all three types, not one of the CASE statements evaluates to True.
I find this strange, too.

“Debug.Print dt, dtNow, dt > dtNow” shows that the relationship returns True, but this apparently not recognised within the Select statement by the third Case statement

(later) I am tired of finding bugs, so when I woke from my nap I looked closely at the help files.
My understanding of the SELECT statement is in error.
I grew up with Nested Ifs, once I got past FORTRAN II, and have little experience in SELECT/CASE.

I think in terms of nested-ifs, and tried to rewrite my 3-way IF statement as a 3-Case SELECT statement. That doesn’t work.
In the attached TESTWin7Word2003.txt (and upgraded) text file you will find:-
(1) My faulty use of SELECT
(2) My original nested-IF
(3) A better but limited) use of SELECT
The contents of my Debug Window are presented as an example of output
Cheers
Chris

[HansV]

Here is another way:

Code: Select all

Sub TESTWin7Word2003()
    Dim strDT1 As String
    strDT1 = "02/17/2020 10:42:21 AM"
    Dim strDT2 As String
    strDT2 = "02/17/2020 10:39:53 AM"
    
    Dim dt As Date
    dt = CDate(strDT1)  ' Date/Time for this scheduled event
    Dim dtNow As Date
    dtNow = CDate(strDT2) ' use a constant date for the select statement
    Debug.Print Format(dt, "yyyy/mm/dd hh:mm:ss"), Format(dtNow, "yyyy/mm/dd hh:mm:ss")
    
    Select Case True
        Case dt = 0 ' Nothing to do with this record; ignore it completely
            Debug.Print "Nothing to do"
        Case dt <= dtNow ' This record is past due; run it now
            Debug.Print "RUNning "
        Case dt > dtNow ' This record is immature; retain it as a Remainder
            Debug.Print "Postponed"
    End Select
End Sub

[ChrisGreaves]

Here is another way:

Code: Select all

    Select Case True

Thanks, Hans. (Although I shall have to think about this a bit)
Cheers
Chris

[HansV]

Select Case True is a bit uncommon, but it is perfectly valid. You can use it when you have disparate conditions.

Code: Select all

    Select Case True
        Case condition1
            statement1
        Case condition2
            statement2
        Case condition3
            statement3
        ...
    End Select

The conditions can be any expression that evaluates to True or False.
VBA will execute the statement below the first condition that evaluates to True.

[ChrisGreaves]

Select Case True is a bit uncommon, ...

Thanks Hans.
I am sure that I am not alone in pondering the question :-
Yes, But, how do YOU come up with these answers?

Frankly, "SELECT TRUE" seems like a, well, a useless truism!

Cheers and thanks
Chris

[HansV]

I don't remember when or where I learned this trick. It may have been from Rory...

[Doc.AElstein]

Hi Chris

At fist glance, everything makes perfect sense to me, and I don’t find anything strange or confusing in what is happening.
( I am not saying that makes me clever. Quite the opposite: - more often than not, I miss the point totally, so get the right answer , by mistake … if you understand my meaning … a problem I often have is that I am too stupid to understand the logic of more intelligent people getting it wrong…. ( The best Experts are correct when they are ( very rarely ) wrong.. , I can’t understand that. They can. )
_.____________

So:-
This not returning any Debug.Print output at your Case points is exactly as I would expect

Code: Select all

    Dim strDT1 As String
    strDT1 = "02/17/2020 10:42:21 AM"
    Dim strDT2 As String
    strDT2 = "02/17/2020 10:39:53 AM"
    
    Dim dt As Date
    dt = DateValue(strDT1) + TimeValue(strDT1)  ' Date/Time for this scheduled event
    Dim dtNow As Date
    dtNow = DateValue(strDT2) + TimeValue(strDT2) ' use a constant date for the select statement
    Debug.Print Format(dt, "yyyy/mm/dd hh:mm:ss"), Format(dtNow, "yyyy/mm/dd hh:mm:ss")
    
    Select Case dt
        Case (dt = 0) ' Nothing to do with this record; ignore it completely
            Debug.Print "Nothing to do"
        Case (dt <= dtNow) ' This record is past due; run it now
            Debug.Print "RUNning "
        Case (dt > dtNow) ' This record is immature; retain it as a Remainder
            Debug.Print "Postponed"
    End Select

As I see it, with or without the extra ( ) the Select Case point and also the 3 Case points are going to evaluate what you wrote there, at these 4 points:-
___ dt
(dt = 0)
(dt <= dtNow)
(dt > dtNow)
So, at those 4 points VBA returns True, False , or something like 17.02.2020 10:42:21
___ dt ‘ returns something like 17.02.2020 10:42:21
(dt = 0) ‘ returns False
(dt <= dtNow) ‘ returns False
(dt > dtNow)’ returns True

( For all I know, it might even do some of that evaluating at compiling or at the start of the macro even before it gets there. I am not sure)
In any case, ( or in any Select Case :) ) , your coding actually looks like this

Code: Select all

Select Case dt
        Case False ' Nothing to do with this record; ignore it completely
            Debug.Print "Nothing to do"
        Case False ' This record is past due; run it now
            Debug.Print "RUNning "
        Case True 
            Debug.Print "Postponed"

Or , more actually, it is doing this

Code: Select all

Select Case 17.02.2020 10:42:21
        Case False ' Nothing to do with this record; ignore it completely
            Debug.Print "Nothing to do"
        Case False ' This record is past due; run it now
            Debug.Print "RUNning "
        Case True 
            Debug.Print "Postponed"

In that last code snippet, you are trying to find a match to 17.02.2020 10:42:21. But you are only giving it the choices of False, False, and True,

If dt were a Boolean and set to True, then the Case True would be selected. ( Debug.Print "Postponed" ).
So Hans idea to get your thing to work makes sense.
Either of these would do the same

Code: Select all

 Dim Boo As Boolean: Let Boo = True
    Select Case Boo
        Case (dt = 0) ' Nothing to do with this record; ignore it completely
            Debug.Print "Nothing to do"
        Case (dt <= dtNow) ' This record is past due; run it now
            Debug.Print "RUNning "
        Case (dt > dtNow) ' This record is immature; retain it as a Remainder
            Debug.Print "Postponed"
    End Select

Code: Select all

 
    Select Case True
        Case (dt = 0) ' Nothing to do with this record; ignore it completely
            Debug.Print "Nothing to do"
        Case (dt <= dtNow) ' This record is past due; run it now
            Debug.Print "RUNning "
        Case (dt > dtNow) ' This record is immature; retain it as a Remainder
            Debug.Print "Postponed"
    End Select

In my naive way of thinking about it, you are trying to match what is placed after the Select Case with what is after the Cases.
Its as simple as that.

_._____

In many situation in VBA : if you give it a value, it takes it; If you give it a variable it takes the value in it; If you give it a x>y type thingy , then it takes the True or False evaluation.

So in your case…
If you put a value in, it takes that
If you put a variable in, it takes the value of it
If you put a logical expression in, then it takes the result of that , True or False
_.______________

A few other examples might help get across what I am trying to say…

Code: Select all

 Sub SelectNutCases()
Dim Nut1 As Long, Nut2 As String, Nut3 As Double
Let Nut1 = 1: Nut2 = "2": Nut3 = 3
    Select Case 3
     Case Nut1
      Debug.Print "Won't see this"
     Case Nut2
      Debug.Print "Won't see this"
     Case Nut3
      Debug.Print "You'll see this"
     End Select
End Sub

It is just a case of matching stuff.
Your original macro was trying to match a date time string value to either
Fasle or
False or
True
So you had no match found.
_._________________

…., not one of the CASE statements evaluates to True.

That is not quite right
The third Case point evaluated to True
ThirdCaseIstTue.JPG : https://imgur.com/5pVbruu" onclick="window.open(this.href);return false;

ThirdCaseIstTrue.JPG

But in that Third Case bit you were you were trying to match True to something like 17.02.2020 10:42:21 – that is not the matched case
In Hans modification you are trying at that third Case point to match True to the True which Hans put at the Select Case point – that is the matched case.

_.______________

As I understand it ,the VBA Select Case statement thing, works such as to pick the case that matches.
You could try to match a False , if you like

Code: Select all

 Sub SelectFalse()
    Select Case False
     Case 1 = 1 ' is True
      Debug.Print "Won't see this"
     Case UCase("tWo") = "TWO" ' is True
      Debug.Print "Won't see this"
     Case "two" = "TWO" ' is False
      Debug.Print "You'll see this"
     End Select
End Sub

_._________________________

Alan

_._____
P.s.

..... It may have been from rory...

possible, I noticed he sometimes knows Excel stuff quite good.

[HansV]

Only sometimes?

[Doc.AElstein]

He comes under that category of Best Expert which I mentioned at the start, so it means I guess he is always correct, which I don’t always understand :)

_.________________
BTW , Chris, ....there is a third alternative to the Select Case and nested Ifs.
( I have not ever got around to looking at that in detail yet though
( http://excelmatters.com/2019/03/19/ifs- ... functions/" onclick="window.open(this.href);return false; ) )

[HansV]

IFS is an Excel worksheet function, not a generic VBA function!

[Doc.AElstein]

Correct . I am confusing it with something similar in VBA which is a third alternative to Select Case or nested IFs
I know there is one. It works similar to the Excel IF , a one code line thing...
I have used it.
But I can’t seem to find it just now

EDIT : Found it - I was confusing IFS with IIf https://docs.microsoft.com/en-us/office ... f-function" onclick="window.open(this.href);return false; , http://www.excelfox.com/forum/showthrea ... ion-in-VBA" onclick="window.open(this.href);return false;

Code: Select all

Sub IIf_thingy()
Dim Answer As String
Dim Nm As Long: Let Nm = 2
 Let Answer = IIf(Nm = 1, "One", IIf(Nm = 2, "Two", "Three"))
 MsgBox prompt:=Answer
Let Nm = 2: MsgBox prompt:=IIf(Nm = 1, "One", IIf(Nm = 2, "Two", "Three"))
Let Nm = 3: MsgBox prompt:=IIf(Nm = 1, "One", IIf(Nm = 2, "Two", "Three"))
End Sub

Thanks for catching my mistake. Lucky I am not an expert, so I have it correct and got the right answer.

[HansV]

VBA has a function IIf:

Code: Select all

    x = IIf(y = 1, "A", IIf(y = 2, "B", IIf(y = 3, "C", "Z")))

And also a function Switch, more or less comparable to IFS:

Code: Select all

    x = Switch(y = 1, "A", y = 2, "B", y = 3, "C", y > 3, "Z")

And a function Choose:

Code: Select all

    x = Choose(y, "A", "B", "C")

[Doc.AElstein]

Thanks, I knew the IIfs and Choose, but the Switch is a new one on me




Edit some Refs..
http://www.excelfox.com/forum/showthrea ... rmula-Help!" onclick="window.open(this.href);return false;!

[ChrisGreaves]

Having thought about it a bit … NOW I get it!
When I think of the SELECT statement as an alternative to a nested IF:-

SelectCaseTrue.png

I would read this as:-
If True it is that “dt=0”
Else
If True it is that “dt<=dtNow”
Else
If True it is that “dt>dtNow”

That is, the SELECT statement is always testing between a variable/value stated immediately after the word SELECT, and each of the following terms stated immediately after the CASE alternatives.
In the example above <blue> is being compared to each of the <purple> (until a match is made, of course)

HV> If True it is that “dt<=dtNow”
Yes. Yes! YES!! I see it as my being allowed to have any valid expression either side of the If <epression1> is equal to <expression2>, so instead of thinking “If this is TRUE” I can say “If TRUE is this” (or that, or the other).
Makes perfect sense to me now.
Until your next pronouncement on this Case (grin)

Cheers
Chris

[ChrisGreaves]

... you are trying to match what is placed after the Select Case with what is after the Cases.

Yes. My main stumbling block was my issuance of “Select Case dt” which was probably an expression of my fuddled brain at the time thinking “I have got to test this dt variable now; so I’ll start off by saying that I am interested in the dt-thingy, let’s start by writing “dt”.
And from there things just unravelled.
Cheers
Chris

[Doc.AElstein]

You know I guess about the ElseIf. Pretty well identical to the Select Case

Code: Select all

    Select Case x
     Case 1
     
     Case 2
     
     Case Else
     
    End Select
    
    
    If x = 1 Then
    
    ElseIf x = 2 Then
    
    Else
    
    End If
    

I think I read a few academic theoretical discussions that the Select Case should be quicker / more efficient. But most people find in the practice that it can go both ways. Most think the Select Case looks clearer. I personally find the Ifs Then Else ElseIfs EndIf more beautiful in my coding. My coding is always beautiful.

I also like to include the Else even when I don’t need it

Lots of people do this

If … Then

EndIf

I always include the Else. I usually put a ‘Comment there to help later reference to what is going on, and it is easier to modify later when I might want to use the Else

If ….. Then

Else ‘ case it was not a ……
EndIf

That is much prettier, and beautiful.


I also like to sometimes do the long single line

If …. Then do this : that : and the other : and lots more while I am here hidden to the right where people cant see me …..

There are a few subtle cases where the long line does not work quite the same as like…_

If ….. Then
do this
that
and the other
Else
EndIf

_... I can’t remember them just now,, but when I do I will edit and note them here, because this Thread has become a good reference of the different ways, including the ones Hans mentioned that a lot of people don’t know about.


You can also do like

If …. Then this : and that Else this : and the other



Alan