Compress multiple OR-conditions in VBA code

Question

I use the following code to allow users to write a value into Cell A1.

Sub TestUsername()
If Environ("Username") = "firstname1.lastname1" Or Environ("Username") = "firstname2.lastname2" _
Or Environ("Username") = "firstname3.lastname3" Or Environ("Username") = "firstname4.lastname4" Then
Sheet1.Range("A1").Value = 1
Else
Sheet1.Range("A2").Value = 2
End If
End Sub

As you can see I list each user who is allowed to enter a value into Cell A1 with an OR-condition in my VBA code. All this works fine.

Now, I was wondering if there is an easier way to do this. Something like this:

Sub TestUsername()
If List of or-conditions: {"firstname1.lastname1", "firstname2.lastname2", _
"firstname3.lastname3", "firstname4.lastname4"} = True Then
Sheet1.Range("A1").Value = 1
Else
Sheet1.Range("A2").Value = 2
End If
End Sub

I just know in PHP you can compress multiple conditions like here. Therefore, I thought this might also be possible for VBA programming.

Select Case might be a solution – Storax Jun 29 '18 at 13:44 — Storax, Jun 29 '18 at 13:44

score 7 · Accepted Answer · edited Jun 29 '18 at 14:30

7

Maybe something like this

Sub TestUsername()
    Select Case Environ("Username")
    Case "firstname1.lastname1", "firstname2.lastname2", "firstname3.lastname3"
        Sheet1.Range("A1").Value = 1
    Case Else
        Sheet1.Range("A2").Value = 2
    End Select
End Sub

edited Jun 29 '18 at 14:30

Michi

4,663
6
33
83

answered Jun 29 '18 at 13:46

Storax

11,158
3
16
33

Miqi180 · Answer 2 · 2018-06-29T13:57:44.317

I suppose, if you had an atrocious amount of conditions, you could stick them in an array and then simply replace your conditional statement

If Environ("Username") = "firstname1.lastname1" Or Environ("Username") = "firstname2.lastname2" _
Or Environ("Username") = "firstname3.lastname3" Or Environ("Username") = "firstname4.lastname4" Then

with this

If IsInArray(Environ("Username"), arr) Then

This does require that you dimension an array with the conditions first and use this function, however:

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function

This way, your code becomes much more readable and easy to maintain.

score 0 · Answer 3 · answered Jun 29 '18 at 13:55

Since you're working in a cell, you might want to define the allowed usernames within the spreadsheet.

Here's how the spreadsheet table might look:

And here's the code you might use:

Sub TestUsername()
    Dim username As String
    Dim userInTable As Integer
    Dim allowedUserRange As Excel.Range

    username = Environ("username")
    Set allowedUserRange = Excel.Range("tUsers")

    userInTable = Excel.WorksheetFunction.CountIf(allowedUserRange, username)

    If userInTable Then
        Sheet1.Range("A1").Value = 1
    Else
        Sheet1.Range("A1").Value = 2
    End If
End Sub

Don't think this really answers the OP's question – Samuel Hulla Jun 29 '18 at 16:49 — Samuel Hulla, Jun 29 '18 at 16:49

score 0 · Answer 4 · answered Nov 24 '20 at 23:15

The Select Case provides a great solution to testing multiple conditions at the same time. I am using this to alert the user when they have not furnished all the required inputs. I am monitoring inputs from a number of Drop Down Boxes as well as some direct cell inputs.

Select Case True 
  Case Range("Customer_DD_Control_Cell") > 0 _ 
    And Range("Dealer_DD_Control_Cell") > 0 _ 
    And Range("Rep_DD_Control_Cell") > 0 _ 
    And Range("Product_DD_Control_Cell") > 0 _ 
    And Len(Range("Customer_State_Input")) > 0 _ 
    And Len(Range("Contract_Date_Input")) > 0
Case Else
 MsgBox "You have not completed the required inputs" 
End Select

Compress multiple OR-conditions in VBA code

4 Answers4

Linked