Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying styles in VBA, then pasting into Excel
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying styles in VBA, then pasting into Excel
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. -- Regards, Tom Ogilvy "Maury Markowitz" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying styles in VBA, then pasting into Excel
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? -- kkkni ----------------------------------------------------------------------- kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754 View this thread: http://www.excelforum.com/showthread.php?threadid=26967 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying styles in VBA, then pasting into Excel
"Tom Ogilvy" wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying styles in VBA, then pasting into Excel
"kkknie" wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
some unknown Cell styles are shown in cell styles | Excel Worksheet Functions | |||
Excel 2007 Styles | Setting up and Configuration of Excel | |||
Stop carrying over text styles when pasting | Excel Discussion (Misc queries) | |||
Excel Cell Styles - Quick Styles? | Excel Discussion (Misc queries) | |||
Styles in Excel | Excel Discussion (Misc queries) |