Heavy/Slow Calculations

I have a workbook that, at certain times, has to make several thousand
calculations. I could just about go to lunch by the time this process
completes. Is there some simple way to speed this up? It seems like I
recall an article some place called a million calculations in a few
seconds????


--
Thanks
Shawn

Hi Shawn,

If not aleady doing so, wrap your code something like:

Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Cells.PageBreak = xlPageBreakNone

'Your code


With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

I assume that your code is written to avoid selections.

For specialist advice and information on matters pertaining to calculation,
visit Charles Williams's DecisionModels site at:

http://www.decisionmodels.com/optspeedd.htm

---
Regards,
Norman



"Shawn" <[email protected]> wrote in message
news:[email protected]...
>I have a workbook that, at certain times, has to make several thousand
> calculations. I could just about go to lunch by the time this process
> completes. Is there some simple way to speed this up? It seems like I
> recall an article some place called a million calculations in a few
> seconds????
>
>
> --
> Thanks
> Shawn

The calculations aren't occuring in VBA but in various formulas embeded in
linked cells.
--
Thanks
Shawn


"Norman Jones" wrote:

> Hi Shawn,
>
> If not aleady doing so, wrap your code something like:
>
> Dim CalcMode As Long
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> Cells.PageBreak = xlPageBreakNone
>
> 'Your code
>
>
> With Application
> .Calculation = CalcMode
> .ScreenUpdating = True
> End With
>
> I assume that your code is written to avoid selections.
>
> For specialist advice and information on matters pertaining to calculation,
> visit Charles Williams's DecisionModels site at:
>
> http://www.decisionmodels.com/optspeedd.htm
>
> ---
> Regards,
> Norman
>
>
>
> "Shawn" <[email protected]> wrote in message
> news:[email protected]...
> >I have a workbook that, at certain times, has to make several thousand
> > calculations. I could just about go to lunch by the time this process
> > completes. Is there some simple way to speed this up? It seems like I
> > recall an article some place called a million calculations in a few
> > seconds????
> >
> >
> > --
> > Thanks
> > Shawn
>
>
>

Hi Shawn,

> The calculations aren't occuring in VBA but in various formulas embeded in
> > linked cells.

I was mislead by the fact that you posted to the programming group.

However, the suggestion that you visit Charles Williams site stands.

---
Regards,
Norman



"Shawn" <[email protected]> wrote in message
news:[email protected]...
> The calculations aren't occuring in VBA but in various formulas embeded in
> linked cells.
> --
> Thanks
> Shawn
>
>
> "Norman Jones" wrote:
>
>> Hi Shawn,
>>
>> If not aleady doing so, wrap your code something like:
>>
>> Dim CalcMode As Long
>>
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> Cells.PageBreak = xlPageBreakNone
>>
>> 'Your code
>>
>>
>> With Application
>> .Calculation = CalcMode
>> .ScreenUpdating = True
>> End With
>>
>> I assume that your code is written to avoid selections.
>>
>> For specialist advice and information on matters pertaining to
>> calculation,
>> visit Charles Williams's DecisionModels site at:
>>
>> http://www.decisionmodels.com/optspeedd.htm
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Shawn" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a workbook that, at certain times, has to make several thousand
>> > calculations. I could just about go to lunch by the time this process
>> > completes. Is there some simple way to speed this up? It seems like I
>> > recall an article some place called a million calculations in a few
>> > seconds????
>> >
>> >
>> > --
>> > Thanks
>> > Shawn
>>
>>
>>

Sorry about posting in the wrong place. I generally do have programming
questions and my shortcut leads me straight here.

Actually, the program in question is one I wrote several years ago. A
"canned" report sheet referrences a very, very, very large table of data. It
allows the user to manipulate several variables (date, proivder, county,
type) which then casues the "canned report sheet" to change its results.
There are 1,000s of calculations occuring when the provider changes a
variable.

I may just need to re-write the whole thing and see where my programming
leads me considering the knowledge I have gained over the years.


--
Thanks
Shawn


"Norman Jones" wrote:

