VBA help needed

This is a discussion on VBA help needed within the Technical Analysis forums, part of the Methods category; Gentlemen, I need to modify the following code to enable update on a tick-by-tick basis. I think the line in ...

LinkBack Thread Tools Search this Thread
Old Oct 17, 2007, 3:41pm   #1
Joined Jun 2006
VBA help needed


I need to modify the following code to enable update on a tick-by-tick basis.

I think the line in bold (set at 1-second intervals) is what needs to changed. Any advice appreciated.

Thank you.


Public RunWhen As Double
Public Const cRunWhat = "MyFunc"

Sub StartTimer()
RunWhen = Now + TimeValue("00:00:01")
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
End Sub

Sub MyFunc()

Dim row As Single

row = ActiveSheet.Range("A65536").End(xlUp).row + 1
Cells(row, 1).Value = Time()
Cells(row, 2).Value = [A1].Value
Cells(row, 3).Value = [B1].Value
Cells(row, 4).Value = [C1].Value
Cells(row, 5).Value = [D1].Value
Cells(row, 6).Value = [E1].Value
Cells(row, 7).Value = [F1].Value

End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

Sub ClearArea()
End Sub
grantx is offline   Reply With Quote
Old Oct 17, 2007, 11:23pm   #2
The General's Avatar
Joined Oct 2003

A couple of things:

1. What is stopping it from working is the fact that you are setting the scheduling to 'True' - this doesn't work as it (Now + Timevalue) is not a time which can ever repeat in this format. The same is true of the 'Stop' routine, you can not cancel a schedule for something which cannot be scheduled.

To get it working as is then, take TRUE out of the initial timesetting, delete the line in the 'STOP' oproc entirely. Replace this line with setting a module-level boolean variable. Test for this variable before re-affirming the application.ontime method....

2. This timing methodology is prone to errors and is patchy at best.
A better solution is to download the CCRP Timer control (GOOG it) which is free and fires a timer event where you can handle the capture of your data values. This control is a true timer as opposed to the above 'dirty-workaround' and when you set the interval to a number of milliseconds, that is truly what it is.

IMHO, only use ontime method for initial timed firing of the CCRP timer object if required....

Hope this helps...

The General.
The General is offline   Reply With Quote
Old Oct 17, 2007, 11:24pm   #3
Joined Aug 2003
I would avoid the ontime function. A better method would be to use the built-in event called Worksheet_Change. Click on the worksheet and then select the relevant drop-down boxes in the VBA window.

This will be entered every time any cell on this worksheet changes. So you then need to test for a change in the specific cell that you want to monitor.

The parameter going into this routine is called Target and it is a range. So you can say... If target.row = blah blah and target.column = blah blah else exit.

This is a very quick answer. Hope it makes sense. If not, let me know and I will explain in a bit more detail.
swandro is offline   Reply With Quote
Old Oct 17, 2007, 11:44pm   #4
The General's Avatar
Joined Oct 2003

All well and good, but this is a quick way to fill 65k rows in the spreadsheet...

One would therefore imagine that was the foundation of the requirement to have a timed 'capture' ??

The General.
The General is offline   Reply With Quote
Old Oct 17, 2007, 11:57pm   #5
Joined Aug 2003

I read Grant's requirement differently. He does not want a timed capture - he wants a tick-by-tick capture. Tick updates can presumably arrive at any time. So with my approach, the Change event would only be triggered when a tick update occurs.

That's how I saw it anyway.
swandro is offline   Reply With Quote
Old Oct 18, 2007, 12:27am   #6
Joined Jun 2006
grantx started this thread Come on gentlemen, let’s not fall out.

“setting a module-level boolean variable...If target.row = blah blah and target.column = blah blah else exit.”

My knowledge of VBA is less than patchy, so please bear that in mind.

The code works but only for “intervals”. General, your solution for an interval of milliseconds may just be an extension of this but tick intervals are indeterminate, ie random. If a trade hasn’t occurred for say, three seconds but the interval is 1 second, you’ll get three identical values, which is obviously incorrect.

Swandro, re Worksheet_Change. Can this differentiate between successive trades at identical prices?

If I remember correctly, average max for tick data (bund, for example) is around 80,000 – 100,000 rows.

grantx is offline   Reply With Quote
Old Oct 18, 2007, 12:41am   #7
Joined Aug 2003

After testing that the cell that contains the new value is the one that has changed, as I have already outlined, you could then test the new value against the last value which you would store in a static variable. Because it is static, it will not be lost when you exit from the Change routine.

So it would run something like this:

If variable = value in cell then exit

variable= value in cell

Then do your code for copying the new value into a new row.

Another thing I would do is to have a bit of code to stop this routine trying to run itself when it is already busy. This could happen if two tick changes occur almost simultaneously.

To do this:

Dim IAmBusy as boolean

If IAmBusy = true then exit

IAmBusy = true

all the other code

IAmBusy = false

Hope this makes sense
swandro is offline   Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA Developer job??? gurm Home Trader 7 Nov 30, 2005 9:29pm
help needed spredbetterstop Techies Corner 5 Mar 5, 2004 10:07pm
any techies able to help Excel Vba V. III Techies Corner 3 Sep 19, 2003 9:51am
help needed? techcherry Techies Corner 5 Feb 1, 2003 2:05pm
Help needed please options Techies Corner 6 Jan 23, 2003 3:01pm

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)