Cymatic Trader Community

[72_square]

I've got the following bit of code which runs ok, but bogs down the computer. There must be something up with it, but being a newb to vba, I'm having trouble pinning it down. Code is:

Private Sub Worksheet_Calculate()
Dim ws As Worksheet
Dim cel As Range
Dim myRow As Long

Set ws = ThisWorkbook.Sheets("Cymatic")

For Each cel In Range("BV8:BV37,CE8:CE37")
myRow = cel.Row
If Range("E4").Value = "False" Then
If IsEmpty(Range("BA" & myRow)) Then Range("BA" & myRow).Value = Range("BV" & myRow).Value
If IsEmpty(Range("BB" & myRow)) Then Range("BB" & myRow).Value = Range("J" & myRow).Value
If IsEmpty(Range("BC" & myRow)) Then Range("BC" & myRow).Value = Range("BP" & myRow).Value
End If

If Range("E4").Value = "True" Then
If IsEmpty(Range("BA" & myRow)) Then Range("BA" & myRow).Value = Range("CE" & myRow).Value
If IsEmpty(Range("BB" & myRow)) Then Range("BB" & myRow).Value = Range("BR" & myRow).Value
If IsEmpty(Range("BC" & myRow)) Then Range("BC" & myRow).Value = Range("BQ" & myRow).Value
End If
Next cel


If Range("BK1").Value = "Clear" Then
Range("BA8:BI37").ClearContents
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim cel As Range
Dim myRow As Long

Set ws = ThisWorkbook.Sheets("Cymatic")

If Not Intersect(Target, Range("BD8:BD37")) Is Nothing Then
For Each cel In Target
myRow = cel.Row
If ws.Range("BD" & myRow).Value = "PLACED" Then
ws.Range("BX" & myRow).Value = ws.Range("BE" & myRow).Value
ws.Range("BW" & myRow).Value = ws.Range("BF" & myRow).Value
End If
Next cel
End If

End Sub



What I'm trying to do:
1) The BV columns basically have a formula in them that gives the "Lay" command. I'm using the worksheet calculate function to detect this as I believe worksheet_change cannot detect changes via formula (correct me if wrong).
2) Once the lay command is given, Lay, odds and stake get copied over to the active parts of the spreadsheet to trigger a bet.
3) Worksheet change event picks up the "Placed" command to copy the odds and stake values placed to another part of the sheet
4) Bit of code to clear the status cells when race goes in-play
5) Worksheet Calculate to do similar with back commands.

All works fine, but slowly. Even unconnected and changing a single cell with have it hanging for a few seconds. Is it looping over and over? (no stack errors). Any thoughts?

Thanks

[Gavin]

You are changing cell contents from inside Worksheet_Change, which causes Worksheet_Change to fire again. So you have to use the INTERSECT command to tell Worksheet_Change to abort when it gets called by changes to cells that you made in the Worksheet_Change, otherwise it goes round in an unintended loop (re-entrancy). You are using the INTERSECT command but not to properly test whether Worksheet_Change was triggered by the cells that you changed.

Code: Select all

If Not Intersect(Target, Range("BD8:BD37")) Is Nothing Then

This above command should be looking for any ranges of cells that you may have changed in the code.

For example, these commands:

Code: Select all

ws.Range("BX" & myRow).Value = ws.Range("BE" & myRow).Value
ws.Range("BW" & myRow).Value = ws.Range("BF" & myRow).Value

are changing cells in BX and BW, so you should be asking about those ranges in your Intersect commands.

The idea is that the intersect command only lets the procedure continue if the cells were not changed by your code.

As your work is being done in Worksheet_Calculate and Worksheet_Change, you might need to check that you are not causing those functions to repeat based on any of the changes that you made. It might be worth putting an INTERSECT inside both of those events. At the moment you have only done it for Worksheet_Change. Actually, I can't remember if the INTERSECT command is needed in WorkSheet_Calculate or not, so you could consider checking that just to make sure.

I haven't checked the logic of the rest of the code.

Kind regards,

Gavin

[72_square]

Gavin

Thank you for the reply. I played about with it for most of Sat and Sun and whatever I did, it was still taking a while to loop. In the short term I have ditched the worksheet_calculate loop and just written everything thing out longhand. This works fine. I presume the loop was reading each of the 30 cells, plus all of the linked cells for each change, whereas this way it is only checking the 30 cells and acting on the ones it needs to? Anyway, it works as it is and it will do for now!

Regards

[Gavin]

Good to hear that.

Happy trading,

Gavin