> Hi Shawn,
>
> > The calculations aren't occuring in VBA but in various formulas embeded in
> > > linked cells.
>
> I was mislead by the fact that you posted to the programming group.
>
> However, the suggestion that you visit Charles Williams site stands.
>
> ---
> Regards,
> Norman
>
>
>
> "Shawn" <[email protected]> wrote in message
> news:[email protected]...
> > The calculations aren't occuring in VBA but in various formulas embeded in
> > linked cells.
> > --
> > Thanks
> > Shawn
> >
> >
> > "Norman Jones" wrote:
> >
> >> Hi Shawn,
> >>
> >> If not aleady doing so, wrap your code something like:
> >>
> >> Dim CalcMode As Long
> >>
> >> With Application
> >> CalcMode = .Calculation
> >> .Calculation = xlCalculationManual
> >> .ScreenUpdating = False
> >> End With
> >>
> >> Cells.PageBreak = xlPageBreakNone
> >>
> >> 'Your code
> >>
> >>
> >> With Application
> >> .Calculation = CalcMode
> >> .ScreenUpdating = True
> >> End With
> >>
> >> I assume that your code is written to avoid selections.
> >>
> >> For specialist advice and information on matters pertaining to
> >> calculation,
> >> visit Charles Williams's DecisionModels site at:
> >>
> >> http://www.decisionmodels.com/optspeedd.htm
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Shawn" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a workbook that, at certain times, has to make several thousand
> >> > calculations. I could just about go to lunch by the time this process
> >> > completes. Is there some simple way to speed this up? It seems like I
> >> > recall an article some place called a million calculations in a few
> >> > seconds????
> >> >
> >> >
> >> > --
> >> > Thanks
> >> > Shawn
> >>
> >>
> >>
>
>
>

Shawn,

I did a similar conversion in one of my workbooks where I drove the
calculations off of a command button, but you could use the worksheet_change
event to run a vba loop(s) to update your table(s). You might test for
certain edit cells if you don't want updates on every single change, or give
yourself a maintenance mode to make numerous changes and then calculate at
when you're exit edit mode. I use a vertical scan loop with an embedded
horizontal scan loop where the calculations take place. Whatever makes sense
for your data set. As I recall, pulling all of those formulas out of the
individual cells and replacing with vba code also reduced my file size as a
hidden benefit.

Roy

"Shawn" wrote:

> Sorry about posting in the wrong place. I generally do have programming
> questions and my shortcut leads me straight here.
>
> Actually, the program in question is one I wrote several years ago. A
> "canned" report sheet referrences a very, very, very large table of data. It
> allows the user to manipulate several variables (date, proivder, county,
> type) which then casues the "canned report sheet" to change its results.
> There are 1,000s of calculations occuring when the provider changes a
> variable.
>
> I may just need to re-write the whole thing and see where my programming
> leads me considering the knowledge I have gained over the years.
>
>
> --
> Thanks
> Shawn
>
>
> "Norman Jones" wrote:
>
> > Hi Shawn,
> >
> > > The calculations aren't occuring in VBA but in various formulas embeded in
> > > > linked cells.
> >
> > I was mislead by the fact that you posted to the programming group.
> >
> > However, the suggestion that you visit Charles Williams site stands.
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Shawn" <[email protected]> wrote in message
> > news:[email protected]...
> > > The calculations aren't occuring in VBA but in various formulas embeded in
> > > linked cells.
> > > --
> > > Thanks
> > > Shawn
> > >
> > >
> > > "Norman Jones" wrote:
> > >
> > >> Hi Shawn,
> > >>
> > >> If not aleady doing so, wrap your code something like:
> > >>
> > >> Dim CalcMode As Long
> > >>
> > >> With Application
> > >> CalcMode = .Calculation
> > >> .Calculation = xlCalculationManual
> > >> .ScreenUpdating = False
> > >> End With
> > >>
> > >> Cells.PageBreak = xlPageBreakNone
> > >>
> > >> 'Your code
> > >>
> > >>
> > >> With Application
> > >> .Calculation = CalcMode
> > >> .ScreenUpdating = True
> > >> End With
> > >>
> > >> I assume that your code is written to avoid selections.
> > >>
> > >> For specialist advice and information on matters pertaining to
> > >> calculation,
> > >> visit Charles Williams's DecisionModels site at:
> > >>
> > >> http://www.decisionmodels.com/optspeedd.htm
> > >>
> > >> ---
> > >> Regards,
> > >> Norman
> > >>
> > >>
> > >>
> > >> "Shawn" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >I have a workbook that, at certain times, has to make several thousand
> > >> > calculations. I could just about go to lunch by the time this process
> > >> > completes. Is there some simple way to speed this up? It seems like I
> > >> > recall an article some place called a million calculations in a few
> > >> > seconds????
> > >> >
> > >> >
> > >> > --
> > >> > Thanks
> > >> > Shawn
> > >>
> > >>
> > >>
> >
> >
> >