Undocumented ColorIndices

[John Coleman]

Greetings,
I created an Excel 2000 macro to allow a user to try out different
colors for the line in an XY-chart series. The key code fragment was

If MySeries.Border.ColorIndex = 56 Then
MySeries.Border.ColorIndex = 1
Else
MySeries.Border.ColorIndex = MySeries.Border.ColorIndex + 1
End If

I attached the macro to a button and start clicking to see the result.
It worked well for a while - then disappeared! Forever. Evidently the
following is true: for series 1 , the automatic ColorIndex is 25
(though in my test graph I had started with ColorIndex 1). When the
code bumped the ColorIndex to 25, Excel reset the colorindex to 57
(and *not* -4105, which would have been annoying but would have made
sense since xlColorIndexAutomatic = -4105).
Now things get wierd: after the colorindex was set to 57, the next
time the macro fired it tried to bump it to 58. No runtime error is
thrown but the effect is to make the series appear to disappear *and*
to reset the colorindex to 57. If you click on the invisible series to
view its formatting the format dialog box itself suggests that the
line should be visible (it isn't that the line has been set to none,
which is what happens if you set the ColorIndex to xlColorIndexNone).
Firing the macro further at this stage just stays trapped in ghost
index 57/58. Curiously, ColorIndex 59 seems to be an alias for 1, but
60 finally throws an error.
At first I hypothesized that charts were using just 1 byte for the
colorindex, but if you explicitly set the ColorIndex to
xlColorIndexAutomatic and afterwards ask it to msgbox the ColorIndex
it displays -4105 and not 57.

I've already made my own work-around, but this behavior is strange.
Are there any other Excel objects that have undocumented ColorIndices?

-John Coleman

 

[Tom Ogilvy]

Excel only supports 56 colors. xlnone is a special case.

http://support.microsoft.com/kb/149170/EN-US/
Sample Visual Basic Code to Create Color Index Table

 

[FrankStone]

hi,
excel only handles 56 colors. that is it limit right now.
other application do more.(lotus = 256 colors)
to find the excel colors and indexes...
in a blank work book paste and run the following code...
Sub macGetColors()

Sheets("Sheet1").Select
Range("B2").Select
Set ci = Range("A1")
ci.Value = 1
Set c = Range("B2")
Do Until ci > 56
Set c2 = c.Offset(1, 0)
Set cnum = c.Offset(0, 1)
c.Interior.ColorIndex = ci.Value
c.Offset(0, 1) = ci.Value
ci.Value = ci.Value + 1
Set c = c2
c.Select
Loop
msgbox("done.")
End Sub

 

[Tom Ogilvy]

Just to add:
Excel can display any color supported by your monitor/windows settings. In
any single workbook it is limited to a subset of 56 of those colors as
selected in the palette.

Frank's code will show the 56 colors currently defined for the palette in
the activeworkbook.

 

[John Coleman]

Thanks for your reply - but I fail to see how looking at documentation
will help in understanding undocumented behavior. My original code
assumed 56 indices, but Excel itself is what is doing something wierd
with indices 57-59.

-John Coleman

Excel only supports 56 colors. xlnone is a special case.

http://support.microsoft.com/kb/149170/EN-US/
Sample Visual Basic Code to Create Color Index Table

(snip)

 

[Peter T]

John,

In charts applying color index's 57/8 & 59 applies automatic (typically)
white & black as set by your system. Cannot apply these to all chart items.
When you return these Excel matches to the closest colours in the palette,
by default 1 is black and 2 is white. If you customize your palette, say
"move" black & white to different palette positions or change your system
colours you will see what I mean, and get different index's returned.

Probably better to avoid using these numbers except as SchemeColors for
automatic black & white, if/when appropriate.

With shapes, apart from the 56 palette colours you can apply most system
colours with ScemeColor 64 to 80 (but not to text).

Regards,
Peter

 

[John Coleman]

Peter -
Thanks for confirming that I wasn't just imagining things. I'll
have to check out the shape objects. I gather from your post that
there is a Windows color palette in the background which shows through
in some undocumented ways.

Have a good day

-John

p.s. - Sorry that us Americans have replaced "colour" by a less
colorful word.