CODEWITHSUNDEEP
vbaexcel
Brad
Brad

Reputation: 1480

Changing IF statement to be more efficient

I have a lot of scenarios that lead to the same message box alert. Is there an easier/better solution than making several if statements?

      PRODUCTS            BOX1     BOX2       BOX3
    --------------------------------------------------
    |Apples, Oranges, |    X    |    x    |          |
    |Grapes, Peaches  |    x    |    x    |          |
    |------------------------------------------------|
    |Wheat            |    x    |    x    |     x    |
    |-------------------------------------------------
    |Peanuts          |         |    x    |          |
    --------------------------------------------------

If product = "Apples" or product = Oranges or product = Grapes or products = Peaches then
    If box = "box1" or box = "box2" then
        msgbox "Your box may require approval"
    End If
End If

If product = "Wheat" then
    If box = "box1" or box = "box2" or box = "box3" then
        msgbox "Your box may require approval"
    End If
End If

If product = "Peanuts" then
    If box = "box2" then
        msgbox "Your box may require approval"
    End If
End If

Upvotes: 1

Views: 56

Answers (3)

trincot
trincot

Reputation: 350272

You could do it like this:

Select Case product
Case "Apples", "Oranges", "Grapes", "Peaches", "Wheat", "Peanuts"
    Select Case box
    Case "box1", "box2", "box3":
        If product = "Wheat" Or box = "box2" Or (product <> "Peanuts" And box <> "box3") Then
            MsgBox "Your box may require approval"
        End If
End Select

Upvotes: 3

Vityata
Vityata

Reputation: 43585

You can keep the values in an array and check from there. Something like this:

Option Explicit

Public Function b_value_in_array(my_value As Variant, my_array As Variant) As Boolean

    Dim l_counter as long

    For l_counter = LBound(my_array) To UBound(my_array)
        my_array(l_counter) = CStr(my_array(l_counter))
    Next l_counter

    b_value_in_array = Not IsError(Application.Match(CStr(my_value), my_array, 0))

End Function

Public Sub TestMe()

    Dim product         As String: product = "Oranges"
    Dim box             As String: box = "box2"

    Dim arr_products1   As Variant
    Dim arr_products2   As Variant
    Dim arr_products3   As Variant
    Dim arr_boxes_1     As Variant
    Dim arr_boxes_2     As Variant
    Dim arr_boxes_3     As Variant

    arr_products1 = Array("Apples", "Oranges", "Grapes", "Peaches")
    arr_products2 = Array("Wheat")
    arr_products3 = Array("Peanuts")

    arr_boxes_1 = Array("box1", "box2")
    arr_boxes_2 = Array("box1", "box2", "box3")
    arr_boxes_3 = Array("box2")

    If b_value_in_array(product, arr_products1) And b_value_in_array(box, arr_boxes_1) Then
        Call ApprovalMsgBox
    End If

    If b_value_in_array(product, arr_products2) And b_value_in_array(box, arr_boxes_2) Then
        Call ApprovalMsgBox
    End If

    If b_value_in_array(product, arr_products3) And b_value_in_array(box, arr_boxes_3) Then
        Call ApprovalMsgBox
    End If

End Sub

Public Sub ApprovalMsgBox()
    MsgBox "Your box may require approval"
End Sub

Run TestMe. Originally, you may use elseif, but it would not save you much time and I think that it is better this way^^.

Upvotes: 1

Doug Coats
Doug Coats

Reputation: 7107

Yes! You can write a public Sub to be called upon

If product = "Apples" or product = Oranges or product = Grapes or products = Peaches then
    If box = "box1" or box = "box2" then
        Call MySub
    End If
End If

If product = "Wheat" then
    If box = "box1" or box = "box2" or box = "box3" then
        Call MySub
    End If
End If

If product = "Peanuts" then
    If box = "box2" then
        Call MySub
    End If
End If

Public Sub MySub
     msgbox "Your box may require approval"
End Sub

Upvotes: 0

Related Questions