If then else if's won't go past the seventh one??

[jbarbara11]

I cannot get past this. I am guessing there is a better approach?:banghead:


Sub test() Dim PG As String


Dim depth As String
Dim bottomtime As String

depth = 35
bottomtime = 44


Select Case PG

Case depth < 36 And bottomtime < 11 Or depth < 41 And bottomtime < 10 Or depth < 51 And bottomtime < 8 Or depth < 61 And bottomtime < 7 Or depth < 71 And bottomtime < 6 Or depth < 81 And bottomtime < 5 Or depth < 91 And bottomtime < 5 Or depth < 101 And bottomtime < 4 Or depth < 111 And bottomtime < 4 Or depth < 121 And bottomtime < 4 Or depth < 131 And bottomtime < 4
MsgBox "A"
Case depth < 36 And bottomtime < 20 Or depth < 41 And bottomtime < 17 Or depth < 51 And bottomtime < 14 Or depth < 61 And bottomtime < 12 Or depth < 71 And bottomtime < 10 Or depth < 81 And bottomtime < 9 Or depth < 91 And bottomtime < 8 Or depth < 101 And bottomtime < 7 Or depth < 111 And bottomtime < 7 Or depth < 121 And bottomtime < 6 Or depth < 131 And bottomtime < 6 Or depth < 141 And bottomtime < 5
MsgBox "B"
Case depth < 36 And bottomtime < 26 Or depth < 41 And bottomtime < 23 Or depth < 51 And bottomtime < 18 Or depth < 61 And bottomtime < 15 Or depth < 71 And bottomtime < 13 Or depth < 81 And bottomtime < 11 Or depth < 91 And bottomtime < 10 Or depth < 101 And bottomtime < 9 Or depth < 111 And bottomtime < 8 Or depth < 121 And bottomtime < 7 Or depth < 131 And bottomtime < 7 Or depth < 141 And bottomtime < 6
MsgBox "C"
Case depth < 36 And bottomtime < 30 Or depth < 41 And bottomtime < 26 Or depth < 51 And bottomtime < 20 Or depth < 61 And bottomtime < 17 Or depth < 71 And bottomtime < 14 Or depth < 81 And bottomtime < 12 Or depth < 91 And bottomtime < 11 Or depth < 101 And bottomtime < 10 Or depth < 111 And bottomtime < 9 Or depth < 121 And bottomtime < 8 Or depth < 131 And bottomtime < 8 Or depth < 141 And bottomtime < 7
MsgBox "D"
Case depth < 36 And bottomtime < 33 Or depth < 41 And bottomtime < 28 Or depth < 51 And bottomtime < 22 Or depth < 61 And bottomtime < 18 Or depth < 71 And bottomtime < 16 Or depth < 81 And bottomtime < 14 Or depth < 91 And bottomtime < 12 Or depth < 101 And bottomtime < 11 Or depth < 111 And bottomtime < 10 Or depth < 121 And bottomtime < 9 Or depth < 131 And bottomtime < -9998 Or depth < 141 And bottomtime < 8
MsgBox "E"
Case depth < 36 And bottomtime < 37 Or depth < 41 And bottomtime < 32 Or depth < 51 And bottomtime < 25 Or depth < 61 And bottomtime < 20 Or depth < 71 And bottomtime < 17 Or depth < 81 And bottomtime < 15 Or depth < 91 And bottomtime < 13 Or depth < 101 And bottomtime < 12 Or depth < 111 And bottomtime < 11 Or depth < 121 And bottomtime < 10 Or depth < 131 And bottomtime < 9 Or depth < 141 And bottomtime < 9
MsgBox "F"
Case depth < 36 And bottomtime < 41 Or depth < 41 And bottomtime < 35 Or depth < 51 And bottomtime < 27 Or depth < 61 And bottomtime < 22 Or depth < 71 And bottomtime < 19 Or depth < 81 And bottomtime < 16 Or depth < 91 And bottomtime < 14 Or depth < 101 And bottomtime < 13 Or depth < 111 And bottomtime < 12 Or depth < 121 And bottomtime < 11 Or depth < 131 And bottomtime < 10 Or depth < 141 And bottomtime
MsgBox "G"
Case depth < 36 And bottomtime < 45 Or depth < 41 And bottomtime < 38 Or depth < 51 And bottomtime < 29 Or depth < 61 And bottomtime < 24 Or depth < 71 And bottomtime < 20 Or depth < 81 And bottomtime < 18 Or depth < 91 And bottomtime < 16 Or depth < 101 And bottomtime < 14 Or depth < 111 And bottomtime < 13 Or depth < 121 And bottomtime < 12 Or depth < 131 And bottomtime < 11 Or depth < 141 And bottomtime
MsgBox "H"
Case depth < 36 And bottomtime < 49 Or depth < 41 And bottomtime < 41 Or depth < 51 And bottomtime < 32 Or depth < 61 And bottomtime < 26 Or depth < 71 And bottomtime < 22 Or depth < 81 And bottomtime < 19 Or depth < 91 And bottomtime < 17 Or depth < 101 And bottomtime < 15 Or depth < 111 And bottomtime < 14 Or depth < 121 And bottomtime < -9998 Or depth < 131 And bottomtime
MsgBox "I"
Case depth < 36 And bottomtime < 53 Or depth < 41 And bottomtime < 45 Or depth < 51 And bottomtime < 34 Or depth < 61 And bottomtime < 28 Or depth < 71 And bottomtime < 23 Or depth < 81 And bottomtime < 20 Or depth < 91 And bottomtime < 18 Or depth < 101 And bottomtime < 16 Or depth < 111 And bottomtime < -9998 Or depth < 121 And bottomtime < 13 Or depth < 131 And bottomtime
MsgBox "J"
Case depth < 36 And bottomtime < 58 Or depth < 41 And bottomtime < 49 Or depth < 51 And bottomtime < 37 Or depth < 61 And bottomtime < 30 Or depth < 71 And bottomtime < 25 Or depth < 81 And bottomtime < 22 Or depth < 91 And bottomtime < 19 Or depth < 101 And bottomtime < 17 Or depth < 111 And bottomtime < 15 Or depth < 121 And bottomtime < 14 Or depth < 131 And bottomtime
MsgBox "K"
Case depth < 36 And bottomtime < 63 Or depth < 41 And bottomtime < 52 Or depth < 51 And bottomtime < 40 Or depth < 61 And bottomtime < 32 Or depth < 71 And bottomtime < 27 Or depth < 81 And bottomtime < 23 Or depth < 91 And bottomtime < 20 Or depth < 101 And bottomtime < 18 Or depth < 111 And bottomtime < 16 Or depth < 121 And bottomtime
MsgBox "L"
Case depth < 36 And bottomtime < 68 Or depth < 41 And bottomtime < 56 Or depth < 51 And bottomtime < 42 Or depth < 61 And bottomtime < 34 Or depth < 71 And bottomtime < 28 Or depth < 81 And bottomtime < 24 Or depth < 91 And bottomtime < 22 Or depth < 101 And bottomtime < 19 Or depth < 111 And bottomtime < 17 Or depth < 121 And bottomtime
MsgBox "M"
Case depth < 36 And bottomtime < 74 Or depth < 41 And bottomtime < 61 Or depth < 51 And bottomtime < 45 Or depth < 61 And bottomtime < 36 Or depth < 71 And bottomtime < 30 Or depth < 81 And bottomtime < 26 Or depth < 91 And bottomtime < 23 Or depth < 101 And bottomtime < 20 Or depth < 111 And bottomtime
MsgBox "N"
Case depth < 36 And bottomtime < 80 Or depth < 41 And bottomtime < 65 Or depth < 51 And bottomtime < 48 Or depth < 61 And bottomtime < 38 Or depth < 71 And bottomtime < 32 Or depth < 81 And bottomtime < 27 Or depth < 91 And bottomtime < 24 Or depth < 101 And bottomtime < 21 Or depth < 111 And bottomtime
MsgBox "O"
Case depth < 36 And bottomtime < 86 Or depth < 41 And bottomtime < 70 Or depth < 51 And bottomtime < 51 Or depth < 61 And bottomtime < 40 Or depth < 71 And bottomtime < 34 Or depth < 81 And bottomtime < 29 Or depth < 91 And bottomtime < 25 Or depth < 101 And bottomtime
MsgBox "P"
Case depth < 36 And bottomtime < 93 Or depth < 41 And bottomtime < 75 Or depth < 51 And bottomtime < 54 Or depth < 61 And bottomtime < 43 Or depth < 71 And bottomtime < 36 Or depth < 81 And bottomtime < 30 Or depth < 91 And bottomtime < 26 Or depth < 101 And bottomtime
MsgBox "Q"
Case depth < 36 And bottomtime < 101 Or depth < 41 And bottomtime < 80 Or depth < 51 And bottomtime < 58 Or depth < 61 And bottomtime < 45 Or depth < 71 And bottomtime < 37 Or depth < 81 And bottomtime < 31 Or depth < 91 And bottomtime
MsgBox "R"
Case depth < 36 And bottomtime < 109 Or depth < 41 And bottomtime < 86 Or depth < 51 And bottomtime < 61 Or depth < 61 And bottomtime < 48 Or depth < 71 And bottomtime < 39 Or depth < 81 And bottomtime
MsgBox "S"
Case depth < 36 And bottomtime < 118 Or depth < 41 And bottomtime < 92 Or depth < 51 And bottomtime < 64 Or depth < 61 And bottomtime < 50 Or depth < 71 And bottomtime < 41 Or depth < 81 And bottomtime
MsgBox "T"
Case depth < 36 And bottomtime < 128 Or depth < 41 And bottomtime < 98 Or depth < 51 And bottomtime < 68 Or depth < 61 And bottomtime < 53 Or depth < 71 And bottomtime
MsgBox "U"
Case depth < 36 And bottomtime < 140 Or depth < 41 And bottomtime < 105 Or depth < 51 And bottomtime < 72 Or depth < 61 And bottomtime < 55 Or depth < 71 And bottomtime
MsgBox "V"
Case depth < 36 And bottomtime < 153 Or depth < 41 And bottomtime < 112 Or depth < 51 And bottomtime < 76 Or depth < 61 And bottomtime < 56 Or depth < 71 And bottomtime
MsgBox "W"
Case depth < 36 And bottomtime < 169 Or depth < 41 And bottomtime < 121 Or depth < 51 And bottomtime < 81 Or depth < 61 And bottomtime
MsgBox "X"
Case depth < 36 And bottomtime < 189 Or depth < 41 And bottomtime < 130 Or depth < 51 And bottomtime
MsgBox "Y"
Case depth < 36 And bottomtime < 206 Or depth < 41 And bottomtime < 141 Or depth < 51 And bottomtime
MsgBox "Z"
End Select




