CODEWITHSUNDEEP
vbacomparison-operators
N Hanson
N Hanson

Reputation: 101

VBA Comparison Operators Evaluating Incorrect with Case

I have a code block which is consistently evaluating incorrectly . I'm at a loss as to why this might be.

I've determined that it must be something to do with my 'Select Case' at the very least. If the order of operators is changed, e.g. =, >, < or <, =, > etc. the evaluation changes.

My expectation is that the evaluation would be a = b with the eventuality that highest_header = 24 - While 24 is inevitably assigned to highest_header, my code does so using what I believe to be the wrong 'Case'.

I have simplified the code snippet slightly, but the result is the same - the comparison is being evaluated as a = b = FALSE and a < b = TRUE

Sub highest_header()
Dim highest_header_count As Integer
Dim a As Integer
Dim b As Integer

a = 24
b = 24

Debug.Print a '24
Debug.Print b '24
Debug.Print TypeName(a) 'Integer
Debug.Print TypeName(b) 'Integer

Select Case highest_header_count
    Case a = b
        highest_header_count = b
    Case a < b
        highest_header_count = b
    Case a > b
        highest_header_count = a
End Select

End Sub

Any insight would be appreciated.

Upvotes: 2

Views: 297

Answers (4)

Shai Rado
Shai Rado

Reputation: 33692

Or, you can use Select Case, just "trick" it a little.

You can use Select Case a - b , and then check Case 0, Case Is < 0 and Case Is > 0, see my code below:

Sub highest_header()

Dim highest_header_count As Integer
Dim a As Integer
Dim b As Integer

a = 24
b = 24

Debug.Print a '24
Debug.Print b '24
Debug.Print TypeName(a) 'Integer
Debug.Print TypeName(b) 'Integer

Select Case a - b
    Case 0
        highest_header_count = b
    Case Is < 0
        highest_header_count = b
    Case Is > 0
        highest_header_count = a
End Select

End Sub

You can make the Select Case even shorter:

Select Case a - b
    Case 0, Is < 0 ' <-- both scenarios lead to the same result
        highest_header_count = b      
    Case Is > 0
        highest_header_count = a
End Select

Upvotes: 1

paul bica
paul bica

Reputation: 10715

Option Explicit

Public Sub HighestHeader()
    Dim highest_header_count As Long
    Dim a As Long
    Dim b As Long

    a = 24
    b = 24

    Debug.Print a '24
    Debug.Print b '24
    Debug.Print TypeName(a) 'Long
    Debug.Print TypeName(b) 'Long

    Select Case a
        Case Is = b
            highest_header_count = b
        Case Is < b
            highest_header_count = b
        Case Is > b
            highest_header_count = a
    End Select
End Sub

You are comparing highest_header_count against the 3 comparisons (a = b, etc)

Upvotes: 3

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You need IF Else statements instead of Select Case here. Also in the Select Case you are using the wrong variable highest_header_count.

Sub highest_header()
Dim highest_header_count As Integer
Dim a As Integer
Dim b As Integer

a = 24
b = 24

Debug.Print a '24
Debug.Print b '24
Debug.Print TypeName(a) 'Integer
Debug.Print TypeName(b) 'Integer

If a = b Then
    highest_header_count = b
ElseIf a < b Then
    highest_header_count = b
ElseIf a > b Then
    highest_header_count = a
End If

End Sub

OR use your code like this...

Sub highest_header()
Dim highest_header_count As Integer
Dim a As Integer
Dim b As Integer

a = 24
b = 24

Debug.Print a '24
Debug.Print b '24
Debug.Print TypeName(a) 'Integer
Debug.Print TypeName(b) 'Integer

Select Case True
    Case a = b
        highest_header_count = b
    Case a < b
        highest_header_count = b
    Case a > b
        highest_header_count = a
End Select

End Sub

Upvotes: 1

Brian M Stafford
Brian M Stafford

Reputation: 8868

Your Select Case is comparing an integer with a value of 0 to a Boolean.

a = b is True which is -1, a < b is False which is 0, a > b is False which is 0.

So that is why you are getting a hit on a < b.

Upvotes: 3

Related Questions