CODEWITHSUNDEEP
excelvba
Suyog Patil
Suyog Patil

Reputation: 23

Error with application.ontime method in excel vba

I havew live data feed in excel, now want to record data as shown here:

enter image description here]

I wrote on code as follows 

Option Explicit

Dim SchedRecalc As Date
Sub Recalc()
    Range("A1").Value = Format(Now, "dd-mmm-yy")
    Range("B1").Value = Format(Time, "hh:mm:ss AM/PM")

    Call SetTime
End Sub

Sub SetTime()
    SchedRecalc = Now + TimeValue("00:00:01")
    Application.OnTime SchedRecalc, "Recalc"
    Application.OnTime SchedRecalc, "Record"

End Sub

Sub Record()
'
' update Macro
'


    With Worksheets("Sheet1")

        Application.ScreenUpdating = False
        Sheet1.Range("A1:A169").Copy _
        Destination:=mySheet.Range("E9")
        Sheet1.Range("E9:E169").Insert Shift:=xlShiftToRight

        Selection.Insert Shift:=xlToRight
        Application.CutCopyMode = False

    End With

End Sub


Sub Disable()
    On Error Resume Next
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc",                
    Schedule:=False
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Record",    
    Schedule:=False

End Sub

but following error is coming.. i have disabled all security setting:

enter image description here]

Upvotes: 0

Views: 4413

Answers (1)

Aaron Bartz
Aaron Bartz

Reputation: 38

I successfully ran your code. Make sure the procedure you reference using Application.OnTime is placed into a Module.

You may also double check your last line. Copying and pasting your code didn't make the VBA editor happy.

Sub Disable()
    On Error Resume Next
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc",                
    Schedule:=False
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Record",    
    Schedule:=False
End Sub

It should be on one line.

Sub Disable()
    On Error Resume Next
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Record", Schedule:=False
End Sub

Upvotes: 1

Related Questions