vendredi 14 juin 2019

If-ElseIf-Else Code is skipping Else block

I'm pretty new to VBA and I have been absolutely bewildered on a portion of a subroutine I have for close to 2 weeks now. Today I went through my code one line at a time (pressing the F8 key) to see where it was getting hung-up and I think I discovered the source. My understanding of IF-THEN statements is that the condition is checked at each "IF" statement and only if the condition is True then the code after the "THEN" portion is run. When using IF-Elseif-Else, the code will check the ElseIf portion in a similar manner and if none are true, the ELSE section will run.

However, I have been noticing that with my particular code, in times I am certain that the ELSE portion should run it does not. I have checked this section of the code by commenting out all the related IF and ElseIf code above it and it works nicely.

So, my question is then this: With nested IF statements like I have, if some of the nested IFs are true but not all of them, will the ELSE statement become void at this point? Does Excel think "ok, something here in this first IF-Then nest is true, I'll keep checking this out, I can ignore the ElseIfs and ELSE statements after"

Thanks for bearing through this with me. The code I have shown could certainly be written better. Ultimately, I think the solution here for me is to not use nested if statements and compile the sub-level IF statements into one albeit VERY long IF statement where it checks if all of those things are true at once.

Any thoughts/recommendation/feedback would be greatly appreciated! P.S. I had to retype all the code below from a printed sheet on my home computer. The code i have on my actual work computer is all spelled correctly.

    Range("I3").FormulaArray= "=INDEX(CombinedFillet,MATCH(1,(F3=StudMaterial)*(G3=BaseMaterial)*(H3=StudDiameter),0))"

If listboxStudMatl.ListIndex <> -1 And listboxStudDiamDecimal.ListIndex <> -1 And listboxBase.ListIndex <> -1 Then
    If Not IsError (Application.Match(listboxBase.value, Sheets("Parts").Range("SupportRod"), 0)) Then
        If listboxStudMatl.Value = "OSS/HSS" or listboxStudMatl.Value = "CRES" or listboxStudMatl.value = "HY-80" Then
            If listboxStudDiamDecimal.Value = "0.375" Then
                textboxJ1_3.Text = Cells.Range("M4").Text
                MsgBox "Test 1" 'for debugging only
            Elseif listboxStudDiamDecimal.value > 0.375 Then
                MsgBox "Choose a different option"
                Exit Sub
            End If
        End If
    End If

ElseIf listboxParts.ListIndex <> -1 And listboxBase.ListIndex <> -1 Then
    If Not IsError(Application.Match(listboxParts.Value, Sheets("Parts").Range("Stud375"), 0)) Then
        textboxJ1_3.Text = Cells.Range("M4").Text
        MsgBox "Test 2" 'for debugging only
    End If

Else
MsgBox "Test 3" 'for debugging only
Range ("I3").Select
Selection.Copy
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues
textboxJ1_3 = Cells.Range("I4").Text
MsgBox "Test 4" ' for debugging only

End If

Aucun commentaire:

Enregistrer un commentaire