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

Check cell status on Calculate event: resource issues?
 

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

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