Cymatic Trader Community

[Shaun]

Do you need for the status to be cleared more than once, like to place multiple bets or is it just to greenup?

Here is some code i use to clear cells on another sheet after i switch markets, it monitors the value in A1 for a change, you could just change the A1 to any cell and put a formula in that cell that would change under certain conditions, just be careful because every change would trigger this event, there other options to lock this down to only one change but that requires more work.

this goes in the sheet code not a module

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count < 16 Then Exit Sub
Static MyMarket As Variant
Application.EnableEvents = False
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Sheets("Control").Range("J10:J35").Value = ""
End If
Xit:
Application.EnableEvents = True
End Sub


[Gavin]

Hi Shaun,

Keep in mind that if you set Application.EnableEvents to False, Cymatic won't be able to respond to any triggers whilst events are turned off, because Cymatic relies upon 'events' occurring in Excel in the sheets to which it is connected. When events occur in Excel, Cymatic checks for trigger commands and Status cells being empty, in order to find valid command and act upon them.

When your code sets EnableEvents back to true, Cymatic won't instantly detect that you cleared some Status cells. This is because you cleared them whilst the event firing mechanism was turned off.

In which case Cymatic will only detect that there are valid commands with empty Status cells, NEXT time an 'event' is fired in Excel. So if for example, you have formulae in the sheet that relate to the market data cells, then the recalculation of those formulae will trigger fire the Cymatic to detect the triggers when the data is next refreshed, which will obviously delay it responding to your code that cleared the Status cells, until the next refresh occurs, i.e. it is delayed by a duration equal to one refresh cycle (the setting for price refresh intervals in the Cymatic settings).

Even worse would be if the user does not have an formulae in the Cymatic worksheet. In which case Cymatic would only see an event being fired the next time the user changes a trigger cell manually or VBA code changes it (with EnableEvents not set to false).

Strictly speaking you could just comment out the lines that turn the EnableEvents on / off, because you have wisely put it a test at the top of the code block to check whether the changed cells are likely to be the ones that your code just changed (using the intersect command). Obviously without setting EnableEvents to false, your code will fire twice, because it changes cell and thus causes the Worksheet_Change event to fire again. This is fine though because your INTERSECT command prevents an infinite loop.

It could be argued that it is better to put any such VBA code into the Worksheet_Calculate event of a separate sheet (lets call the sheet 'Rules' for example). Then put a simple formula somewhere in Rules that refers to the last refreshed time (cell C4) in the Cymatic sheet. The formula would be:

=Cymatic!C4

This formula would therefore be recalculated every time new data arrive in the Cymatic sheet. This causes Rules' Worksheet_Calculate event to fire. So you can happily put code in Rules' Worksheet_Calculate event, to clear the Status cells in Cymatic, without causing re-entrancy into the Worksheet_Calculate event in Rules.

Kind regards,

Gavin

Kind regards,

Gavin

[Shaun]

Hi Gavin

Thanks for your input, i will run some tests using your ideas, i know in the past i have used code like this to trigger events with out issues or loss of performance but in saying that VBA is not my strongest area and still have room for improvement in coding

[sjaak1943]

Hi Gavin and Shaun,

This does the trick:

Private Sub Worksheet_Calculate()

If Range("BB3").Value > 0 And Range("BA3").Value = 0 Then
Range("BD8:BD33") = ""
End If

End Sub

Thx for thinking with me.

[Gavin]

Hi Sjaak,

You didn't make it clear what is in BB3 and BA3, presumably formulae?

Using Worksheet_Calculate is a good idea, assuming that you have at least one formula in that sheet that is based on the refreshing data.

Thanks and regards,

Gavin

[sjaak1943]

Hi Gavin:

BA3=SUM(AT8:AT33)
BB3=SUM(AQ8:AQ33)+SUM(AW8:AW33)

Btw, what i put in command cell to LAY and KEEP in play? LAY KEEP and KEEP LAY don't place a LAYbet. Only LAY does, all in demo mode.

[Gavin]

Hi Sjaak,

OK, thanks.

The command you asked about, has the syntax LAY KEEP. It works for me! Are you sure that you hadn't forgotten to put values in the stake and odds cells? This is required.

Kind regards,

Gavin

[sjaak1943]

[quote="Gavin"
The command you asked about, has the syntax LAY KEEP. It works for me! Are you sure that you hadn't forgotten to put values in the stake and odds cells? This is required.
[/quote]

Problem solved, my fault!

[marinica1967]

Hi,
Please can someone help me to solve a problem?
I need to activate the macro VBA module automatically and not by pressing the trigger button manual.
VBA macro module with the following code works perfectly if it is run from VBA or attached to the command button:

Sub sbClearCells ()
Range ("BD8: CBD33"). Clear
end Sub

I want to use a command like this:

= If (sum (AW8: AW33) <> 0, sbClearCells (), "") but the command does not work!

if I use another VBA macro mode like this:

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Function SoundMe() As String
Call Shell("C:\Program Files (x86)\Windows Media Player\wmplayer.exe C:\Users\maria\Documents\Cymatic\beep-01.WAV", 0)
SoundMe = ""
End Function
Sub module()
End Sub


command:

= IF (BF 4 = TRUE, SoundMe (), "")

works great!
If you can help me with another code to clean the cells automatically STATUS I will be grateful!
Excuse my bad English. I hope you have understood my problem!
Thank you in advance
Have a nice day

[Gavin]

Hi Marinica1967,

You probably want to put your code in the WorkSheet_Calculate event of a separate Worksheet that you create with the name rules and containing the formula the points to C4 in the Cymatic sheet, as explained in this message:

http://www.cymatic.co.uk/forum/topic652-10.html#p3349

Please read that information carefully to ensure that you do not cause re-entrancy (triggering infinite repeating bets). For example, you should use the INTERSECT command within the WorkSheet_Calculate event of the Rules sheet, to test for the cell that recalculated. So the Rules worksheet can only trigger its Worksheet_Calculate event if cell C4 has changed in the main Cymatic sheet, then you can be sure that your orders that cause other cells to recalculate in the Cymatic sheet will not trigger the same code to clear the status cells in an infinite loop and so won't duplicate the bets.

Kind regards,

Gavin