I'm new to Excel prgramming although familiar with VB.
To run my code on a data changein the spreadsheet I am using the "Calculate" event. Is that the right way? Are there any other/better ways to monitor the arrival of new data?
Thanks in advance.
Calculate Event
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
Depends on what you're doing really, if it's all VBA you'd probably want to use Worksheet_Change as they'd be no calculations kicking off your events.
In simple terms a calculate event would fire because of a calculation taking place when the data refreshed i.e =A1+B1 if no calculations occured on the sheet as everything was handled by VBA you'd probably want to use change and that'd fire when upon a refresh. The main thing with your coding is to avoid allowing the code to go into endless loops with each calculation or change re-triggering everything. I use Worksheet_Change in my bots as each refresh sends a set amunt of columns so it's easier to limit your code to fire off only once per refresh.
Here's a page with a much better explanation than I could give on events http://www.cpearson.com/excel/Events.aspx
In simple terms a calculate event would fire because of a calculation taking place when the data refreshed i.e =A1+B1 if no calculations occured on the sheet as everything was handled by VBA you'd probably want to use change and that'd fire when upon a refresh. The main thing with your coding is to avoid allowing the code to go into endless loops with each calculation or change re-triggering everything. I use Worksheet_Change in my bots as each refresh sends a set amunt of columns so it's easier to limit your code to fire off only once per refresh.
Here's a page with a much better explanation than I could give on events http://www.cpearson.com/excel/Events.aspx
Last edited by spreadbetting on Wed Jul 05, 2017 4:03 pm, edited 3 times in total.
Have a look at Worksheet_Change event - lots in Google including http://www.ozgrid.com/VBA/run-macros-change.htm
Put the sub in the code for the "Bet Angel" page and drive things from that - beware changing the sheet itself otherwise you trigger the event again and get lost in a race condition !
There used to be some sample BA downloads for Excel in the appropriate forum section which also give some useful ideas.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
+!spreadbetting wrote: ↑Wed Jul 05, 2017 3:59 pmThe main thing with your coding is to avoid allowing the code to go into endless loops with each calculation or change re-triggering everything.
I've never liked the sledgehammer appraoch of the Change events.
I've always found it better to have a timer loop. I have a 'start/stop' button on my sheet that toggles a RunStatus field and executes a routine a bit like this....
PauseTime = Sheets("Preferences").Range("RecordInterval") ' Set frequency of checks
While Sheets("FrontEnd").Range("RunStatus") = "Yes" And PauseTime > 0
Call CheckForChanges 'and do stuff if key items change
WaitForNext
Wend
and WaitForNext() looks something like this...
Start = Timer ' Set start time.
Do While Sheets("FrontEnd").Range("RunStatus") = "Yes" And Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
It's all proabably a bit old skool, but it's how I skin this particuar cat.
-
- Posts: 165
- Joined: Wed Mar 01, 2017 2:06 pm
Programming books are great for learning the overall language and syntax, also the best practices.
They rarely have something as specific as what you're asking though. I find forums/websites to be the best for when you need to achieve something specific.
StackOverflow I find to be a fantastic resource
They rarely have something as specific as what you're asking though. I find forums/websites to be the best for when you need to achieve something specific.
StackOverflow I find to be a fantastic resource
How odd. As I said in my OP, I am using "Worksheet_Calculate()" and it is working as I had hoped. No formulas, just VB.
I linked some fields from the "Bet Angel" sheet to my sheet and coded the "Worksheet_Calculate()" event for my sheet to do some maths on the data, using VB, and display the result, nothing fancy, just beginner stuff. But it does seem to work OK.
I just wanted to make sure I wasn't going in the wrong direction before I really get stuck in.