Re: count occurences of font color

I agree, your check is faster, but I am not seeing those types of
differences.

If we cull it down to the essence of the discussion:

Sub Tester1()
Dim s As Single, e As Single
Dim num As Long, res As Long
Dim b As Boolean
num = 100000
s = Timer
For i = 1 To num
' res = ActiveCell.Characters(1, 1).Font.ColorIndex
Next
Debug.Print Timer - s
s = Timer
For i = 1 To num
' b = IsNull(ActiveCell.Font.ColorIndex)
' res = ActiveCell.Font.ColorIndex
Next
Debug.Print Timer - s
End Sub


I get differences like this

2.191406 ' using characters
1.378906 ' using isnull and not using characters

--
Regards,
Tom Ogilvy


"keepITcool" <xrrcvgpbby@xxxxxxxxx> wrote in message
news:xn0ei2qeq157zlg000keepitcoolnl@xxxxxxxxxxxxxxxxxxxxx
Tom,

Please rethink: The difference is in the cells which have NOT been
partially formatted.

For a cell which has an entire blue font there is hardly any difference
as the loop will exit on the first character. (although the isnull is
more efficient (factor 10) as it avoids the characters method.)

However:
if a cell is (entirely) formatted as Auto or Red (not blue) then your
code must complete the loop to determine that all
.Characters(i,1).font.colorindex <> 41

Run a test:
1000 rows with text of 30 chars.

.cells.font.colorindex= 41
my code: .05 secs.
your code: .66 secs.


.cells.font.colorindex=xlAutomatic or vbRed
my code: .05 secs.
your code: 20.00 secs




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

Not true. Your isnull test determines the need to loop or not. If
it is null, you loop. No difference there in approaches except the
extra test.

If it isn't null, you check the font color of the range. In that
case, my checking the first character would stop the loop just as
easily with no additional looping..

For Each rCell In rRange.Cells
With rCell
For i = 1 To Len(.Value)
If .Characters(i, 1).Font.ColorIndex = iColor Then
n = n + 1
Exit For
End If
End If
End With
Next

Should be just as effective. No looping through xlautomatic except
in the same situations your original code. would. loop.


.

Relevant Pages

  • Re: count occurences of font color
    ... Dim num As Long, res As Long ... Debug.Print Timer - s ... ' using isnull and not using characters ... as the loop will exit on the first character. ...
    (microsoft.public.excel.programming)
  • Re: count occurences of font color
    ... Tom Ogilvy ... Debug.Print Timer - s ... ' using isnull and not using characters ... as the loop will exit on the first character. ...
    (microsoft.public.excel.programming)
  • Re: Question from newbie
    ... > procedure Sort is ... > if Num not in Index then ... Num loop ... > character and any range of characters will occurred error as below. ...
    (comp.lang.ada)
  • Re: question about thread scheduling
    ... I will try what you suggested, the reason that I didn't use the sleep method ... whatsoever to tamper with the timer tick. ... If the NN run in a different thread as the control loop ...
    (microsoft.public.windowsce.platbuilder)
  • Re: Timer module for interpreter
    ... What I'd like to do is have a fall-back timer so that if some ... loop which is running the code. ... Presumably the flag set by the ... module and checked by the main interpreter loop would also have ...
    (comp.sys.acorn.programmer)