Results 1 to 11 of 11
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    As I already noted in this thread I'm working with huge data sets.

    The final step in this process is to color the cells depending on their content.
    This takes - forever - hours even.

    So, I'm wondering about strategies.
    Originally I used a For Each Next loop to move through each cell and loop through the options of cell value and colour.
    This was really slow (apparently according to Bullen, Bovey and Green) loops within loops can slow down exponentially with really large data sets (now I know this for myself).

    So now, I'm using the fact that there are fewer colors to be applied (bins) than there are cells. Instead of looping through each cell - I'm finding the cells that match the data value and applying the color.
    Here's snippet of the code:
    Code:
    'Set the default colour of cells to the first bin
    	Range("ChromaArray").Interior.ColorIndex = ArrayColourBins(1, 2)
    lngCounter = 2 'set the counter
    For lngCounter = 2 To lngBinCount 'loop through each bin
    varValue = ArrayColourBins(lngCounter, 1) 'label of bin
    varCheckBin = ArrayColourBins(lngCounter, 2) 'colour
    With Selection
     	Set rngChroma = .Find(varValue, LookIn:=xlValues) 'within the ChromaArray look for the matching bin label
    	If Not rngChroma Is Nothing Then 
     	firstAddress = rngChroma.Address
     	Do
     	rngChroma.Interior.ColorIndex = varCheckBin 'set the cell colour to the bin
     	Set rngChroma = .FindNext(rngChroma)
     	Loop While Not rngChroma Is Nothing And rngChroma.Address <> firstAddress
    	End If
    End With
    Next lngCounter
    Is there a better strategy out there? One I haven't thought of?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I think it would depend on your situation, but you could also try filtering the data, or loading it into an array to read, before checking the values and colouring if appropriate.
    There is also no point to the Is Nothing test in this line:
    Code:
    Loop While Not rngChroma Is Nothing And rngChroma.Address <> firstAddress
    since you won't be in the loop if the range is nothing, and the .address check would error if it were Nothing.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by rory View Post
    I think it would depend on your situation, but you could also try filtering the data, or loading it into an array to read, before checking the values and colouring if appropriate.
    There is also no point to the Is Nothing test in this line:
    Code:
    Loop While Not rngChroma Is Nothing And rngChroma.Address <> firstAddress
    since you won't be in the loop if the range is nothing, and the .address check would error if it were Nothing.
    Am I missing something?

    If you look at it like this: Loop While (Not (rngChroma Is Nothing)) and (rngChroma.Address <> firstAddress)

    The left side of the AND is TRUE only when rngChroma is not empty
    and FALSE when rngChroma is empty ... remember the Not reverses things.

    Thus, the second half of the test is only evaluated if rngChroma has data which is what is the desired state.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by RetiredGeek View Post
    Am I missing something?
    ...
    the second half of the test is only evaluated if rngChroma has data which is what is the desired state.
    That last line is, unfortunately, not the case. Both parts of the And are evaluated regardless.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    When you are creating your array of references, can you add a colour attribute to the array. Then you can loop through the array and apply the required colour?

    cheers, Paul

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by rory View Post
    That last line is, unfortunately, not the case. Both parts of the And are evaluated regardless.
    Sorry about that, I ran a test to confirm and you're correct, I must have been thinking in another
    language because I know I've programmed in languages that shortcut an AND if the left side is
    false...more efficient.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by rory View Post
    I think it would depend on your situation, but you could also try filtering the data, or loading it into an array to read, before checking the values and colouring if appropriate.
    There is also no point to the Is Nothing test in this line:
    Code:
    Loop While Not rngChroma Is Nothing And rngChroma.Address <> firstAddress
    since you won't be in the loop if the range is nothing, and the .address check would error if it were Nothing.
    Do you think filtering could be faster than looping through a find? Hmmm? I guess I'm the tester for this...
    loading it into an array to read - But once I've read it in the array - I still need to work with the physical cells to colour them (one at a time). Please tell me I'm wrong about this.

    Thanks for catching the wacky loop while not.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by P T View Post
    When you are creating your array of references, can you add a colour attribute to the array. Then you can loop through the array and apply the required colour?

    cheers, Paul
    That is in fact what I do.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you filter the data, you should be able to simply colour all the visible cells in one go, rather than looping (subject to the limits of manipulating more than 8192 discrete ranges at once)
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by rory View Post
    I think it would depend on your situation, but you could also try filtering the data, or loading it into an array to read, before checking the values and colouring if appropriate.
    There is also no point to the Is Nothing test in this line:
    Code:
    Loop While Not rngChroma Is Nothing And rngChroma.Address <> firstAddress
    since you won't be in the loop if the range is nothing, and the .address check would error if it were Nothing.
    hmmm - I checked - I got this weird construction from the Excel Help files.
    Which of course, doesn't mean its' perfect - in fact those double negatives seemed designed to trigger headaches.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The Help files are wrong (and have been for many versions!)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •