CODEWITHSUNDEEP
excelvba
MarcBmann
MarcBmann

Reputation: 15

If Statement Evaluates to True even when Function Returns False

I have a spreadsheet with some VBA. It prompts the user to select a spreadsheet with a file browser. The spreadsheet the user is selecting contains a long list of items across rows, some of which are repeated. Each item has several attributes in the neighboring columns.

The code is taking each item, combining the attributes, and generating a new sheet without any duplicate items and the attributes combined.

My code should iterate through each item in the selected spreadsheet and checks the sheet it is building to see if it has already processed that item. If it has, it should move on. If not, it grabs the attributes, checks the rest of the sheet for duplicates, totals the attributes, and adds it to the new sheet.

In the main routine, I locate the columns I'm working with, and iterate through the user-selected spreadsheet with a for loop. With each iteration I call a function doesNotExist(). This function takes the item name as a parameter, and will iterate through all of the items on the new sheet to see if that item already exists on the new sheet. If the item does not exist on the new sheet, it returns True. Otherwise, it returns False.

The return data is passed to an If statement, which will only evaluate if my function returns True. Or, at least, it should.

I have used a lot of message boxes to see what my code is doing exactly. The function always evaluates the data correctly. However, the code inside the If statement will always execute, regardless of the returned value.

I have even added an Else to my if statement with a message box. That message box never opens, even when my function returns a value of false. 

Function doesNotExist(itemName As String, itemP As String, arSheet As Worksheet) As Boolean
    'iterates through all items on the PO. Checks to see if item defined in param exists in PO

    For i = 17 To (arSheet.Range("itemCount") + 17)
        If ((StrComp(arSheet.Cells(i, 4), itemName)) = 0) Then
            If ((StrComp(arSheet.Cells(i, 7), itemP)) = 0) Then
                doesNotExist = False
            End If
        End If
    Next i

    doesNotExist = True
End Function

If (doesNotExist(wb2.Worksheets(1).Cells(i, itemName.Column), wb2.Worksheets(1).Cells(i, itemP.Column), ar)) Then
    ~code~
Else
    MsgBox ("I did not print " & wb2.Worksheets(1).Cells(i, skuName.Column))
End If

Upvotes: 0

Views: 614

Answers (1)

Skin
Skin

Reputation: 11332

You need to exit your function when you’ve reached false ...

Function doesNotExist(itemName As String, itemP As String, arSheet As Worksheet) As Boolean
    'iterates through all items on the PO. Checks to see if item defined in param exists in PO

    For i = 17 To (arSheet.Range("itemCount") + 17)
        If ((StrComp(arSheet.Cells(i, 4), itemName)) = 0) Then
            If ((StrComp(arSheet.Cells(i, 7), itemP)) = 0) Then
                Exit Function
            End If
        End If
    Next i

    doesNotExist = True
End Function

Setting the return value in VBA doesn’t return from the function like other languages.

Also, you don't need to set the value to false, it's the default return value if you don't set it to true, therefore, I removed the line where you set the return value to false.

Upvotes: 4

Related Questions