Sum by Colour Vba clarification please

[ancient]

Hi All

I have found a couple of different Vba methods for count by colour, but would like clarification please:

1st method:

Function SumByColor(CellColor As Range, rRange As Range)
Application.Volatile
Dim cSum As Single
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function
The original value in line 3 was "Dim cSum As long" but it would then round up/down the results, which I did not want, so "Dim cSum As single" seems to work...is this correct?
I can also replace it with "Dim TCell As Range" this also seems to work
Can someone please explain what the actual differences are?

2nd Method


Function SumByColor(CellColor As Range, SumRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In SumRange
If ICol = TCell.Interior.ColorIndex Then
SumByColor = SumByColor + TCell.Value
End If
Next TCell
End FunctionAgain could someone please confirm if both methods provide accurate results...?
Is one method better than the other?
What are the actual differences?

with the addition of volatile, when the colours are changed and the sheet is saved/closed the figures are updated....is there another way (apart from a calculate all macro)

Thank you in advance for any insight.

Kind Regards

[Kenneth Hobs]

Dim the count as Long. Calls to WorksheetFunction slow things a bit.

Interior color changes do not trigger change events so you can not use the routine as a dynamic UDF. A calculation event will trigger an update if used as a UDF.

Two sites you might want to review:
http://www.cpearson.com/excel/colors.aspx
http://peltiertech.com/using-colors-in-excel/

I normally count color rather than interior color though it matters little in this case.

Function ColorCount(CellColor As Range, ColorRange As Range) As Long Application.Volatile
Dim I As Integer, r As Range, c As Long
I = CellColor.Interior.ColorIndex
For Each r In ColorRange
If I = r.Interior.ColorIndex Then
c = c + 1
End If
Next r
ColorCount = c
End Function

[jolivanes]

Some additional info to check.
If you have a look at this site at the bottom, you'll see why at times you should not use "Long".
http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=108:declaring-variables-using-dim-statement-variable-names-a-variable-data-types-in-excel-vba&catid=79&Itemid=475

[ancient]

Thanks guys

[Bob Phillips]

Some additional info to check.
If you have a look at this site at the bottom, you'll see why at times you should not use "Long".
http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=108:declaring-variables-using-dim-statement-variable-names-a-variable-data-types-in-excel-vba&catid=79&Itemid=475

I admit I didn't read it all, although I saw lots of things to disagree strongly with in the article, but I couldn't see anything suggesting I shouldn't use Long for integer variables. Can you elaborate for my enlightenment?

[ancient]

Holds long integer values and stores only whole numbers....ie rounds up or down

[Kenneth Hobs]

Yes, but in the context of counting number of cells with interior color index, it would always be a "whole" number.

If you wanted to sum the values of cells with the interior color as your subject line suggests, then Double would be the best route.

I guess in the scheme of things, Single does not hurt anything other than taking up more memory.

I normally use Long for row numbers and Integer for column numbers.

Nice seeing you around again xld!

[jolivanes]

"Holds long integer values and stores only whole numbers."
The part I meant was "only whole numbers"
Since I have been caught by it I always think that other people might also.
Belief me Bob, if I may call you that, this was not meant for a "Grand Master" but just for someone with seemingly little experience at the moment to remember.
BTW, this was also not against what Kenneth mentioned. I see now that it looks like it is.
Sorry about that Kenneth. My apologies

[Bob Phillips]

Indeed it does, but as Kenneth says, counting is whole numbers, so I couldn't see what the problem was. If you use the wrong variable type you can always run into issues, you need to know what you are using it for, and the characteristics of said variable and apply accordingly. There is still a place for experience and knowledge! I just thought I might be missing something subtle, but I also wondered if that article's author was saying something just plain wrong. I don't think it is a great article, looks more like a cut and paste of various old articles to me.

Nice to see that you are still here too Kenneth. Doesn't seem to be many of us 'old-uns' still frequenting the forums. On your point, I never use integer any longer, with 64 bit systems integers are cast into longs under the hood and back again, so it is just wasteful. I agree that integer is a more self-documenting type name, but nowadays we can get into problems using it. Memory is rarely an issue with my code, and we need to cater for bigger numbers (look at 64 bit API calls with memory addressing needing LongPtr).