Worksheet_Calculate running twice per one refresh cycle?

[DavDvo]

Hi All,
I have multiple market ecxel sheet connected to Guardian and I am running macro every refresh cycle for each market.
To achieve that I used
Private Sub Worksheet_Calculate()
with =C3 in cell D3 (copy of last update time).
Running fine, but after some time and deeper investigation I noticed, that the macro is actually executed 2 times per refresh cycle for given market. First at expected refresh interval (let's say every 1s if 5 markets are loaded with refresh rate 200ms) and then immediately again (with time diff about 0.02s) which is significantly increasing CPU consumption if many markets are loaded and linked.
Any idea what is behind that?
Is there any more efficient way how to execute macro every refresh cycle?
David

[nigelk]

Have you got any other formulas in the worksheet?, and where is the macro? in this workbook, or every sheet?

A copy of your code might help.

Cheers,Nigel.

[DavDvo]

I solved the problem now, at least I think so.
There were two formulas present in the worksheet...
Bets regards
David

[spreadbetting]

Might be worth checking how many writes BA does to the sheet on each refresh, If you enter the following into a sheet linked to BA and view the data in the immediate window in the VBA editor it'll show you the columns written to the sheet. I'm not clued up with BA but know some betting software does more than one write depending on the API calls it makes. If it's doing more than one you can easily have your code exit the sub routine unless the correct amount of columns is updated ie the price data.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
 Debug.Print Target.Address
End Sub