Incorrect execution inside if else loop

Question

I have the following code. But the if-else loop executes not correctly. I.e. MsgBox (Hour(strTime)) prints 14,

So If (Hour(strTime) = 17 & Minute(time) = 31 & Weekday(time) <> 1 & Weekday(time) <> 2) should not execute. But it does it anyway every time. Appreciate if someone see any mistakes in the code. Best Regards

Private RunWhen As Date
Private Const StartTime As Date = #2:14:30 PM#
Private Const EndTime As Date = #5:34:55 PM#
Private Const cRunInterval = "00:01:00"
Private Const cRunWhat = "Data" ' the name of the procedure to run


Sub StartTimer()
Dim strTime
strTime = time

    If RunWhen = 0 Then
        RunWhen = StartTime
    Else
        RunWhen = RunWhen + TimeValue(cRunInterval)
    End If
    MsgBox (Hour(strTime))
    If (RunWhen <= EndTime & Weekday(time) <> 1 & Weekday(time) <> 2) Then
        If (Hour(strTime) = 17 & Minute(time) = 31 & Weekday(time) <> 1 & Weekday(time) <> 2) Then
            'MsgBox ("Mail")
            Call CDO_Mail_Small_Text
        Else
            If (Hour(strTime) = 17 & Minute(time) = 33 & Weekday(time) <> 1 & Weekday(time) <> 2) Then
                MsgBox ("clear")
                Call ClearData
            Else
            Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
            End If
        End If
    End If


End Sub

Sub Data()
Dim RowNo As Long
Dim RowNo2 As Long
RowNo = Sheets(1).Cells(Rows.Count, 11).End(xlUp).Row
RowNo2 = Sheets(1).Cells(Rows.Count, 14).End(xlUp).Row + 1
    Sheets(1).Cells(RowNo2, 15) = Sheets(1).Cells(RowNo, 11) 
    Sheets(1).Cells(RowNo2, 16) = Sheets(1).Cells(RowNo, 12) 
    Sheets(1).Cells(RowNo2, 17) = Sheets(1).Cells(RowNo, 13) 
    Sheets(1).Cells(RowNo2, 14) = time

    StartTimer ' Reschedule the procedure
End Sub

R3uK · Accepted Answer · 2017-02-17 13:53:32Z

3

I believe your issues is with using & instead of the boolean operator AND.

Sub StartTimer()
Dim strTime
strTime = time

If RunWhen = 0 Then
    RunWhen = StartTime
Else
    RunWhen = RunWhen + TimeValue(cRunInterval)
End If
MsgBox (Hour(strTime))
If (RunWhen <= EndTime AND Weekday(time) <> 1 AND Weekday(time) <> 2) Then
    If (Hour(strTime) = 17 AND Minute(time) = 31 AND Weekday(time) <> 1 AND Weekday(time) <> 2) Then
        'MsgBox ("Mail")
        Call CDO_Mail_Small_Text
    Else
        If (Hour(strTime) = 17 AND Minute(time) = 33 AND Weekday(time) <> 1 & Weekday(time) <> 2) Then
            MsgBox ("clear")
            Call ClearData
        Else
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
        End If
    End If
End If


End Sub

Also, I am not sure if you are calling this sub with arguments from another routine, but when I tested it I was getting numerous variables that weren't defined.

It is good practice to ensure Option Explicit is declared at the beginning of your module.

This ensures all variables are declared and it has saved me from countless hours of tracing back variables due to typos.

edited Feb 17, 2017 at 13:53

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

answered Feb 17, 2017 at 13:46

Brandon Barney

2,3921 gold badge11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1665355 Over a year ago

Thanks, I have been programming to much javascript... Forgot that VBA has AND :P I dont get any undefined errors and have option explicit now.

Brandon Barney Over a year ago

Glad to hear it helped. I completely understand the difficulties in switching languages. I am sure it happens to the best of us!

Collectives™ on Stack Overflow

Incorrect execution inside if else loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related