'VBA Case Select Multiple Conditions

New to VBA. I'm attempting to build a value of Dimensions (pulling from two different cells in an excel spreadsheet in which one might be larger than the other, and I always want the lower number first) in which the output (a string which will be concatenated with strings from other functions) might be one of the following:

4868 (no x separating the integer values) 48x60.5 (with x separating an integer and real number) 36.5x60 (with x separating a real number and an integer) 24.75x72.125 (with x separating a real number and an integer)

Variable types are defined in VBA as Single (not Double). Here's my code:

Function getDimDisplay(h As Single, w As Single) As String

Dim strResult As String
Dim iH As Integer
Dim iW As Integer
Dim strH As Variant
Dim strW As Variant

iH = CInt(h)
iW = CInt(w)

Select Case h
    Case (h >= w And iH = h And iW = w)
        strH = CStr(iH)
        strW = CStr(iW)
        strResult = strW & strH
    Case (h >= w And iH <> h And iW = w)
        strH = CStr(h)
        strW = CStr(iW)
        strResult = strW & "x" & strH
    Case (w >= h And iH = h And iW <> w)
        strH = CStr(iH)
        strW = CStr(w)
        strResult = strH & "x" & strW
    Case (w >= h And iH <> h And iW <> w)
        strH = CStr(h)
        strW = CStr(w)
        strResult = strH & "x" & strW
End Select

getDimDisplay = strResult

End Function

It will compile, but it won't return any output. What gives?

vba excel

Solution 1:^[1]

your variable 'h' is not a boolean. However, you're calling it in select case to match conditions which are either true or false.

Change your "select case h" to "select case true". all else will work ok.

Select Case True

Case (h >= w And iH = h And iW = w)
    strH = CStr(iH)
    strW = CStr(iW)
    strResult = strW & strH
Case (h >= w And iH <> h And iW = w)
    strH = CStr(h)
    strW = CStr(iW)
    strResult = strW & "x" & strH
Case (w >= h And iH = h And iW <> w)
    strH = CStr(iH)
    strW = CStr(w)
    strResult = strH & "x" & strW
Case (w >= h And iH <> h And iW <> w)
    strH = CStr(h)
    strW = CStr(w)
    strResult = strH & "x" & strW

End Select

Solution 2:^[2]

Select Case doesn't work like this. It compares the item presented (h) to the values calculated for the individual case statements.

The case statements you have all evaluate to a bool, true or fasle. Whatever h equals, it's not that! For this bit of code, you nedd an if then else if structure.

Solution 3:^[3]

Just for completeness, the closest you can get to the structure youre looking for is this type of thing:

Select Case h
Case Is >= w And Is = iH
    If w = iW Then
    '   do stuff
    Else
    '   do other stuff
    End If
Case Is <= w And Is = iH
    If w <> iW Then
    '   do stuff
    End If
Case Is > -w And Is <> iH
    If w <> iW Then
    '   do stuff
    End If
End Select

Solution 4:^[4]

In Select case, you can't use "and" operator, instead you have to use a comma ","

Select Case h
Case Is >= w , Is = iH
    If w = iW Then
    '   do stuff
    Else
    '   do other stuff
    End If
Case Is <= w , Is = iH
    If w <> iW Then
    '   do stuff
    End If
Case Is > -w , Is <> iH
    If w <> iW Then
    '   do stuff
    End If
End Select

Please see the below example for more clarity

http://gadoth.com/excel-vba-series-post-9-select-case/

Solution 5:^[5]

try this:

Function getDimDisplay(h As Single, w As Single) As String
Dim iH%:    iH = CInt(h)
Dim iW%:    iW = CInt(w)

If h >= w And iH = h And iW = w Then
    getDimDisplay = CStr(iW) & CStr(iH)
Else
    If h >= w And iH <> h And iW = w Then
        getDimDisplay = CStr(iW) & "x" & CStr(h)
    Else
        If w >= h And iH = h And iW <> w Then
            getDimDisplay = CStr(iH) & "x" & CStr(w)
        Else
            If w >= h And iH <> h And iW <> w Then
                getDimDisplay = CStr(h) & "x" & CStr(w)
            End If
        End If
    End If
End If
End Function

Solution 6:^[6]

Fixed the error I was seeing with some numbers not being handled correctly. I was missing a comparison scenario - should have been four comparisons to make instead of three for each h>=w or w>=h situation. Yay! Thanks folks! Here's the working code:

Function getDimDisplay(h As Single, w As Single) As String

Dim iH%:    iH = CInt(h)
Dim iW%:    iW = CInt(w)

If h >= w And iH = h And iW = w Then
    getDimDisplay = CStr(w) & CStr(h)
Else
    If h >= w And iH <> h And iW = w Then
        getDimDisplay = CStr(w) & "x" & CStr(iH)
    Else
        If h >= w And iH = h And iW <> w Then
            getDimDisplay = CStr(w) & "x" & CStr(iH)
        Else
            If h >= w And iH <> h And iW <> w Then
                getDimDisplay = CStr(w) & "x" & CStr(h)
            Else
                If w >= h And iH = h And iW = w Then
                    getDimDisplay = CStr(iH) & CStr(iW)
                Else
                    If w >= h And iH <> h And iW = w Then
                        getDimDisplay = CStr(h) & "x" & CStr(iW)
                    Else
                        If w >= h And iH = h And iW <> w Then
                            getDimDisplay = CStr(iH) & "x" & CStr(w)
                        Else
                            If w >= h And iH <> h And iW <> w Then
                                getDimDisplay = CStr(h) & "x" & CStr(w)
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If    
End Function

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Michael James
Solution 2	simon at rcl
Solution 3
Solution 4	0m3r
Solution 5	PPh
Solution 6	Zsmaster

'VBA Case Select Multiple Conditions

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]