CODEWITHSUNDEEP
vbaexcelselectcase
Dimitri
Dimitri

Reputation: 1

VBA Select Case runs through without selection

I am really puzzled by this one. Searched the net already but did not find any answer related to my specific case. I have the following code:

Dim CE_res As Integer


For Index = 1 To 7

Status = ""
CE_res = CInt(Worksheets("werkblad").Range("CEres" & Index).Value)

If CE_res = 0 Then
' Everything ok
    Status = "Pass"

Else

    Select Case CE_res

        Case CE_res < -1500
            Status = "Invalid kV Value"

        Case CE_res < -999
            Status = "No kV Value"

        Case CE_res < -399
            Status = "kV too high"

        Case CE_res = 0
            Status = "kV too low"

        Case CE_res > 500
            Status = "Invalid mAs Value"

        Case CE_res > 49
            Status = "mAs Value missing"

        Case CE_res > 9 And CE_res < 50
            Status = "Adapt Target"

        Case CE_res > 200
            Status = "Adapt Filter"

        Case Else
            Status = "No Selection"

    End Select

End If

The CE_res is set to 50. However, always Case Else is selected. Declared CE_res as integer, coverted Worksheets("werkblad").Range("CEres" & Index).Value to an integer, just to be sure. However, it does not seem to execute the Select Case correctly. Tried it also with other values, even changed the case CE_res > 49 to CE_res = "50" but this also did not work.

Now, I am out of ideas what could be wrong.

Upvotes: 0

Views: 155

Answers (2)

iShaymus
iShaymus

Reputation: 532

As mentioned by other members, due to your use of < > = comparitors you need to make sure you order them correctly or they will not work as intended. With the very specific values you are using you would probably be better to use TO and be more specific with the values.

Select Case
  Case -1000 TO -1500
    'Do This
  Case -400 TO -999
    'Do That
  Case -1 TO -399
    'Do Something Else
End Select

If you do this the there is no ambiguity as to what does what, it's easier to read and the operation order doesn't matter.

You also don't need to add the CE_res to each Case statement as you already specified the variable on the Select Case CE-res line. The Correct syntax is

Select Case CE_res
  Case <-1500
    Status = "Invalid kV Value"
End Select

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

You are not using the correct syntax for the Case statements

Dim CE_res As Integer


For Index = 1 To 7

Status = ""
CE_res = CInt(Worksheets("werkblad").Range("CEres" & Index).Value)

If CE_res = 0 Then
' Everything ok
    Status = "Pass"

Else

    Select Case CE_res

        Case < -1500
            Status = "Invalid kV Value"

        Case < -999
            Status = "No kV Value"

        Case < -399
            Status = "kV too high"

        Case 0  ' or possibly Case < 0 ????
            Status = "kV too low"

        Case > 500
            Status = "Invalid mAs Value"

        Case > 200
            Status = "Adapt Filter"

        Case > 49
            Status = "mAs Value missing"

        Case > 9
            Status = "Adapt Target"

        Case Else
            Status = "No Selection"

    End Select

End If

Note that I had to move > 200 prior to > 49, otherwise a value of, for instance, 230 would have matched > 49 and therefore never reached your > 200.

The way you had it, the statement Case CE_res < -1500 would test if CE_res was < -1500. If it was, that would return a True which was then compared to the object of the case statement (i.e. CE_res) and, if it matched (which it wouldn't) that leg of the Select statement would execute.

Upvotes: 2

Related Questions