[RESOLVED] Excel Automation - Change color pallette

[camoore]

I am using the late binding method to automate Excel from within a VB6 application. It works fine (thanks to si the geek's tutorial in this Forum).

A limitation seems to be the number of colours available for use in cells. It seems there are just 56 referred to as colorIndex as in this line of code:

oXLSheet.Range("A1:M1").Interior.ColorIndex = 6 'Give top row of table a yellow background

I have read that it is possible in code to modify the Excel default pallette of 56 colours such that, while still being limited to just 56, these can be redefined by code to be any desired RGB value. However the code examples I found refer to VBA and do not seem to work with VB6.

In the case of the above line of code, how could I modify the default Excel palette so that, for example, ColorIndex 6 becomes RGB(255,000,000) [which would make it bright red instead of yellow]?

Suggestions appreciated.

camoore

Wales, UK

[si_the_geek]

I've never done that before, but you should be able to use the VBA code with minor modifications (as shown in the Macros section of my tutorial).

[Spoo]

Camoore

You can specify any color you want by using

oXLSheet.Range("A1:M1").Interior.Color = RGB(255, 255, 0)     ' bright yellow
oXLSheet.Range("A1:M1").Interior.Color = RGB(255, 255, 153)   ' light yellow

If you are not familiar with RGB color specs, you can simulate this
by doing the following in Excel:

Tools > Options > Color > click a color > Modify > Custom

This will display the 3 RGB components for your selected color
(for reference purposes)

HTH
Spoo

[camoore]

Thanks very much si the geek and Spoo.

I had tried a lot of what I thought were sensible adaptations of the ColorIndex default palette approach by amending the VBA code, but without success - hence this post.

I have found the answer I needed in the Spoo response. In retrospect it seems so simple, but I was distracted from trying such a code by reading that Excel "has to use just those 56 ColorIndex values".

It does not. The Spoo approach allows "normal" VB6 RGB specifications for colour in Excel automation. In all probability the Spoo method would also work with Hexadecimal colour specification - but my preference is to work in RGB.

(The background to this post was that I wanted to print a small Excel file created by code in VB6. I wanted the cell background colours to show, but not too vividly such that on certain printers it became difficult to read the text against the background. Therefore I had need to shade the backgrounds more a more subtle way than the default 56 colour Excel palette seems to allow.)

Great solution thanks Spoo. Maybe a worthwhile addition to your Excel automation tutorial at next update si?

Thread to be marked closed.

camoore

Wales, UK

[si_the_geek]

In all probability the Spoo method would also work with Hexadecimal colour specification - but my preference is to work in RGB.

It would work, because they are exactly the same thing... &HFFFFFF is the same as RGB(255,255,255)

They are just written in different ways, and using parameters to the RGB function allows you to read them in decimal rather than hex.

Maybe a worthwhile addition to your Excel automation tutorial at next update si?

Definitely not I'm afraid... the tutorial is already too big (several people ignore large chunks of it because they can't be bothered to read it), and this is by no means something specific to VB6 automation of Excel (it applies at least as much to Excel VBA).

[camoore]

Si, I have been guilty of just what you have said - not reading your tutorial carefully enough (I recently missed the point about late binding and thus how a program may malfunction when exposed to different versions of Excel).

The HEX should indeed (and does) work in place of the RGB(rrr,ggg,bbb) method of colour definition. However I believe that, just to make life easier for us all, in the HEX expression the order of colours is reversed to BGR!. Thus it is &Hbbggrr where bb/gg/rr are the hexadecimal values corresponding to ranges 0-255 when using the RGB method. So for example pure blue would be RGB(0,0,255) or hex &HFF0000&, pure green would be RGB(0,255,0) or hex &HFF00& and pure red would be RGB(255,0,0) or hex &HFF&.[Noting that with hexadecimal expressions lead zeros are usually supressed].

camoore

Wales, UK

[Bobbles]

Camoore

You can specify any color you want by using

oXLSheet.Range("A1:M1").Interior.Color = RGB(255, 255, 0)     ' bright yellow
oXLSheet.Range("A1:M1").Interior.Color = RGB(255, 255, 153)   ' light yellow

HTH
Spoo

I too recommend that Spoo's EXCELLENT tip be broadcast more widely.
I have been stumbling around for years thinking that we were restricted to using 56 Colors.
I only tripped over this thread tonight, whilst Googling around trying to fumble once more with that restrictive ColorIndex
I have read about that ColorIndex a few times over the past few years, and not one of those sources said what Spoo said (mutter, mutter)
Thank you Spoo

[camoore]

Your post just read, Bobbles. I am pleased that the thread has helped you - as it did myself earlier this year.

The very helpful contributions by SPOO and Si The Geek were much appreciated by me at the time, and doubtless by subsequent readers such as yourself.

It has been my experience that this excellent Forum invariably assists greatly in the solution of VB coding difficulties.

I do not know how to promulgate the SPOO suggestion more widely, other than having it remain as a post to this thread which can be searched for via Forum FAC etc.

Am sure that SPOO and Si will appreciate the fact that this thread continues to help folk - that is after all what these threads are for. I for one am glad that the answer to my query has helped you on this occasion.

camoore Wales, UK