End Sub

[SamT]

The Diver's depth time chart can't be calculated, it is all empirical.

A simpler to keyboard method with no limits is:

Function GetMessage(Depth As Long, BottomTime As Long) As String
Select Case Depth
Case Is = 36: GetMessage = Depth36(BottomTime)
Case Is = 41: GetMessage = Depth41(BottomTime)
End Select
End Function

Function Depth36(BTime As Long) As String
Select Case BTime
Case Is < 11: Depth36 = "Message A"
Case Is < 20: Depth36 = "Message B"
'
'
'Case Else: Get outtta da water NOW!!!
End Select
End Function
How it works:
Copy the "Case is =41" line and paste anew it for each depth. Change 2 numbers in each new line. Function GetMessage is now complete.

Complete Function Depth36 and paste a new copy under it. Select the new copy and use Ctrl+H to replace all instances of "Depth36" with Depth41." In each Case line, change the Bottom Time to suit. Repeat for each Depth Function.

[Paul_Hossler]

Select Case PG
Case depth < 36 And bottomtime < 11 Or depth < 41 And bottomtime < 10 Or depth < 51 And bottomtime < 8 Or depth < 61 And bottomtime < 7 Or depth < 71 And bottomtime < 6 Or depth < 81 And bottomtime < 5 Or depth < 91 And bottomtime < 5 Or depth < 101 And bottomtime < 4 Or depth < 111 And bottomtime < 4 Or depth < 121 And bottomtime < 4 Or depth < 131 And bottomtime < 4
MsgBox "A"


