Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding colors, patterns, comments to large spreadsheets in bulk
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding colors, patterns, comments to large spreadsheets in bulk
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 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding colors, patterns, comments to large spreadsheets in bul
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 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Colors and/or Patterns in the Same Line | Charts and Charting in Excel | |||
Comments at End of Large Table | Excel Discussion (Misc queries) | |||
Patterns in excel printing as solid colors (not as selected) | Excel Discussion (Misc queries) | |||
excel does not shows colors or patterns in screen, but will print | Excel Discussion (Misc queries) | |||
linking comments between spreadsheets | New Users to Excel |