VBA Comparison Operators Evaluating Incorrect with Case

Question

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.

Answer 1

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