Solved My Case Statement doesn't work properly (1 Viewer)

S

silversun

Registered User.
Local time
Today, 11:43
Joined
Dec 28, 2012
Messages
204
Hi everybody,
I have a case statement in my project that is not working and I can't see the issue. Anybody can help me please?
Private Function myBar(riskRate)
Select Case (riskRate)

Case (0 < riskRate <= 216)

Me.Box34.Visible = True
Me.myArrow.Top = 4000

Case (216 < riskRate <= 432)
Me.Box33.Visible = True
Me.myArrow.Top = 3700

Case (432 < riskRate <= 684)
Me.Box32.Visible = True
Me.myArrow.Top = 3400

Case (684 < riskRate <= 864)
Me.Box31.Visible = True
Me.myArrow.Top = 3100

Case (864 < riskRate <= 1080)
Me.Box30.Visible = True
Me.myArrow.Top = 2900

Case (1080 < riskRate <= 1296)
Me.Box29.Visible = True
Me.myArrow.Top = 2600

Case (1296 < riskRate <= 1512)
Me.Box28.Visible = True
Me.myArrow.Top = 2300

Case (1512 < riskRate <= 1728)
Me.Box27.Visible = True
Me.myArrow.Top = 2000

Case (1728 < riskRate <= 1944)
Me.Box26.Visible = True
Me.myArrow.Top = 1700
Case Else
Me.Box25.Visible = True
Me.myArrow.Top = 1400

End Select
End Function
Click to expand...
When I call my function, any number that is assigned to riskRate (for example 1700). goes through case statement function and always executes "Case Else" clause only. Using breakpoints I am able to see the progress as well as wrong execution with any given number. It passes all cases and no matter what it executes the last statement.
Am I allowed to use Boolean expression in each case statement? What is the solution then?
Thank you for your time and fast respond
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:43
Joined
May 7, 2009
Messages
19,231
and "And" to your Expression and Evaluate from the Greatest
value down to smallest:

...
Case (1728 > riskRate) 'And rskRate <= 1944)
...
Case (1512 > riskRate And riskRate <= 1728)
...
etc.
 
vba_php

vba_php

Forum Troll
Local time
Today, 13:43
Joined
Oct 6, 2019
Messages
2,880
silversun said:
Am I allowed to use Boolean expression in each case statement?
Click to expand...
your code is NOT using boolean logic. that is 0/-1. or TRUE/FALSE. you are using operators. try something like this:
Code: 
select case riskRate
   case < number (smallest number)

   case < number 2 (next smallest number)

   case else
      'do something
end select
you could also do something like this, I would guess:
Code: 
case is > [smallest number] and < [next largest number]
.....
.....
I hope that's right. I haven't written basic in a while.
 
P

plog

Banishment Pending
Local time
Today, 13:43
Joined
May 11, 2011
Messages
11,638
Check the documentation:

docs.microsoft.com

Select Case statement (VBA)

Office VBA reference topic
docs.microsoft.com

You don't use the variable in each Case line, you use it at the start then every Case uses it implicitly. Ranges can be done like so:


Case 1 To 216
 
S

silversun

Registered User.
Local time
Today, 11:43
Joined
Dec 28, 2012
Messages
204
plog said:
Check the documentation:

docs.microsoft.com

Select Case statement (VBA)

Office VBA reference topic
docs.microsoft.com

You don't use the variable in each Case line, you use it at the start then every Case uses it implicitly. Ranges can be done like so:


Case 1 To 216
Click to expand...
Hi
I used two different formats as you and arnelgp suggested but it is not working. Can you please help?
Code: 
Private Function myBar(test_riskRate)
Select Case test_riskRate
Case (2160 < test_riskRate And test_riskRate <= 1944)
    Me.myArrow.Visible = True
    Me.Box34.Visible = True
    Me.myArrow.Top = 1400
.............
.............

    Me.Box28.Visible = True
    Me.myArrow.Top = 3200
Case (647 < test_riskRate And test_riskRate <= 432)
    Me.myArrow.Visible = True
    Me.Box27.Visible = True
    Me.myArrow.Top = 3500
Case (431 < test_riskRate And test_riskRate <= 216)
    Me.myArrow.Visible = True
    Me.Box26.Visible = True
    Me.myArrow.Top = 3800
Case Else
    Me.myArrow.Visible = True
    Me.Box25.Visible = True
    Me.myArrow.Top = 4000
End Select
End Function

Also I modified as following without success run:
Code: 
Private Function myBar(test_riskRate)
Select Case test_riskRate

Case 2160 To 1944
    Me.myArrow.Visible = True
    Me.Box34.Visible = True
    Me.myArrow.Top = 1400

Case 1943 To 1728
    Me.myArrow.Visible = True
    Me.Box33.Visible = True
    Me.myArrow.Top = 1700
 ....
 ....
Case 647 To 432
    Me.myArrow.Visible = True
    Me.Box27.Visible = True
    Me.myArrow.Top = 3500
    
Case 431 To 216
    Me.myArrow.Visible = True
    Me.Box26.Visible = True
    Me.myArrow.Top = 3800
Case Else
    Me.myArrow.Visible = True
    Me.Box25.Visible = True
    Me.myArrow.Top = 4000
    
End Select
End Function

What do you think?
Thank you
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:43
Joined
Feb 28, 2001
Messages
27,137
You can also try this:

Case (0 < riskRate <= 216) can be done as Case Between 1 and 216

Note that the "Between" operator is inclusive of the two limits but your statement was NOT inclusive of 0
 
P

plog