I'm trying to see how the Case Selector (i.e. the string PG) would match ANY of those Case's

Are you thinking of a massively complicated If/Then/ElseIf?

Paul

[Paul_Hossler]

Another really tedious way similar to SamT's



Option Explicit

Dim depth As String
Dim bottomtime As String

depth = 35
bottomtime = 44
Select Case depth
Case Is < 36
Call MakeMessage(bottomtime, _
Array(11, 20, 26, 30, 33, 37, 41, 45, 49, 53, 58, 63, 68, 74, 80, 86, 93, 101, 109, 118, 128, 140, 153, 169, 189, 206), _
Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"))

Case Is < 41
Case Is < 51
Case Is < 61
Case Is < 71
Case Is < 81
Case Is < 91
Case Is < 101
Case Is < 111
Case Is < 121
Case Is < 141
Case Else
End Select

End Sub

Sub MakeMessage(BT As Long, A As Variant, M As Variant)
Dim i As Long

For i = LBound(A) To UBound(A) - 1
If BT < A(i) Then
MsgBox M(i)
Exit Sub
End If
Next i

MsgBox M(UBound(A))
End Sub



Paul

[SamT]

Paul,

I think PG is just a placeholder variable.

@J Barbara,

Consider using lookup tables.
ColumnA is depths, B to (X) is BottomTimes.

Find Depth row, then BT on that row, BT Column-1 is the message number

No change you can make to the lookup table will require any code changes and the code itself is very small.

Member snb can write a oneline code to do that.

It can probably be done with a single Excel formula.

[Paul_Hossler]

@SamT



Paul, I think PG is just a placeholder variable.


Maybe, but I was thinking that things like this ...



depth < 36 And bottomtime < 11 Or depth < 41 And bottomtime < 10 Or depth < 51 And bottomtime < 8 Or depth < 61 And bottomtime < 7 Or depth < 71 And bottomtime < 6 Or depth < 81 And bottomtime < 5 Or depth < 91 And bottomtime < 5 Or depth < 101 And bottomtime < 4 Or depth < 111 And bottomtime < 4 Or depth < 121 And bottomtime < 4 Or depth < 131 And bottomtime < 4



... will only return True or False (since it looks like a boolean to me), and since PG was a String, I didn't see how a Select Case with 26 different Cases was going to work

Paul

[SamT]

Select method is a boolean method


VarToTest = 1
Select VarToTest
Case (Boolean Expression = True) 'VarToTest < 2 And 2 > VarToTest = True
Do This
End Select


If VarToTest (PG) is a String then numerical comparison raises Error and Errors <> True

[Paul_Hossler]

SamT -- To help me understand, can you put that in context of the OP's original Select Case statements?

Esp. the PG as string part

Paul

[SamT]

I think that "PG" is just a dummy place holder, kind of like I use "SomeVar" or "VarToTest."

The OP's entire Select method is based on comparing numbers. The variable PG was declared as a String. You just can't compare 42 and "Apple."

As you know, an If statement that only contains one line of code
If "Apple" = "Orange" Then
i = 2
End If Can be written as
If "Apple" = "Orange" Then i = 2

A Select Case statement is a specialized instance of the If statement.
SomeVar = "Apple"
Select SomeVar
Case "Orange"
i = 2
End Select is identical in results as the two If statements above. The Select Case does not have the seven level limitation as does the IF statement, and the Or operator is replaced with a comma.
If "Apple = "Orange" Or "Apple" = "Cherry" Then i = 2
Select SomeVar
Case "Orange", "Cherry"
i = 2
End Select One limitation of the Select Case that the If statement doesn't have is that all Case's in a Select must compare to the same SomeVar, but an If+Or (If+And) can compare different things.
If "Apple" = "Orange" Or SomeLongVar = 42 Then Select Case operators operators include less than (<), less than or equal to (<=), greater than (>), greater than or equal to (>=), not equal (<>), and equal (=), but as you can see, the = operator is implied. If specifically used, VBA will add "Is".
Select SomeVar
Case Is = "Orange"
i = 2
End Select

[Paul_Hossler]

Hmmm -- that will require some thought. Thanks for taking the time to explain



This is a simpler example of where I was coming from, trying to figure out how any of the Case would be selected


Option Explicit
Sub P()
Dim i1 As Long, i2 As Long
Dim sSomeVar As String
i1 = 1
i2 = 2

Select Case sSomeVar
Case (i1 = 1) And (i2 = 1) ' False
MsgBox "1-1"
Case (i1 = 1) And (i2 = 2) ' True
MsgBox "1-2"
Case (i1 = 2) And (i2 = 1) ' False
MsgBox "2-1"
Case (i1 = 2) And (i2 = 2) ' False
MsgBox "2-2"
Case Else ' Only one that hits
MsgBox "????"
End Select
End Sub





Paul

[fumei]

"A Select Case statement is a specialized instance of the If statement. "

mmmmmmmmmm, I am not sure I agree.

IF is boolean, as there is only ONE choice, evaluating to either true or false. Yes you can do multiple evaluations BUT each MUST be an individual evaluation statement.

Select Case does an actual (admitedly boolean) evaluation of MULTIPLE choices, each one evaluating to true or false

Select Case Yadda
Case 12, 34, 45, 67, 78 evaluates five choices.

Case 12 to 78 ' this evaluates all 66 choices.

There is no equivalent use of range of values in an IF statement.

Yeah, there are similarities, but you could argue that IF is a specialised instance of Select Case.

[SamT]

Yeah, there are similarities, but you could argue that IF is a specialised instance of Select Case.

True, But the IF came first.

Or was it the chicken. :)

