Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell status on Calculate event: resource issues?
Hi. I have a resource issue question related to checking a single cell on
calculate events in a financial spreadsheet (Excel 2007). I would like to insure that I am doing this in the most efficient way because the spreadsheet is very calculation-intensive. It is fed via a DDE tick-by-tick stock quote feed and thus recalculates constantly. I need to check one single cell on calculate events (the cell contains a formula, hence my choice of the calculate event). Please see code at bottom. I am not concerned about the macros overhead because they won't run on every calculate and that part is covered. However, since the cell will be checked on each calculation event and there are so many of them, can this create a bottleneck, or is the check of the 2 initial conditions trivial as programmed. Thanks in advance for advice and info. Seattle63 Private Sub Worksheet_Calculate() Application.EnableEvents = False If Range("$BX$57").Value 0 And Range("A60").Value = "Auto" Then Worksheets("Trade").Application.Calculation = xlCalculationManual 'If 1st condition true Then execute macro1 'If 2nd condition true Then execute macro2 End If Worksheets("Trade").Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cell status on Calculate event: resource issues?
Hi. Thanks for your reply. I have tested it and it works. However, because of
the DDE stock feed constant updates (it's tick-by-tick) the macro runs litterally as often as the calculate event can be fired by Excel. I am concerned that this bogs down the spreadsheet unnecessarily and can affects the timeliness of the DDE updates. My first choice had been to see if I could get away with a change event but the cells being tested and all those from which it is derived contain formulas. "Nigel" wrote: Cannot see how you can make it any simpler. The calculate event will run once for each sheet recalculation regardless of the number of cells with formulae. -- Regards, Nigel "Seattle63" wrote in message ... Hi. I have a resource issue question related to checking a single cell on calculate events in a financial spreadsheet (Excel 2007). I would like to insure that I am doing this in the most efficient way because the spreadsheet is very calculation-intensive. It is fed via a DDE tick-by-tick stock quote feed and thus recalculates constantly. I need to check one single cell on calculate events (the cell contains a formula, hence my choice of the calculate event). Please see code at bottom. I am not concerned about the macros overhead because they won't run on every calculate and that part is covered. However, since the cell will be checked on each calculation event and there are so many of them, can this create a bottleneck, or is the check of the 2 initial conditions trivial as programmed. Thanks in advance for advice and info. Seattle63 Private Sub Worksheet_Calculate() Application.EnableEvents = False If Range("$BX$57").Value 0 And Range("A60").Value = "Auto" Then Worksheets("Trade").Application.Calculation = xlCalculationManual 'If 1st condition true Then execute macro1 'If 2nd condition true Then execute macro2 End If Worksheets("Trade").Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking status of issues based on first two digits of a field | Excel Discussion (Misc queries) | |||
Check Box Status | Excel Programming | |||
Cell wrapping event, does it exist? How can I calculate cell filli | Excel Programming | |||
how do I create event-activated macros that check every cell? | Excel Programming | |||
Status bar event ? | Excel Programming |