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.
Guardian -> Excel update speeds
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
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.
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
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
That's the event I was looking for! I'm off and running now. Brilliant.nigelk wrote: ↑Tue Jul 03, 2018 2:56 pmIf 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
I think I need to RTFM I was a vb guy but never a vba guy.
- 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.
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.
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.
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.
- 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.
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.