Every 5 seconds record the value in 2 cells in another worksheet VBA

Question

Problem: I have searched extensively for this and cannot seem to get it to work. I have a timer running when the "StartBtn" is pressed:

Dim StopTimer           As Boolean
Dim SchdTime            As Date
Dim Etime               As Date
Dim currentcost         As Integer
Const OneSec            As Date = 1 / 86400#

Private Sub ResetBtn_Click()
    StopTimer = True
    Etime = 0
    [TextBox21].Value = "00:00:00"
End Sub

Private Sub StartBtn_Click()
   StopTimer = False
   SchdTime = Now()
   [TextBox21].Value = Format(Etime, "hh:mm:ss")
   Application.OnTime SchdTime + OneSec, "Sheet1.NextTick"
End Sub

Private Sub StopBtn_Click()
    StopTimer = True
    Beep
End Sub

Sub NextTick()
   If StopTimer Then
      'Don't reschedule update
   Else
      [TextBox21].Value = Format(Etime, "hh:mm:ss")
      SchdTime = SchdTime + OneSec
      Application.OnTime SchdTime, "Sheet1.NextTick"
      Etime = Etime + OneSec
   End If
End Sub

Then in another cell (say, C16) I have a manually entered value which is the hourly cost rate. I have a third cell that is calculating total cost by C16*current timer value.

What I want to do is record every 5 seconds after the "StartBtn" is clicked the current time and current calculated cost in another sheet. This is what I have started:

Sub increment()
Dim x As String
Dim n As Integer
Dim Recordnext As Date

n = 0
Record = [TextBox21].Value
Recordnext = [TextBox21].Value + OneSec

Range("B13").Value = Recordnext

Do Until IsEmpty(B4)
    If [TextBox21].Value = Recordnext Then ActiveCell.Copy
      Application.Goto(ActiveWorkbook.Sheets("Sheet2").Range("A1").Offset(1, 0))
        ActiveSheet.Paste
        Application.CutCopyMode = False
        n = n + 1
        Recordnext = [TextBox21].Value + 5 * (OneSec)
    Exit Do
    End If
    ActiveCell.Offset(1, 0).Select
Loop
End Sub

But it doesnt work. Any help would be appreciated.

You state **'5 seconds'** but every indication in your code seems to be **1 second**. Your public variables should be in a module code sheet not a worksheet's code sheet. Use **Option Explicit*. — , Feb 05 '16 at 23:37

score 0 · Answer 1 · edited May 23 '17 at 10:32

I have tried to simplify your timer method down to what is actually needed.

Sheet1 code sheet

Option Explicit

Private Sub ResetBtn_Click()
    bRun_Timer = False
    'use the following if you want to remove the last time cycle
    TextBox21.Value = Format(0, "hh:mm:ss")
End Sub

Private Sub StartBtn_Click()
    bRun_Timer = True
    dTime_Start = Now
    TextBox21.Value = Format(Now - dTime_Start, "hh:mm:ss")
    Range("D16").ClearContents
    Call next_Increment
End Sub

Module1 code sheet

Option Explicit

Public bRun_Timer As Boolean
Public Const iSecs As Integer = 3  'three seconds
Public dTime_Start As Date

Sub next_Increment()
    With Worksheets("Sheet1")
        .TextBox21.Value = Format(Now - dTime_Start, "hh:mm:ss")
        .Range("D16") = Sheet1.Range("C16") / 3600 * _
                              Second(TimeValue(Sheet1.TextBox21.Value)) '# of secs × rate/sec
        Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Resize(1, 2).Offset(1, 0) = _
            Array(.TextBox21.Value, .Range("D16").Value)
    End With

    If bRun_Timer Then _
        Application.OnTime Now + TimeSerial(0, 0, iSecs), "next_Increment"
End Sub

Note that the operation of transferring the data to Sheet2 is a direct value transfer¹ with no .GoTo, ActiveCell or Select.

It was not entirely clear to me what you were trying to do with the value transfer. I have stacked them one after another on Sheet1.

You would benefit by adding Option Explicit² to the top of all your code sheets. This requires variable declaration and if you misplace a public variable, you will quickly know.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

² _{Setting Require Variable Declaration within the VBE's Tools ► Options ► Editor property page will put the Option Explicit statement at the top of each newly created code sheet. This will avoid silly coding mistakes like misspellings as well as influencing you to use the correct variable type in the variable declaration. Variables created on-the-fly without declaration are all of the variant/object type. Using Option Explicit is widely considered 'best practice'.}

Thanks so much for the help. First: I've tried your code through the links you provided but I can't get it to run. Any ideas? Second: Sheet 2 is perfect. What I'm trying to do is calculate cost during a task (which normally takes ~2 hours). The standard hourly rate is $1000, but at specific times during the task the rate may increase (to $2000) or decrease (to $500) depending on what is currently happening. The user will control what is currently happening (and thereby the current hourly cost rate) via radio buttons. I need to keep track of the cost through time (hence Sheet 2). Thanks again — emunson5, Feb 07 '16 at 01:06
① Likely a permissions issue on a downloaded file. Check the Properties in an Explorer window or save it to a Trusted Location. — , Feb 07 '16 at 01:47

Every 5 seconds record the value in 2 cells in another worksheet VBA

1 Answers1