Sum by Colour Vba clarification please

[ancient333]

Hi All

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

1st method:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Again could someone please confirm if both methods provide accurate results...?
Is one method better than the other?
What are the actual differences?

Thank you in advance for any insight.

Kind Regards

[shg]

Welcome to the board.

Please take a few minutes to read the Forum Rules, and then edit your post to wrap your code with CODE tags.

Thanks.

[ancient333]

Hi

Apologies, code tags added.

[shg]

Please Login or Register  to view this content.

>> Is one method better than the other?

Neither method is very good; color is not data to Excel, and making the function volatile doesn't solve the problem that Excel won't recalculate automatically when a cell's color changes.

In the first function,

o it makes no sense to use a worksheet function to do simple addition, other than that it accommodates text in the range

o all number values on a worksheet are Doubles, so conversion to Single adds unnecessary processing and can introduce error

o ColorIndex returns a Variant/Long; a type conversion to Integer makes no sense

For both functions, Excel versions 2007+ support RGB color; conversion to ColorIndex is a many-to-one mapping, so you could have cells of various color contributing to the result.

[ancient333]

If I don't put single in it rounds up/down.
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)

Both methods seem to work...what are the actual differences between them

(To a vba beginner )
Could you let me know what code you would use?

Thanks.

[shg]

The function I posted, a variation on your second function, doesn't round at all.

[ancient333]

Great...thanks....
Whats the difference between Dim cSum As Single and Dim TCell As Range ?

Also could you please explain
+ cell.Value2
Dim iCol As Long
Dim cell As Range

ie what these values actually relate to?

thanks

[shg]

A Dim(ension) statement declares a variable. The type of variable (Long, Single, Range, among many others) controls the type and range of data it can contain.

You might look on the web for an introductory-level class or book on using VBA with Excel. There's more than be covered in a thread on a board.

[ancient333]

Thank you.

[shg]

You're welcome.