VBA Case Select Multiple Conditions

Question

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?

Answer 1

I want to use the switch statement with multiple or conditions. Using , in the case will work. Please see the below code that will work.

Dim value as String

'Get a value to use in switch case
expression = getValues(variable)

Select Case expression
'if the expression has value1 or value2
'Execute the below case statement

    Case "value1", "value2"
         Call firstSub(expression)

    Case "value3"
         Call secondSub()
          
End Select

Answer 2

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/

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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.