Using a timer function to accurately log incoming data RRS feed

  • Question



    I am very new to VBA and need to do some coding to record some incoming data from a LabJack data logger into excel. The logger is constantly taking a temperature reading. The LabJack website provided me with some VBA code which fetches the temperature reading at the moment you run the macro, and puts the value into a cell on the excel spreadsheet. What I want to do is add some sort of Timer controlled loop to essentially repeat their macro every 0.001 of a second, for ten seconds, and list this data with a time stamp (so I can graph it).

     Can anyone give me any advice on how to go about doing this? Most of the examples on the internet seem to only use the timer to delay the start of a program, or pause a program something like that. My boss has thrown me into the deep end here and then gone on holidays!


    Thursday, June 28, 2012 11:42 PM

All replies

  • As far as I know, that is not possible - the Timer function returns the number of seconds since midnight with a resolution of 1/60 of a second. So you can't record 1/1000 of a second.

    Regards, Hans Vogelaar

    Friday, June 29, 2012 5:46 AM
  • Thanks for your help. So if I just take the maximum number of points, so every 1 60th of a second, is it possible to accurately log at each of those timer ticks, so I end up with 600 data points?
    Friday, June 29, 2012 6:12 AM
  • I guess you'll just have to run a loop that enters the Timer value in a cell and calls the data logger macro, then clean up the results afterwards.

    Regards, Hans Vogelaar

    Friday, June 29, 2012 6:19 AM
  • Hello Geo2,

    I don't know if the following will assist you or not. It is simply a program to list the times at approx 1/100th of a second intervals. It does not perform this function to the nth degree of accuracy but maybe you can extract what you require from it.

    Note: The Now() function in VBA only increments in 1 second intervals. (At least that is the best I can get out of it) and hense using the worksheet function Now().

    It is largely dependent on how long it takes to read the instrument and I have no way of testing this. The program should run for 10 secs. If it appears to be running longer than this then Ctrl/Break to stop it because it is probably in an eternal loop.

    I think that the inaccuracy of the increments is due to multi tasking taking place in the computer; but not sure of this.

    Simply insert the code into a new workbook and run it initially to see how it increments. then insert a call to the code to read the instrument and insert the value in column C where I have indicated with the comment.

    Sub test2()

        Dim i
        Cells(1, 1) = "Progressive Time"
        Cells(1, 2) = "Time increments"
        Application.ScreenUpdating = False
        Columns("A:A").NumberFormat = "0.00000000000000000"
        Columns("B:B").NumberFormat = "0.0000000000000000000000"
        Columns("C:C").NumberFormat = "0"
        i = 1
            i = i + 1
            'Following line removes days (Int of number) and coverts time to seconds _
             and inserts the result in column A as progressinve time
            Cells(i, 1).Formula = "=(NOW()-INT(NOW()))*86400"
            Cells(i, 1).Copy
            Cells(i, 1).PasteSpecial xlValues
                'Following line removes days (Int of number) and coverts time to seconds _
                 and inserts result in column B and the loop creates a delay _
                 until it is at least .01 secs.
                Cells(i, 2).FormulaR1C1 = "=(NOW()-INT(NOW()))*86400-RC[-1]"
                Cells(i, 2).Copy
                Cells(i, 2).PasteSpecial xlValues
            Loop Until Cells(i, 2) >= 0.01  'Secs/100
            'Insert code here to read instrument and copy into Cells(i, 3)
            'Cells(i, 3) = i 'Used during testing
        Loop While Cells(i, 1) + Cells(i, 2) < Cells(2, 1) + 10   'After 10 seconds
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
    End Sub


    Regards, OssieMac

    Saturday, June 30, 2012 7:11 AM