Calculate Event

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
Frogmella
Posts: 220
Joined: Mon May 30, 2011 2:44 pm
Location: Towcester

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.
spreadbetting
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
Last edited by spreadbetting on Wed Jul 05, 2017 4:03 pm, edited 3 times in total.
foxwood
Posts: 390
Joined: Mon Jul 23, 2012 2:54 pm

Frogmella wrote:
Wed Jul 05, 2017 3:33 pm
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.
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.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

spreadbetting wrote:
Wed Jul 05, 2017 3:59 pm
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'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.
User avatar
Frogmella
Posts: 220
Joined: Mon May 30, 2011 2:44 pm
Location: Towcester

Thanks all, I bought an 1100+ page "VBA for Excel" book and have read half of it but the authors still haven't answered the question that prompted this thread. I wonder if they ever do.

I should have just come here first.
welshboy06
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
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

A new Bet Angel sheet contains no formulas, so the calculate event won't work unless you create a formula.

Open up a new BA workbook and everything you see has the possibility to trigger the change event.
User avatar
Frogmella
Posts: 220
Joined: Mon May 30, 2011 2:44 pm
Location: Towcester

nigelk wrote:
Wed Jul 05, 2017 7:10 pm
A new Bet Angel sheet contains no formulas, so the calculate event won't work unless you create a formula.

Open up a new BA workbook and everything you see has the possibility to trigger the change event.
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.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

I linked some fields from the "Bet Angel"
Anything that starts with "=" is a calculation

Ie. "='Bet Angel'!G9"

Will trigger the calculate event
User avatar
Frogmella
Posts: 220
Joined: Mon May 30, 2011 2:44 pm
Location: Towcester

Of course. Thanks.
seanmac
Posts: 21
Joined: Wed Aug 17, 2011 6:48 am

nigel .... he's da man when it comes to excel / vba :D very generous with his time
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”