CODEWITHSUNDEEP
vbaexcel
Matt
Matt

Reputation: 503

Excel VBA - IF/OR not evaluating properly

I have the following VBA code to check the name of an Excel workbook against a list of names and if it doesn't match any of them then it should display a message. The file name is "C12 - PICU Assignment Calculator.xlsm" and matches a name in the list below but it still produces the message saying it does not match.

If ThisWorkbook.Name <> "Day Shift - Respiratory Care Cover Sheet.xlsm" _
Or ThisWorkbook.Name <> "Night Shift - Respiratory Care Cover Sheet.xlsm" _
Or ThisWorkbook.Name <> "C7 - SD-NICU Assignment Calculator.xlsm" _
Or ThisWorkbook.Name <> "C11 - TICU Assignment Calculator.xlsm" _
Or ThisWorkbook.Name <> "C12 - PICU Assignment Calculator.xlsm" _
Or ThisWorkbook.Name <> "D3 - CVICU Assignment Calculator.xlsm" _
Or ThisWorkbook.Name <> "D7 - NICU Assignment Calculator.xlsm" _
Or ThisWorkbook.Name <> "D9 - PULM Assignment Calculator.xlsm" _
Or ThisWorkbook.Name <> "General Floors Assignment Calculator.xlsm" _
Or ThisWorkbook.Name <> "Plano Assignment Calculator.xlsm" _
Then
    MsgBox "The name does not match."
End If

What am I missing? Should I use a different method to accomplish this?

I checked to see if the file name is correct by using this statement

If ThisWorkbook.Name = "C12 - PICU Assignment Calculator.xlsm" Then
    MsgBox "The name matches"
End If

and it displays the "The name matches" message it should.

Upvotes: 1

Views: 118

Answers (4)

Vasily
Vasily

Reputation: 5782

this is not an answer, because it was already provided by @Amnon, but the additional variant (just to extend understanding) of how you can achieve the same result within more simpliest and more readable way:

Sub test()
    Select Case ThisWorkbook.Name
        Case "Day Shift - Respiratory Care Cover Sheet.xlsm", _
                "Night Shift - Respiratory Care Cover Sheet.xlsm", _
                    "C7 - SD-NICU Assignment Calculator.xlsm", _
                        "C11 - TICU Assignment Calculator.xlsm", _
                            "C12 - PICU Assignment Calculator.xlsm", _
                        "D3 - CVICU Assignment Calculator.xlsm", _
                    "D7 - NICU Assignment Calculator.xlsm", _
                "D9 - PULM Assignment Calculator.xlsm", _
            "General Floors Assignment Calculator.xlsm", _
        "Plano Assignment Calculator.xlsm"
    Case Else
        MsgBox "The name does not match."
    End Select
End Sub

Upvotes: 1

u8it
u8it

Reputation: 4296

It looks like the thinking in the OP is that the If statement evaluates as "If A does not match B or C or D or... then A does not match anything". This use of "or" makes sense in plain English; however, If statements require a very specific meaning for the word "or", namely, "or" is used in the context of creating an "OR Gate". This means it conjoins logical tests so that, if any single test is true, then the entire test is true. So the If statement is actually evaluating as "If A <> B then proceed (i.e. A does not match anything) OR If A <> C Then proceed (i.e. A does not match anything)... etc."

Changing Or to And resolves the problem by making the If statement into a "And Gate", which requires that every single conjoined logical test be true in order for the result to be true. This changes the logic to be "(If A <> B) AND (If A <> C)... Then A does not match anything". Alternatively, you could change the <> comparison to be = so that "If A = B then proceed (i.e. A matches something) OR If A = C Then proceed (i.e A matches something)... etc".

Ultimately, I would recommend using something along the lines of an array, list, dictionary, or collection for any test of inclusion. This tends to simplify the if statement and refine its focus to its main purpose, which is to test for inclusion, while the list can be managed independently. This way, the list can grow, shrink, and be modified without affecting the logic where inclusion is tested. Here's an example with an ArrayList

Sub NameTest()
    Set NameList = CreateObject("System.Collections.ArrayList")
    NameList.Add "Day Shift - Respiratory Care Cover Sheet.xlsm"
    NameList.Add "Night Shift - Respiratory Care Cover Sheet.xlsm"
    NameList.Add "C7 - SD-NICU Assignment Calculator.xlsm"
    NameList.Add "C11 - TICU Assignment Calculator.xlsm"
    NameList.Add "C12 - PICU Assignment Calculator.xlsm"
    NameList.Add "D3 - CVICU Assignment Calculator.xlsm"
    NameList.Add "D7 - NICU Assignment Calculator.xlsm"
    NameList.Add "D9 - PULM Assignment Calculator.xlsm"
    NameList.Add "General Floors Assignment Calculator.xlsm"
    NameList.Add "Plano Assignment Calculator.xlsm"

    If NameList.Contains(ThisWorkbook.Name) Then
        MsgBox "The name matches"
    Else
        MsgBox "The name does not match."
    End If
End Sub

Upvotes: 2

John Shaw
John Shaw

Reputation: 348

Try this code:

Public Sub Match()

Dim wb As Workbook
Dim wbnames(1 To 10) As String
Dim i As Integer

Set wb = ActiveWorkbook
On Error Resume Next
MsgBox wb.Name

wbnames(1) = "Day Shift - Respiratory Care Cover Sheet.xlsm"
wbnames(2) = "Night Shift - Respiratory Care Cover Sheet.xlsm"
wbnames(3) = "C7 - SD-NICU Assignment Calculator.xlsm"
wbnames(4) = "C11 - TICU Assignment Calculator.xlsm"
wbnames(5) = "C12 - PICU Assignment Calculator.xlsm"
wbnames(6) = "D3 - CVICU Assignment Calculator.xlsm"
wbnames(7) = "D7 - NICU Assignment Calculator.xlsm"
wbnames(8) = "D9 - PULM Assignment Calculator.xlsm"
wbnames(9) = "General Floors Assignment Calculator.xlsm"
wbnames(10) = "Plano Assignment Calculator.xlsm"

For i = 1 To 10
  If wb.Name = wbnames(i) Then
  MsgBox "this workbook name matches" & wbnames(i)
  End If
Next

Upvotes: 2

Amnon
Amnon

Reputation: 2320

The compound predicate is true. That is why the message "The name does not match." is shown. If you want to check that the does not match any of the names in the list, you ought to use And instead of Or.

Upvotes: 3

Related Questions