Applying styles in VBA, then pasting into Excel

[Guest]

Following advice posted here, we changed our expensive calculation to work
inside VB arrays instead of Excel, and then paste the results into the
spreadsheet when it was done. This saved considerable time for the
calculation.

Suprisingly this had almost no overall effect on the time of the run. The
reason is that after pasting in the values we loop over the rows in Excel and
apply various colors to some of the fields on a per-row basis. THIS still
takes almost as much time as it did before, so the overall gain is tiny.

Sooo, is there some way to apply the styles in VB "inside" the array? IE, is
there some sort of datatype we can put into the array that we can apply
styles to, that will keep those styles when we copy into the spreadsheet?

 

[Tom Ogilvy]

No. You might be able to apply conditional formatting to get the colors.
(up to 3 conditions).

Also, note that you can't paste an array to the worksheet; so you are
writing, not pasting.

 

[kkknie]

How are you "pasting" the results. If you are doing a loop, you coul
just do the formatting at that time.

I'm confused. Could you post some code of this paste?

 

[Guest]

Also, note that you can't paste an array to the worksheet; so you are
writing, not pasting.

I am using:

vntArray = Sheet1.Range("$C$2:$E$5").Value
Sheet1.Range("$C$2:$E$5").Value = vntArray

If this is what you mean, sure, ok.

Maury

 

[Guest]

How are you "pasting" the results. If you are doing a loop, you could
just do the formatting at that time.

We do a loop, that's what the problem is. The loop is shockingly slow.