insert time macro

[madmat252]

Hi i would like to insert the time into a cell when i start this macro and add 5 mins to the time and insert i as a finish time at the end of the macro hope you can help as this will help me impress the boss.

Public Sub startReadings()
Dim stAddr As String
Dim noReadings As Integer
Dim interval As Integer
Dim i As Integer
Dim j As Integer
Dim timer
Dim start
Dim val As Integer
Dim ch1 As Integer
Dim ch2 As Integer
Dim ch3 As Integer
Dim ch4 As Integer
Dim handle As Integer
'Dim ch7 As Integer Insertion needed for expansion
'Dim ch8 As Integer to allow six fans to be used
'Dim ch9 As Integer simultaneously
handle = UsbAdc11OpenUnit() 'New USB only OpenUnit call
opened = handle <> 0
If (Not opened) Then
Call MsgBox("Unable to open USBADC11 on main input", vbCritical + vbOKOnly, "Startup Error")
Else
If MsgBox("Hit [enter] to continue, or [esc] to cancel", vbOKCancel, "Start data gather") = vbCancel Then
Exit Sub
End If

stAddr = getParam("No Runs definition cell")
noReadings = Worksheets(getSheet(stAddr)).Range(getAddress(stAddr)).value
stAddr = getParam("Run intervall cell")
interval = Worksheets(getSheet(stAddr)).Range(getAddress(stAddr)).value
start = Now
Worksheets(getParam("Output Sheet")).Range(getParam("Results range")).Clear
Worksheets("TempDpLog").Range("H3:L54").Clear
For j = 0 To noReadings - 1
'wait 2 seconds
Application.Wait (Now() + TimeValue("00:00:02"))

' Get a reading...
' we can call this routine repeatedly
' to get more blocks with the same settings
On Error GoTo failedLibrary
'Call UsbAdc11GetTimesAndValues(handle, times(0), values(0), 2)
'On Error GoTo 0
i = UsbAdc11GetValue(handle, 1, ch1)
i = UsbAdc11GetValue(handle, 2, ch2)
i = UsbAdc11GetValue(handle, 3, ch3)
i = UsbAdc11GetValue(handle, 4, ch4)
'i = UsbAdc11GetValue(handle, 5, ch7)
'i = UsbAdc11GetValue(handle, 6, ch8)
'i = UsbAdc11GetValue(handle, 7, ch9)
' Copy the data into the spreadsheets - first 'logger results'
timer = j 'Now() - start
i = 0
Worksheets(getParam("Output Sheet")).Range(getParam("Output Start Cell")).Offset(j, 0).value = j 'times(i)
Worksheets(getParam("Output Sheet")).Range(getParam("Output Start Cell")).Offset(j, 1).value = (adc_to_mv(ch1)) / 1000
Worksheets(getParam("Output Sheet")).Range(getParam("Output Start Cell")).Offset(j, 2).value = (adc_to_mv(ch2)) / 1000
Worksheets(getParam("Output Sheet")).Range(getParam("Output Start Cell")).Offset(j, 3).value = (adc_to_mv(ch3)) / 1000
Worksheets(getParam("Output Sheet")).Range(getParam("Output Start Cell")).Offset(j, 4).value = (adc_to_mv(ch4)) / 1000

' Then copy the data into TempDpLog
Worksheets("TempDpLog").Range("H3").Offset(j, 0).value = j
Worksheets("TempDpLog").Range("H3").Offset(j, 1).value = (adc_to_mv(ch1)) / 1000
Worksheets("TempDpLog").Range("H3").Offset(j, 2).value = (adc_to_mv(ch2)) / 1000
Worksheets("TempDpLog").Range("H3").Offset(j, 3).value = (adc_to_mv(ch3)) / 1000
Worksheets("TempDpLog").Range("H3").Offset(j, 4).value = (adc_to_mv(ch4)) / 1000

Next
End If
UsbAdc11CloseUnit (handle) 'New USB only CloseUnit call

Call getCoefficients
Exit Sub
failedLibrary:
Call MsgBox(Err.Description, vbCritical + vbOKOnly, "Critical Error:" + Str(Err.Number))
End Sub

 

[Ralajer]

The current time can be output to a cell from VBA with this code

Range("A1").Value = Format(Now, "HH:MM:SS")

Add 5 minutes to the current time with this

Now + TimeSerial(0, 5, 0)

Putting it all together you get something like this

Public Sub startReadings()
    '// Other dim statements
    Dim StartTime As Date
    Dim EndTime As Date
    StartTime = Now
    EndTime = StartTime + TimeSerial(0, 5, 0)
    Range("A1").Value = Format(StartTime, "HH:MM:SS")
    
    '// Body of CODE
    
    Range("A2").Value = Format(EndTime, "HH:MM:SS")
End Sub