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?

oops, just realized the last case statement is real number and real number. Brain belch.... — user3229658
– user3229658, Commented Jan 23, 2014 at 21:37
So... is your problem solved then? If yes, please remove the post or answer it yourself (see the button at the bottom of the screen). — Max Leske
– Max Leske, Commented Jan 23, 2014 at 21:54
still tinkering..... it works for most situations, but not all. — user3229658
– user3229658, Commented Jan 28, 2014 at 14:27

Michael James · Accepted Answer · 2018-09-25 19:39:08Z

9

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

answered Sep 25, 2018 at 19:39

Michael James

6161 gold badge8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

simon at rcl · Accepted Answer · 2014-01-24 09:02:47Z

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.

answered Jan 24, 2014 at 9:02

simon at rcl

7,3691 gold badge21 silver badges25 bronze badges

1 Comment

user3229658 Over a year ago

the if/then syntax is moving me closer to the goal post. See comments above about the handling of digits (specifically zeros) after the decimal.

Cool Blue · Accepted Answer · 2014-01-30 00:42:51Z

1

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

edited Jan 30, 2014 at 0:42

answered Jan 30, 2014 at 0:19

Cool Blue

6,5067 gold badges31 silver badges70 bronze badges

2 Comments

variant Over a year ago

As noted above, "And" cannot be used with Case statements in VBA.

6diegodiego9 Over a year ago

@variant this is not exact, after some tests I saw that "And" can always be used except with an "Is" statement on its right

Vikram · Accepted Answer · 2024-10-17 23:20:11Z

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

answered Oct 17, 2024 at 23:20

Vikram

7,58318 gold badges87 silver badges131 bronze badges

2 Comments

Trashman Over a year ago

Confirmed that this works, tried it with both strings and numbers

Trashman Over a year ago

Also works with IS inequalities. However, works like an "or" statement, so don't know that it answered the OPs question which involved "and" statements. Simon's answer, though odd in structure, works for that.

PPh · Accepted Answer · 2014-01-24 10:12:19Z

0

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

answered Jan 24, 2014 at 10:12

PPh

473 bronze badges

4 Comments

user3229658 Over a year ago

Sorry to take so long getting back about whether this would work or not. I have about six irons in the fire, and this (while important to ME) was not important to the boss, so it slipped to the bottom of the priority list. But, drum roll, it works for MOST situations. Unfortunately, it doesn't for others. Ironically, there doesn't seem to be a rhyme to the reason. Buy me a clue about defining the variable with the % in it?

user3229658 Over a year ago

It appears to choke where the variables are defined from my spreadsheet with all zeros behind the decimal. In other words, 48.50000 is accurately truncated to 48.5; however, 48.00000 produces no result at all.

PPh Over a year ago

use round function to get rid of zeros like this getDimDisplay = CStr(round(h,10)) & "x" & CStr(round(w,10)). dim iH% is exacly the same as dim iH as Integer just shorten version. read more here

user3229658 Over a year ago

I've actually used round in other situations, but since there's no room for error here, it wasn't appropriate. My problem had to do with having the correct number of comparison options. I had four different scenarios in my original example when there should have been eight. Once I fixed that, all was well in the world. :)

Zsmaster · Accepted Answer · 2017-11-11 19:57:45Z

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

0m3r · Accepted Answer · 2020-03-11 22:56:43Z

0

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/

edited Mar 11, 2020 at 22:56

0m3r

12.5k15 gold badges40 silver badges77 bronze badges

answered Jul 31, 2017 at 6:46

Rajathithan Rajasekar

4105 silver badges12 bronze badges

2 Comments

6diegodiego9 Over a year ago

This is WRONG: the comma in a select case is an OR, not an AND.

Wildhorn Over a year ago

This answer is very wrong. You can use "And" in a Select Case and the comma is not the equivalent of an "And". It act like if you had an extra Case with the same code after (it kinda act like an "Or"). Check excelmacromastery.com/vba-select-case for more details.

Collectives™ on Stack Overflow

VBA Case Select Multiple Conditions

7 Answers 7

Comments

1 Comment

2 Comments

2 Comments

4 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

2 Comments

2 Comments

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related