code question

Hi Gary,

Independently of your code, I would suggest that you expand the ScreenUpdate
wrapper to include calculation and (while you are at it) also include
pagebreak displays. somehing like:

Dim CalcMode As Long
Dim PgBreakMode As Boolean

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

With ActiveSheet
PgBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
End With


' Your code



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

ActiveSheet.DisplayPageBreaks = PgBreakMode

End Sub

---
Regards,
Norman

hi norman:
i added your code. it ran ok, still took a minute and a half. each formula
has 12 links to other files, so they are quite long. they're about 738
characters long to begin with.

thanks for the info and code
-
-


Gary


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:e2$fehBjFHA.3912@TK2MSFTNGP10.phx.gbl...
> Hi Gary,
>
> Independently of your code, I would suggest that you expand the
> ScreenUpdate wrapper to include calculation and (while you are at it) also
> include pagebreak displays. somehing like:
>
> Dim CalcMode As Long
> Dim PgBreakMode As Boolean
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> With ActiveSheet
> PgBreakMode = .DisplayPageBreaks
> .DisplayPageBreaks = False
> End With
>
>
> ' Your code
>
>
>
> With Application
> .Calculation = CalcMode
> .ScreenUpdating = True
> End With
>
> ActiveSheet.DisplayPageBreaks = PgBreakMode
>
> End Sub
>
> ---
> Regards,
> Norman
>
>

BTW, it took about 1 sec on my machine, what do you have?

--
HTH

Bob Phillips

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:Oa9YJACjFHA.1232@TK2MSFTNGP15.phx.gbl...
> hi norman:
> i added your code. it ran ok, still took a minute and a half. each formula
> has 12 links to other files, so they are quite long. they're about 738
> characters long to begin with.
>
> thanks for the info and code
> -
> -
>
>
> Gary
>
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:e2$fehBjFHA.3912@TK2MSFTNGP10.phx.gbl...
> > Hi Gary,
> >
> > Independently of your code, I would suggest that you expand the
> > ScreenUpdate wrapper to include calculation and (while you are at it)
also
> > include pagebreak displays. somehing like:
> >
> > Dim CalcMode As Long
> > Dim PgBreakMode As Boolean
> >
> > With Application
> > CalcMode = .Calculation
> > .Calculation = xlCalculationManual
> > .ScreenUpdating = False
> > End With
> >
> > With ActiveSheet
> > PgBreakMode = .DisplayPageBreaks
> > .DisplayPageBreaks = False
> > End With
> >
> >
> > ' Your code
> >
> >
> >
> > With Application
> > .Calculation = CalcMode
> > .ScreenUpdating = True
> > End With
> >
> > ActiveSheet.DisplayPageBreaks = PgBreakMode
> >
> > End Sub
> >
> > ---
> > Regards,
> > Norman
> >
> >
>
>

it takes about a second here with blank worksheets. i have an athlon64 3200
with a gb of ram.

--


Gary


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:uZBQwpDjFHA.1480@TK2MSFTNGP10.phx.gbl...
> BTW, it took about 1 sec on my machine, what do you have?
>
> --
> HTH
>
> Bob Phillips
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:Oa9YJACjFHA.1232@TK2MSFTNGP15.phx.gbl...
>> hi norman:
>> i added your code. it ran ok, still took a minute and a half. each
>> formula
>> has 12 links to other files, so they are quite long. they're about 738
>> characters long to begin with.
>>
>> thanks for the info and code
>> -
>> -
>>
>>
>> Gary
>>
>>
>> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
>> news:e2$fehBjFHA.3912@TK2MSFTNGP10.phx.gbl...
>> > Hi Gary,
>> >
>> > Independently of your code, I would suggest that you expand the
>> > ScreenUpdate wrapper to include calculation and (while you are at it)
> also
>> > include pagebreak displays. somehing like:
>> >
>> > Dim CalcMode As Long
>> > Dim PgBreakMode As Boolean
>> >
>> > With Application
>> > CalcMode = .Calculation
>> > .Calculation = xlCalculationManual
>> > .ScreenUpdating = False
>> > End With
>> >
>> > With ActiveSheet
>> > PgBreakMode = .DisplayPageBreaks
>> > .DisplayPageBreaks = False
>> > End With
>> >
>> >
>> > ' Your code
>> >
>> >
>> >
>> > With Application
>> > .Calculation = CalcMode
>> > .ScreenUpdating = True
>> > End With
>> >
>> > ActiveSheet.DisplayPageBreaks = PgBreakMode
>> >
>> > End Sub
>> >
>> > ---
>> > Regards,
>> > Norman
>> >
>> >
>>
>>
>
>

Gary,

So where does a minute come from?

Bob

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:%23SHDWQHjFHA.1148@TK2MSFTNGP12.phx.gbl...
> it takes about a second here with blank worksheets. i have an athlon64
3200
> with a gb of ram.
>
> --
>
>
> Gary
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:uZBQwpDjFHA.1480@TK2MSFTNGP10.phx.gbl...
> > BTW, it took about 1 sec on my machine, what do you have?
> >
> > --
> > HTH
> >
> > Bob Phillips

i guess because each formula is over 700 characters long to begin with. my
original routine must be slowed by that, i guess

--


Gary


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:eOEEPWHjFHA.476@TK2MSFTNGP14.phx.gbl...
> Gary,
>
> So where does a minute come from?
>
> Bob
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:%23SHDWQHjFHA.1148@TK2MSFTNGP12.phx.gbl...
>> it takes about a second here with blank worksheets. i have an athlon64
> 3200
>> with a gb of ram.
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
>> news:uZBQwpDjFHA.1480@TK2MSFTNGP10.phx.gbl...
>> > BTW, it took about 1 sec on my machine, what do you have?
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>
>

Did my loopless code help at all?

--
HTH

Bob Phillips

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:%23h5jUbHjFHA.1416@TK2MSFTNGP09.phx.gbl...
> i guess because each formula is over 700 characters long to begin with.
my
> original routine must be slowed by that, i guess
>
> --
>
>
> Gary
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:eOEEPWHjFHA.476@TK2MSFTNGP14.phx.gbl...
> > Gary,
> >
> > So where does a minute come from?
> >
> > Bob
> >
> > "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> > news:%23SHDWQHjFHA.1148@TK2MSFTNGP12.phx.gbl...
> >> it takes about a second here with blank worksheets. i have an athlon64
> > 3200
> >> with a gb of ram.
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> >> news:uZBQwpDjFHA.1480@TK2MSFTNGP10.phx.gbl...
> >> > BTW, it took about 1 sec on my machine, what do you have?
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >
> >
>
>