Cymatic Trader Community

[tony95]

Hi Guys,

I am modifying the "Lay the Field" spreadsheet, and I need to introduce a delay in a
macro without pausing the application. Can I use a For Next loop something like:-

For x = 1 To 1000
Next x

Any help would be greatly appreciated

Best regards
Tony95

[nig299]

Hi Tony

Try this in your code:

Code: Select all

Application.Wait (Now + TimeValue("0:00:10"))

where 10 is the number of seconds you want it to pause.Change to suit.

Cheers,Nigel.

[Gavin]

Hi Tony95,

Be careful when introducing a pause that you don't freeze out Cymatic from being able to send data to Excel and read commands.

I'm not sure, but Application.Wait might cause Cymatic to be unable to talk to Excel for the duration of the imposed delay, so it can't send new price data. This is not necessarily a problem, if you don't care about whether price refreshes are omitted during the pause. However, if that poses a problem for your logic, then you might want to consider an alternative below, which doesn't impose such as precise delay as Application.Wait, but which simply uses the Cymatic refresh intervals to simulate a delay without causing a communication lock within Excel.

To do this you could split your macro into two parts, one that operates before the delay and another part that operates after the delay. Then test for the time elapsed each time that Cymatic refreshes the data. For example put a test within whichever event you are using (Worksheet_Change or Worksheet_Calculate) to see if enough time has elapsed since you processes the first part. The time at which you processed the first part can be stored in a cell.

This would ensure that Cymatic does not get locked out from being able to talk to and control Excel, for the duration of the delay.

For example:
1. Cymatic refreshes data, triggering the Worksheet_Change event.
2. The code in the Worksheet_Change event does part 1 of your code and stores the time at which it did it.
3. Cymatic refreshes data again, triggering the Worksheet_Change event again.
4. This time the code in the Worksheet_Change tests whether enough time has elapsed since you performed part 1, if so it performs part 2.

Kind regards,

Gavin

[tony95]

Hi Nig and Gavin,

Many thanks for your prompt replies. I will try both methods.
I wondered, Gavin if it is possible to include the total "Green To" value in the spreadsheet
as summing column C does not always give the correct figure.
Thanks once again

Kind regards
Tony95

[Gavin]

Hi Tony95,

Yes that is a little tricky. Column C merely shows those runners that have amount that could be greened. So if there are other runners that have already been greened, then the green profits from those runners has to be added to the available profits showing in Column C. I am not 100% sure if users can calculate that in Excel, but I suspect that it would be possible from an analysis of the bet summary columns, together with the column C entries, as follows: The information in the matched bet summary columns (for runners with 0 in column C), should enable you to calculate the green profit on those runners which have already been greened. Then sum those already greened amounts and add the sum of column C (those that haven't been fully greened).

Kind regards,

Gavin

[canuk]

Tony95

For Event triggers or delayed events I find this trick useful.
It is simple Excel code that can be used in Excel and/or monitored and used in VBA.
This will not cause any delays in execution as the clock is always running anyway.

= if(MOD(time,incement)=0,"tick","") or whatever value you want as the result
This simply looking for zero remainder of a division

Example: clock in seconds/4 will produce zero every 4 seconds
i.e. 4,8,12,16,20,........etc

I put a sample here called TickTock.xls http://ge.tt/5ZgBgcK2

canuk