Avoiding Nested IFs in VBA with "And _"

Question

I am trying to avoid nesting IF statements but the code below gives me an error. The error only appears when I have the first line present. Without it, the other "And _" work perfectly. I cannot understand why! For background, the objective of this code is to check that the date is in the correct format (dd/mm/yyyy) and occurs after 2020. Any help would be really appreciated!

This code doesn't work:

Public ValidPaymentDate As Boolean
Public ArrInput_PaymentDate As Variant

ArrInput_PaymentDate = Split(PaymentDate.Value, "/") 

If (UBound(ArrInput_PaymentDate) = 2) And _ ' ***Here is the issue!!!***
    ArrInput_PaymentDate(0) > 0 And ArrInput_PaymentDate(0) <= 31 And _
    ArrInput_PaymentDate(1) > 0 And ArrInput_PaymentDate(1) <= 12 And _
    ArrInput_PaymentDate(2) >= 20 And ArrInput_PaymentDate(2) < 100 Then
    ValidPaymentDate = True
End If

This code works (but I'd like to avoid nesting If statements):

Public ValidPaymentDate As Boolean
Public ArrInput_PaymentDate As Variant

ArrInput_PaymentDate = Split(PaymentDate.Value, "/") 

If (UBound(ArrInput_PaymentDate) = 2) Then
    If ArrInput_PaymentDate(0) > 0 And ArrInput_PaymentDate(0) <= 31 And _
    ArrInput_PaymentDate(1) > 0 And ArrInput_PaymentDate(1) <= 12 And _
    ArrInput_PaymentDate(2) >= 20 And ArrInput_PaymentDate(2) < 100 Then
    ValidPaymentDate = True
    End If
End If

Even if it did work, it is a terrible way to validate a date. It will e.g. allow `31/02/1111`. — GSerg, Jan 24 '21 at 07:53
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/isdate-function — braX, Jan 24 '21 at 07:57

score -1 · Answer 1 · answered Jan 24 '21 at 07:50

You have an extra parenthesis that does not belong there

If (UBound(ArrInput_PaymentDate) = 2 And _ ' ***Here is the issue!!!***
    ArrInput_PaymentDate(0) > 0 And ArrInput_PaymentDate(0) <= 31 And _
    ArrInput_PaymentDate(1) > 0 And ArrInput_PaymentDate(1) <= 12 And _
    ArrInput_PaymentDate(2) >= 20 And ArrInput_PaymentDate(2) < 100) Then
    ValidPaymentDate = True
End If

Avoiding Nested IFs in VBA with "And _"

1 Answers1