calc
 

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

calc
 

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

calc
 

Thank you
"vezerid" wrote in message
oups.com...
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

calc
 

Is there a way I could get excel to run this code automatically, say every
minute?
"vezerid" wrote in message
oups.com...
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

calc
 

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

calc
 

Thank you very much, that is fantastic.
"vezerid" wrote in message
ps.com...
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

calc
 

Thanks for the feedback. Glad to be of help.

Kostis