[SamT]

@ Paul,

This badly constructed example (The Else is wrong) is the IF-Then-Else equivalent of your Select Case, except that in your example, you didn't assign a value to SomeVar, so Case Else is the only Case that matched anyway.


SomeVar = ""
If SomeVar = ((i1 = 1) And (i2 = 1)) Then MsgBox "1-1"
If SomeVar = ((i1 = 1) And (i2 = 2)) Then MsgBox "1-2"
If SomeVar = ((i1 = 2) And (i2 = 1)) Then MsgBox "2-1"
If SomeVar = (i1 = 2) And (i2 = 2) Then MsgBox "2-2"
Else MsgBox "????"
Note that in every case, it is SomeVar that is being compared.

Remember the i1 and i2 tests return Boolean values, so your example is really a series of "Case SomeVar = [True|False]."

Any match in a Select Case statement causes the code to exit the statement as soon as the selected code line is run. ie MsgBox "1-2". The Case Else matches anything and everything, even nothing, so it has to be last.

VBA is pretty tolerant, Try assigning the strings "true" and "false" to SomeVar and running your code on them.

[fumei]

Just want to point out that:

If SomeVar = ((i1 = 1) And (i2 = 1)) Then MsgBox "1-1"

returns an error 13 Type mismatch. There is no evaluation of ((i1 = 1) And (i2 = 1))


