Record data from a dynamically changing cell

[itzadrian]

Hey everyone,

I am currently a student and have only been learning VBA for about 9 months now so please be patient with me. Thanks. So heres my problem:
I have a cell and the value is changing about every 1/8 to 1/2 of a second, and I need to record the data and the time of the change and store it another sheet so it looks like this:
A B
Time Value
example:
A B
10:01:01:00 5.5
10:01:01:10 6.0
10:01:01:40 7.2
10:01:02:25 4.8
10:01:02:30 3.1
10:01:02:33 3.4
10:01:02:39 3.9
10:01:02:42 3.5
10:01:02:44 4.4
10:01:03:09 5.9
The time of the changes is erratic and so is the values.

I have no idea really how to write the code. My idea was (very basically written):
if value of cell <> previous value of cell then
record change and time of change
I no its very basic idea, and not even code but I am completley stuff on how to code it.

thanks for any help.
Itzadrian

[rbrhodes]

Hi,

Enter a value in Sheet1 A1. The Worksheet_Change event will post the value to Sheet 2 (average run time of 1.5/100ths of a second).

To see the code right click on the Sheet 1 tab and choose View Code.

There is also a one line sub in module 1 for the button.

Cheers,

dr

[mdmackillop]

Hi DR,
You can skip the Copy/PasteSpecial by inserting the time directly.
[vba].Range("A" & lastrow) = Now
[/vba]

Exactly the same methodology, but compress the code a bit
[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim DestSheet As Worksheet
'A1 changed?
If Not Intersect(Target, Range("A1")) Is Nothing Then
'create object
Set DestSheet = Sheets("Sheet2")
With DestSheet.Range("A65536").End(xlUp).Offset(1)
.NumberFormat = ("h:mm:ss.00")
.Value = Now
.Offset(, 1) = Target
End With
'destroy object
Set DestSheet = Nothing
End If
End Sub
[/VBA]

[mdmackillop]

Hi DR,
I need to retract a bit.
My code is not providing the fractions of a second using Now. I don't understand yet why not, but using Timer gets around this.
[VBA]With DestSheet.Range("A65536").End(xlUp).Offset(1)
.Value = Timer / 3600 / 24
.NumberFormat = "hh:mm:ss.00"
.Offset(, 1) = Target
End With
[/VBA]

[itzadrian]

Thanks rbrhodes and mdmackillop. I am really busy today but I will try your suggestions tonight and try to get it working. I think I understand the code properly, it doesn't seem as difficult as I expected.

thanks again guys I really appreciate.

[rbrhodes]

Hi,

I looked at timer then I found the extra zeros format and it seemed to work. Three heads are better than 1 tho!

Cheers,,
dr

[mdmackillop]

I don't know if it's critical, but it's possible the recording code could interfere with precise timing. Don't know what you can do about it though.

[itzadrian]

hey guys,

sorry I took so long to respond but I have been working away trying to get this piece of code to work, unfortunatley I have had no luck : (

so I will try to explain my predicament a more elaborately and see if you guys have some good suggestions or code, and once again thanks for all your help.

problem:
I am running some stock market quotes through excel and they are in real time so they are updating constantly. So I have, lets say, two cells, A1 and A2. A1: holds the price of MSFT, and A2: holds the price of YHOO, now I do simple math function in cell A3 where I divide cells A1 and A2 (A3=A1/A2). now A3 is updating automatically in real time, as I said before it update every 1/4 second or so. Now, what I am trying to do, is record the data that is in cell A3. The data is changing constantly and so I want to record the data and the time of the change.

The code you guys gave me before (thanks) helped. It did essentially what I wanted, and displayed the recorded data how I want, but that only happened when I manually entered the changes. When I tried to make cell A3, the cell to be recorded, it would record the first value and time but it would never record any of the changes after that.

Thanks for all your help.

Struggling Student - Itzadrian

[itzadrian]

Hey guys,

I had a thought last night. What if instead of recording the data when it changes, I record the data when time changes.
Thus,
If cell A1 was the time (hh:mm:ss.00) and,
If celll A2 was the constantly changing value,
then when cell A1 changes (i.e. 10:00:01.00 to 10:00:01.01) then record the value of A2 and place it in sheet 2 similar to the way before.

Any idea if this makes sense, and any suggestions on how to code it.

Thanks,
Itzadrian

[mdmackillop]

Adjust the decimal timer to suit. The data written to column 6 is just to show speed of cycle.
[VBA]Sub DoTime()
Application.OnTime Now + 0.000008, "DoTime"
Cells(Rows.Count, 6).End(xlUp).Offset(1) = Range("A1")
If Cells(Rows.Count, 4).End(xlUp) <> Range("A1") Then
Cells(Rows.Count, 4).End(xlUp).Offset(1) = Range("A1")
Cells(Rows.Count, 4).End(xlUp).Offset(, 1) = Timer
End If
End Sub

[/VBA]

[itzadrian]

Hey Mdmackillop,

I keep on getting this error:
"Cannot run the macro 'Book1!DoTime'. The macro may not be availble in this workbook or all macros may be disabled.

I made sure macros were enabled.

I know it must be hard dealing with a dull beginner but please be patient.

thanks for your time.
Itzadrian

[rbrhodes]

Hi itza,

You've probably pasted the code in the WorkSheet module instead of a standard module. If so, press <Altt+F11> to open the VBE. Use Insert/Module if needed and paste the code in the Standard module. Also delete it from the sheet Module and save the WorkBook.

The other possibility is that the code IS in a standard module but you've named the module and the sub the same name. Rename the module and you're good to go.

Cheers,

dr