A bit weird?

[danjuma]

Hi Gary,

Using version 1.1.066x15, and excel 2007. Was monitoring the prices of some horses in excel and BA earlier on this afternoon, and noticed 2 things. Firstly, for some reason, the data (in this case numbers) in two cells in the spreadsheet were shaking (like jumping up and down). Not changing in value, same value but just shaking/vibrating.
Secondly, one of the cells displaying the current lay price for one of the horses was for a brief period (couple of minutes or so) not in sync with the figure being displayed on BA. In this instance, the lay odds on BA was 8.6, while the lay odds in the cell in the spreadsheet was 8.4 (or probably the other way round, can't remember exactly now), while other cells were updating in sync with BA (so not a case of the sheet not updating).

Thanks
Dan

[GaryRussell]

Doesn't sound right. All the odds are updated at the same time so this should be impossible. I'll investigate, but not sure I am going to find anything unless it happens regularly.

Are you sure it wasn't any VBA writing to the same cells as BA? It could also explain the shaking.

[danjuma]

Hi Gary.

I have some vba code in the sheet. The cell with odds not updating in sync has the following formula (or similar, as can't remember which cell exactly in the range, but the cells in the range (I5:I40) have the same formula, excepting their cell reference)

=IF(OR(I10="",AR10=FALSE,Win!H10<1),"",IF($AS$2=TRUE,Win!H10,AG10))

where AG10 has the following, making use of your vba code for plusTicks/minusTicks).

=IF(I10="","",IF(AU10=FALSE,plusTicks(J10,AT10),plusTicks(Q10,AT10)))

The two cells that were shaking, have the following similar formula (excepting their cell references, as they are among a range of cells (AF5:AF40):

=IF(AND(K7="M",AX7=0),VLOOKUP(I7,Win_MyBets!$H$2:$L$40,5,0),IF(AND(K7="M",AX7=1),VLOOKUP(I7,Place_MyBets!$H$2:$L$40,5,0), ""))

and none of the cells reference a vba code nor does a vba code reference any cells.

Can email you the sheet if you want.

Cheers
Dan

[GaryRussell]

So are you saying you saw the actual odds in column H change or the column containing your formula?

Is it still happening? If not then I don't think a copy of your spreadsheet will help.

[danjuma]

So are you saying you saw the actual odds in column H change or the column containing your formula?

Is it still happening? If not then I don't think a copy of your spreadsheet will help.

The odds in the column containing my formula not changing in sync with BA.

I have not tried the sheet again yet, will possibly try it again sometime this afternoon.

Cheers
Dan

[GaryRussell]

I've checked my code again and I cannot see any way that the wrong odds could be output to Excel.
It is far more likely your formula is not doing what you expect than BA outputting the wrong odds to column H. See what happens this afternoon. If you think it's out of sync then check the cells that BA is actually outputting, not cells derived from them, these should be in sync.

[danjuma]

Ok, will do. Cheers

[danjuma]

Tried it again briefly yesterday afternoon. My sheet was quite slow updating (hour glass spinning for an unusually long time), and my cursor was jumping about, i.e pointing to tick a form control tick box for example, and another gets ticked instead). Anyway, I decided to cut down on my vba code. Got about 35 active x spin buttons, each with a little similar vba code. Found a way to achieve same thing using form control spin buttons instead, without vba code, so got read of the active x spin buttons and associated vba codes.

The sheet is now responding better. So, one could conclude that may be the problem I was having was related to the code. Though not completely convinced, as don't think my code was that excessive, and also been testing the sheet for a week now with same code and was fine up until two days ago when I first noticed the problem. Except if the problem has been there all along and I just did not notice.

Cheers
Dan