Also, Paul, as it is coded your code would ONLY go to the Case Else as sSomeVar is never given a value.

[Paul_Hossler]

SamT and fumei --

That was the point -- as written by the OP with the Select Case and using the string PG (which is never given a value) as the Case selector, the code will NEVER return a value based on 'depth' and 'bottom'

Yes, I understand comparing an unitialized string to a compund boolean would generate an Err13. Never said to do that. Also, the OP never assigned a value to the PG Case selector, which is why I asked originally.

Converting my simplified example to If/Then/Else construction, then the If's will correctly test booleans



Option Explicit
Sub P1()
Dim i1 As Long, i2 As Long

i1 = 1
i2 = 2

If (i1 = 1) And (i2 = 1) Then
MsgBox "1-1"
ElseIf (i1 = 1) And (i2 = 2) Then
MsgBox "1-2"
ElseIf (i1 = 2) And (i2 = 1) Then
MsgBox "2-1"
ElseIf (i1 = 2) And (i2 = 2) Then
MsgBox "2-2"
Else
MsgBox "????"
End If
End Sub




Using fumei's example, the 2 different constructs would be:




Sub fumei1()
Dim Yadda As Long
'if Yadda = 12 then "A" since it hits that case first
If (Yadda = 12) Or (Yadda = 34) Or (Yadda = 45) Or (Yadda = 67) Or (Yadda = 78) Then
MsgBox "A"
ElseIf (Yadda >= 12) And (Yadda <= 78) Then
MsgBox "B"
Else
MsgBox "C"
End If
End Sub

