Guardian -> Excel update speeds

Discussion regarding the spreadsheet functionality of Bet Angel.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

In the sourcesheet range a1 i've got the name of the sheet i want to monitor.

The sub will only be called if changes happen on that sheet.
You do not have the required permissions to view the files attached to this post.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

nigelk wrote:
Tue Jul 03, 2018 9:22 am
In the sourcesheet range a1 i've got the name of the sheet i want to monitor.

The sub will only be called if changes happen on that sheet.
Thanks nigel, much appreciated. I hadn't thought about going down the Me. route.

I'm still not sure why If Target.Address = Sheets(SourceSheet).Range(ValueCellRef).Address Then doesn't work but sometimes it's not worth trying to find out if a workaround does the job.

It might all come down to VBA internals and the fact they've only ever provided a WorkSheet_Change event rather than Workbook_Change event. I don't think 'Target' is aware of what sheet it relates to and just returns K10, F4 etc and not the full reference including the sheet name.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

If you want something that works on all sheets and returns the sheet name and address, put in in as a workbook event (not as a sheet event) and also use the parent property

Code: Select all

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


MsgBox ActiveCell.Parent.Name & ": " & ActiveCell.Address

End Sub
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

nigelk wrote:
Tue Jul 03, 2018 2:56 pm
If you want something that works on all sheets and returns the sheet name and address, put in in as a workbook event (not as a sheet event) and also use the parent property

Code: Select all

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


MsgBox ActiveCell.Parent.Name & ": " & ActiveCell.Address

End Sub
That's the event I was looking for! I'm off and running now. Brilliant.

I think I need to RTFM :) I was a vb guy but never a vba guy.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

As a PS to this one.

Is there anyway to auto 'Remove suspended markets' ? Either std functionality or a workaround.(is there an Alt-keystoke I could auomate in Hotkeys or similar?)

I know the idea is that by using restricted refresh they become irrelevant but I'm definately getting slowdowns once about 10 or so build up. The moment I click 'Remove suspended markets' my performance returns to normal.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Never looked into that aspect of it before.

Have you taken into account the number of formulas you have in each sheet and to see if you can convert them to values where necessary?

For example, if I record data once a second for the first 10 horses from ten minutes out that’s 600*10 rows of data or 6,000 formulas in one sheet. Times that by ten and you’ve got 60,000 formulas in your workbook.

I’m inclined to convert them to values once I’ve finished with them. If on the first row (600 seconds out), I have a formula telling me that the runner price is 5.50, then on the second row (599 seconds out), the first formula is no longer needed, it’s done it’s job, so I can then put something along the lines of “Range A1”=”Range A1”. Value

Also, if you are not going to use your data afterwards, there’s no harm in clearing down the whole sheet once you’ve finished with it.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Thanks nigel.
I don't have any formulas at all. While the event is recording everything goes into a 2 dimensional array and I only write it when the markets switch and a couple of seconds to dump it isn't an issue. I have one small chart which is populated directly from the arrays rather than from data on a sheet. But there's no memory leak, every variable is type correct and everything is fully qualified with a complete clear down when there's a change of market.
Clicking 'remove suspended markets' instantly speeds it up even though my restricted refresh is - 600 to +10. That's fine if I'm nursing it but not much good if I want to leave it running and go out.

Performance goes down from 8 caps/s to about 3. That sounds trivial but not for what I'm doing with it.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”