Never looked at vba/excel before, but was able to reduce run time on an app
by 75% via recorset streamlining.

Still not happy with run times, learned that manipulation of cells is
consuming most of the other 25%.

Learned that perhaps adding values in bulk to spreadsheet (rather than one
by one) would probably improve performance drastically.

So I'm wondering if the pretty substantial color, pattern and comment
manipulation we're also doing can somehow be applied in bulk, perhaps from an
array or similar workaround?

Have a look at this sample code from Dave Peterson.

Sub ColorFormulas2()
'work on an entire range without For Each.........Next
Dim rng As Range

Set rng = Nothing
On Error Resume Next 'just in case there are no formulas
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If rng Is Nothing Then
'do nothing
Else
rng.Interior.Color = vbYellow
End If
End Sub


Gord Dibben MS Excel MVP

On Wed, 9 Apr 2008 05:58:04 -0700, DB042188 <(E-Mail Removed)>
wrote:

>Never looked at vba/excel before, but was able to reduce run time on an app
>by 75% via recorset streamlining.
>
>Still not happy with run times, learned that manipulation of cells is
>consuming most of the other 25%.
>
>Learned that perhaps adding values in bulk to spreadsheet (rather than one
>by one) would probably improve performance drastically.
>
>So I'm wondering if the pretty substantial color, pattern and comment
>manipulation we're also doing can somehow be applied in bulk, perhaps from an
>array or similar workaround?
>

I can give that a whirl but isnt the idea to source the color, pattern etc
decision from something not already in cells, which from my understanding is
the reason for poor performance in the first place?

What about comments, can I source them from an array similar to the way in
which I'll try to source values from an array?

"Gord Dibben" wrote:

> Have a look at this sample code from Dave Peterson.
>
> Sub ColorFormulas2()
> 'work on an entire range without For Each.........Next
> Dim rng As Range
>
> Set rng = Nothing
> On Error Resume Next 'just in case there are no formulas
> Set rng = Cells.SpecialCells(xlCellTypeFormulas)
> On Error GoTo 0
>
> If rng Is Nothing Then
> 'do nothing
> Else
> rng.Interior.Color = vbYellow
> End If
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Wed, 9 Apr 2008 05:58:04 -0700, DB042188 <(E-Mail Removed)>
> wrote:
>
> >Never looked at vba/excel before, but was able to reduce run time on an app
> >by 75% via recorset streamlining.
> >
> >Still not happy with run times, learned that manipulation of cells is
> >consuming most of the other 25%.
> >
> >Learned that perhaps adding values in bulk to spreadsheet (rather than one
> >by one) would probably improve performance drastically.
> >
> >So I'm wondering if the pretty substantial color, pattern and comment
> >manipulation we're also doing can somehow be applied in bulk, perhaps from an
> >array or similar workaround?
> >
>
>