Sub fumei2()
Dim Yadda As Long
'if Yadda = 12 then "A" since it hits that case first
Select Case Yadda
Case 12, 34, 45, 67, 78
MsgBox "A"
Case 12 To 78
MsgBox "B"
Case Else
MsgBox "C"
End Select
End Sub




IMHO, and using my cracked crystal ball, I believe the OP was misunderstanding and was mixing If/Then/Else with Select Case concepts. However, there was also the possibility that there was something new for me to learn also. Hence my question

Paul

[SamT]

:thumb
:beerchug:

[fumei]

Yes I agree, there was definitely misunderstanding.

[Frosty]

Just on the philosophical note-- I don't think it's that helpful to try and think of Select Case and IF...ElseIf...Then...EndIf logical constructs are really anything but what they are: different logical constructs. Comparing the functionality of the two inevitably leads to preferred approaches when the functionality overlaps. Which is what I'm about to do :-D

You can code the same logic tree in different fashions, just like a house can be made of wood or concrete-- and still provide shelter from the rain. But there is a time and place for each, and a lot of time the decision on which is "better" for a given scenario has a lot to do with what modifications you might be making the *next* time you visit that logic construct-- i.e., the "how readable is this code?" question. And it also has to do with preferred coding methods (personally, I abhor single-line If statements... I prefer longer-but-more-readable code).

To the OP's original code-- forgetting whether Select Case or If...ElseIf...EndIf is a better approach, the better question is -- why are you running into the nested construct in the first place?

Because no one should ever write code with 7 nested And/Or constructs. It's completely unreadable and basically impossible to evaluate. My eyes glazed trying to even make sense of that. Break it apart into smaller chunks. Trying to have code evaluate what is, as SamT said, ultimately an empirical relational data-table is going to make anyone's eyes bleed. I'm sure there is some sort of CPU math-co-processor reason why there is a limitation in nested IF statements, but who cares-- it's hell to debug 3 nested if statements with multiple logical operators and parantheticals.

Except for the easiest If...ElseIf...EndIf structures, I prefer Select Case. And when I run into the limitation of Select Case only being able to evaluate a single value, I generally have the select case evaluate a function return (and sometimes the function might even return a custom enum, with a nice naming convention that makes the select case statement even easier to read).

As I'm not a diver, I'm not 100% sure what the initial problem is--
You can only be at a certain depth for a certain amount of time before you need to start making your way to the surface? And since you can only make your way to the surface at a given speed (or risk getting too much nitrogen in your blood), you're trying to write functions which will evaluate this and thus automatically take in "Depth" and "Time"?

This kind of sounds like a math and coding problem... and maybe the sort of thing being asked by a student, right?

I agree that I think some kind of look up table is the most efficient way to approach (and thus add more empirical data later), but I wouldn't want to give a student the answer for a homework assignment...

[fumei]

An articulate posting. And as usual I completely agree. I too have no fondness for single line If statements. I too pretty much only use If..Then for the simplest, ummm, cases. I prefer Select Case as i find them very easy to read. I too had my brain turn to mush even trying to follow all those nested statements...and I finally gave up.

IMHO readability is paramount. If you can not read the code, the code is essentially useless.

[Paul_Hossler]

I'm wondering why the OP is looking for a MS Word answer (really doesn't sound like a HW assignment).

I made a futile attempt to embed all of those numbers into a 3D VBA array, but I didn't like the difficulty of maintaining it and abandoned that approach

A MS Excel solution with the parameters in a worksheet would have been my first choice


Paul

[Frosty]

Paul: I agree. If I were to tackle this, I'd put it in a word table, and then analyze that table. Embedding the data in VBA is ugly no matter how elegant you try to do it.

[fumei]

Excel would be the way. Ot yeah a table, but yes the VBA is ugly.

[SamT]

I was thinking the OP is a teacher and was developing a test. Student divers have to learn that table by heart, even though they all carry at least a cheat card, and many use dive time calculators.