calc

[PH NEWS]

Hi all,

I have a spreadsheet with about 20 columns of data, 3 of which are
long-winded array formulas. I have some conditional formats and simple IF
functions in the other 17 columns. I would like to keep the sheet calc to
auto, but this makes entry slow because of the 3 columns of arrays. Is there
a way to exclude the array columns so I can keep the auto calc?

Thanks in advance,

SPL

 

[vezerid]

VBA can calculate just a single range. Something like:

Sub CalcRange
Sheets("Data").Range("E:F").Calculate
End Sub

You can have calculation option to Manual and use a button to call this
procedure.

HTH
Kostis Vezerides

 

[PH NEWS]

Thank you

 

[PH NEWS]

Is there a way I could get excel to run this code automatically, say every
minute?

 

[vezerid]

Yes, you can use the Application.OnTime method. I am providing the full
set of subs that you need. You will need a global variable to remember
the next time a calculation is scheduled, so that you can stop periodic
calculation:

Public NextSchedule As Date

Sub CalcRange()
Sheets("Data").Range("E:F").Calculate
End Sub

Sub StartPeriodicCalculation()
Call CalcRange
NextSchedule = Now + TimeValue("00:01:00")
Application.OnTime NextSchedule, "StartPeriodicCalculation"
End Sub

Sub StopPeriodicCalculation()
Application.OnTime NextSchedule, "StartPeriodicCalculation", , False
End Sub

You call Start once. It will take care of subsequent schedules. Then
you call Stop when you are done. In fact, it might be a better idea if
you place the body of Stop in Workbook_BeforeClose, so that it is
guaranteed to work even if you forget to call Stop explicitly.

Beware, I have used OnTime in the past, even though I tried ways of
ensuring that the scheduling stops, sometimes the cancellation did not
work. The result was that Excel was restarting to execute the scheduled
subs.

HTH
Kostis Vezerides

 

[PH NEWS]

Thank you very much, that is fantastic.

 

[vezerid]

Thanks for the feedback. Glad to be of help.

Kostis