vba replace nested if statement

Question

Is there a way to make this more efficient? I find it a bit ugly. I want to verity if there is a next "deck", if so, execute some for it, until deck8

here is my code:

         If deckNum > 0 Then
            'Execute code for deck 1
            If deckNum > 1 Then
                'Execute code for deck 2
                If deckNum > 2 Then
                    'Execute code for deck 3
                    If deckNum > 3 Then
                        'Execute code for deck 4
                        If deckNum > 4 Then
                            'Execute code for deck 5
                            If deckNum > 5 Then
                                'Execute code for deck 6
                                If deckNum > 6 Then
                                    'Execute code for deck 7
                                    If deckNum > 7 Then
                                        'Execute code for deck 8
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
         End If

'Execute code for deck 1 is it a 1 liner code or mulltiple lines? Also shouldn't your if be in reverse? i.e from 7 to 0 and not 0 to 7? — Siddharth Rout
– Siddharth Rout, Commented Oct 18, 2013 at 17:20
Also on that note, is the code variable based on the decknum (I.E do x 2 times for deck 2, 3 times for deck 3, etc). Anything with a pattern, I guess I'm getting at. — Joe Laviano
– Joe Laviano, Commented Oct 18, 2013 at 17:23

Gratzy · Accepted Answer · 2013-10-18 17:28:36Z

3

Use a Case Statement

Select Case deckNum

case 0
    'execute code for deck 0
case 1
    'execute code for deck 1
case 2
    'execute code for deck 2
case 3
    'execute code for deck 3 
End Select

Here is an office VBA reference http://msdn.microsoft.com/en-us/library/office/gg278454.aspx

edited Oct 18, 2013 at 17:28

answered Oct 18, 2013 at 17:23

Gratzy

9,4174 gold badges32 silver badges45 bronze badges

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

2 Comments

Siddharth Rout Over a year ago

+ 1 For select case. Though your select conditions + order is incorrect :)

Gratzy Over a year ago

Yes it is not a complete solution, just an alternative to to the nested if

Siddharth Rout · Accepted Answer · 2013-10-18 17:28:28Z

0

Further to my comments, if your Execute code for.. is a one liner then see option 1 else see option 2

Second thing, you have to check in reverse else any number which is greater than 0 will fire the Deck 1 Code.

OPTION 1

Replace MsgBox... with your one liner code.

Select Case deckNum
    Case Is > 7: MsgBox "A"
    Case Is > 6: MsgBox "B"
    Case Is > 5: MsgBox "C"
    Case Is > 4: MsgBox "D"
    Case Is > 3: MsgBox "E"
    Case Is > 2: MsgBox "F"
    Case Is > 1: MsgBox "G"
    Case Is > 0: MsgBox "H"
End Select

OPTION 2

Select Case deckNum
    Case Is > 7: proc1
    Case Is > 6: proc2
    Case Is > 5: Proc3
    Case Is > 4: Proc4
    Case Is > 3: Proc5
    Case Is > 2: Proc6
    Case Is > 1: Proc7
    Case Is > 0: Proc8
End Select

Sub proc1()

End Sub

Sub proc2()

End Sub

'
'~~> And So oN
'

answered Oct 18, 2013 at 17:28

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

2 Comments

user2895741 Over a year ago

the thing is(using OPTION 2 for example), proc8 applies to all, proc7 applies to Case Is>1 ect. so for example, in Case Is>7, i would need Proc8+Proc7+Proc6...+Proc1 Should I stick to the nested If statements? I don't think switch ca is the best option since I would have to repeat code.

Gratzy Over a year ago

You can still use his option 2 just change the operators to = and if its = 8 then execute procs 1-8, = 7 execute procs 1-7 the only repeated code then is the calling of the subroutines, not the code in the subroutines. I think that would be cleaner than nested ifs

Collectives™ on Stack Overflow

vba replace nested if statement

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related