Banishment Pending
Local time
Today, 13:43
Joined
May 11, 2011
Messages
11,638
By not working you mean it always hits the else statement? I would throw a MsgBox() in the else which spits out the value of test_RiskRate and its type (https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function).

2 observations you should fix even though they may not fix your issue:

1. test_RiskRate is a string. In your function header you should explicitly declare test_RiskRate as an integer/double

2. Your function doesn't return anything, it does things. It should be a Sub.
 
S

silversun

Registered User.
Local time
Today, 11:43
Joined
Dec 28, 2012
Messages
204
The_Doc_Man said:
You can also try this:

Case (0 < riskRate <= 216) can be done as Case Between 1 and 216

Note that the "Between" operator is inclusive of the two limits but your statement was NOT inclusive of 0
Click to expand...
Thanks for your help but it is not working at all.
With this last format I get all errors on my case statements
1586499959577.png

I changed the format to "Integer" and follow all you and others said but it is not working.
When I say it is not working I mean it only executes the last "Else" clause statement.
 
S

silversun

Registered User.
Local time
Today, 11:43
Joined
Dec 28, 2012
Messages
204
plog said:
By not working you mean it always hits the else statement? I would throw a MsgBox() in the else which spits out the value of test_RiskRate and its type (https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function).

2 observations you should fix even though they may not fix your issue:

1. test_RiskRate is a string. In your function header you should explicitly declare test_RiskRate as an integer/double

2. Your function doesn't return anything, it does things. It should be a Sub.
Click to expand...
Yes. When I say it is not working I mean it only executes the last "Else" clause statement.
I changed the format to integer from single but still not working! I changed the function to "Sub" as well.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:43
Joined
May 7, 2009
Messages
19,231
reverse the order:
Code: 
Case 1944 to 2160
    Me.myArrow.Visible = True
    Me.Box34.Visible = True
    Me.myArrow.Top = 1400

Case 1728 To 1943
    Me.myArrow.Visible = True
    Me.Box33.Visible = True
    Me.myArrow.Top = 1700
 ....
 ....
Case 432 To 647
    Me.myArrow.Visible = True
    Me.Box27.Visible = True
    Me.myArrow.Top = 3500
    
Case 216 To 431
    Me.myArrow.Visible = True
    Me.Box26.Visible = True
    Me.myArrow.Top = 3800
Case Else
    Me.myArrow.Visible = True
    Me.Box25.Visible = True
    Me.myArrow.Top = 4000
    
End Select
 
S

silversun

Registered User.
Local time
Today, 11:43
Joined
Dec 28, 2012
Messages
204
arnelgp said:
reverse the order:
Code: 
Case 1944 to 2160
    Me.myArrow.Visible = True
    Me.Box34.Visible = True
    Me.myArrow.Top = 1400

Case 1728 To 1943
    Me.myArrow.Visible = True
    Me.Box33.Visible = True
    Me.myArrow.Top = 1700
....
....
Case 432 To 647
    Me.myArrow.Visible = True
    Me.Box27.Visible = True
    Me.myArrow.Top = 3500
   
Case 216 To 431
    Me.myArrow.Visible = True
    Me.Box26.Visible = True
    Me.myArrow.Top = 3800
Case Else
    Me.myArrow.Visible = True
    Me.Box25.Visible = True
    Me.myArrow.Top = 4000
   
End Select
Click to expand...
It works now. By reversing the order it start working. Thank you all specially arnelgp
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 19:43
Joined
Sep 21, 2011
Messages
14,231
FWIW it works with low to high order tests as well, just that the case line should also be low to high.?

I could not get the Between syntax to work either :(
 
S

silversun

Registered User.
Local time
Today, 11:43
Joined
Dec 28, 2012
Messages
204
Gasman said:
FWIW it works with low to high order tests as well, just that the case line should also be low to high.?

I could not get the Between syntax to work either :(
Click to expand...
My case line order was low to high initially (and it did't work) but as arnelgp recommended I switched the order before I got the desired result. May be if I would keep the case order and modify the range properly it could be working.
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:43
Joined
Feb 28, 2001
Messages
27,137
When your cases have "BETWEEN low-end AND high-end" then the individual cases should work. Whether the case-ladder as a whole would work depends on whether you have any potentially overlapping ranges. If you do not, then the order of presentation of each case should have no effect on whether it would work (though if you have a LOT of cases, some orders might be slower than others.)
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 19:43
Joined
Sep 21, 2011
Messages
14,231
The_Doc_Man said:
When your cases have "BETWEEN low-end AND high-end" then the individual cases should work. Whether the case-ladder as a whole would work depends on whether you have any potentially overlapping ranges. If you do not, then the order of presentation of each case should have no effect on whether it would work (though if you have a LOT of cases, some orders might be slower than others.)
Click to expand...
Doc,

Can you supply the syntax please, as when I tried Case Between x and y the code is just highlighted in red as an error and x is highlighted 'Compile error, expected end of statement'?
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:43
Joined
Feb 28, 2001
Messages
27,137
The syntax would be

Code: 
SELECT CASE Z
    CASE BETWEEN X AND Y
        <do something>
    CASE BETWEEN A AND B
        <do something else>
....
    CASE ELSE
        <and now for something totally different>
END SELECT

That should be about right.

docs.microsoft.com

Select Case statement (VBA)

Office VBA reference topic
docs.microsoft.com

IF you continue having trouble, then as long as X and Y are integers, you can also do

CASE X TO Y

as noted in the linked reference.
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:43
Joined
Feb 28, 2001
Messages
27,137
Then try the CASE 216 to 431 syntax.
 
You must log in or register to reply here.

Similar threads

J
Multiple Select case statements no working
Replies
2
Views
3,727
john4282
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom