Solved Adjust Luminosity via VBA of any color value to it's lightest shade (1 Viewer)

[GeoWorld]

You are correct, I'm using Excel VBA. I don't need to convert back, I was just testing your routines and it seemed to me the reverse should work. My ultimate goal is to calculate Microsoft color shading and I found other code that's supposed to be able to apply a shading percentage to HSL (but it doesn't work). If only MS would publish their algorithm or just put the all color numbers in the XML files. Here's some more debugging:

RGBtoHSL: 13020235 to [H] 128.455284552846, 124.556962025316, [L] 128.470588235294
----------------------------------------
============ HSLtoRGB ============
h=0.356820234869017
s=1.24556962025316
l=1.28470588235294
x1=1.63932688011913
x2=0.930084884586748
red=418.028354430379
green=237.171645569621
blue=392.541772151897
===================================
HSLtoRGB: 16772607 from [H] 128.455284552846, 124.556962025316, [L] 128.470588235294
HSLtoRGB: [R] 0, [G] 0, 0
----------------------------------------

As you can see, the red, green, and blue values come out high from HUEtoRGB function in HSLtoRGB function. I used your routines verbatim (other than adding Debug.Print statements).

 

[isladogs]

Hi
I have both RGB to HLS & HLS to RGB converters as part of my Colour Converter app.

Colour Converter

This demonstrates two different methods for selecting and converting colours between the three different formats used by Access: OLE, RGB & HEX

www.isladogs.co.uk

The code isn't mine and isn't actually used in that app so can't vouch for its accuracy. The source for that code was:

VB Helper: HowTo: Convert RGB values into HLS values and vice versa

www.vb-helper.com

I also have a Qqh to RGB converter where H = hue. Can't remember what Q means

 

[GeoWorld]

Mike, I apologize (I'm a fool), your HSLtoRGB function works perfectly (my debug statements above even prove it). I just misinterpreted the results. I decided to go a different route (although I may add your HSL routines to my cColors class). My work requires a lot of data analysis of MS-Access tables. So when I copy Access data to a spreadsheet, the first thing I do is run code to freeze the top row and set it to a background (fill) color using the MS Color Dialog (API call). While MS Color Dialog allows you to specify any color, I wanted to have a consistent set of colors to use so I built my own color chooser form. Originally I tried to select an Excel Theme Color enum within my form but I have not been able to find an algorithm that calculates the shading results that Microsoft does. Thanks for your help.

 

[isladogs]

Why not export to a template Excel file which already has the required formatting then save as a new file to leave the template unchanged for future use

 

[GeoWorld2]

Why not export to a template Excel file which already has the required formatting then save as a new file to leave the template unchanged for future use

Because Microsoft decides what my shading is (via some algorithm), of which I like very few of their shadings. And I have a macro which formats my headers (Row 1), so if I don't remember to set my "Theme" colors (in Page Layout) before I run my SetHeader routine, I don't have the colors I want. In SetHeader I was making an API call to ChooseColorDlg but It's hard to get consistent coloration. Now I have a form that displays the colors I want like so:

I just click on the color I want and then click on "Select" (yes, one extra step), or if I click "Close", my routine will call Choose Color Dialog and I can select any color I want (like when you select More Colors... in the Fill dropdown).

P.S. I had to go to GeoWorld2 because the US Navy won't let me login from my Navy computer.

 

[Gasman]

Why the second username?

 

[isladogs]

@GeoWorld / @GeoWorld2
Although you were upfront about doing so, you shouldn't have 2 separate accounts here.
I'm not sure what the relevance of logging in from work is but you need to resolve the duplication

I'm sorry but I also don't understand your response in post #45.
My suggestion would require you to create a template file in Excel with no data but formatted the way you want.
Then use VBA to export data to that file and save under a new name to preserve the blank template for future use

This assumes that the formatting of columns / rows would be consistent each time.
However, your custom Excel ribbon suggests your situation may not fit that scenario

 

[GPGeorge]

@GeoWorld / @GeoWorld2
Although you were upfront about doing so, you shouldn't have 2 separate accounts here.
I'm not sure what the relevance of logging in from work is but you need to resolve the duplication

I'm sorry but I also don't understand your response in post #45.
My suggestion would require you to create a template file in Excel with no data but formatted the way you want.
Then use VBA to export data to that file and save under a new name to preserve the blank template for future use

This assumes that the formatting of columns / rows would be consistent each time.
However, your custom Excel ribbon suggests your situation may not fit that scenario

For a major university program that issued Certificates based on testing in their lab, I used exactly the technique Colin describes.

We had Excel templates for each of the certificate types--which numbered four or five as I recall. They were similar, but included some different boilerplate text and data points.

Each template contained a "landing page" into which I exported the raw data from the test results.
Each template included a "display page" with heavy formatting, including a image of the equipment in that particular test.
The data cells on the display page were linked to the data in the landing page, or to formulas that aggregated some of the data for summary rows.

The process was not all that simple to set up, but once the templates were in place, creating new certificates and emailing them to clients was routine.

 

[EricRex]

Hey isladogs,

I know this is 3 years on, but I used your Color Picker and Color Converter code in Access 2021 and found a surprise:

In my version, the colors in the Hex color scheme are not listed in RGB order! That is &HFF0000 is not Red. It's Blue.

For some reason, the colors are now BGR. Thus, &H0000FF is Red, &HDDDDFF is pink, and &HFFDDDD is light blue.

Thus, I had to change your HextoRGB and RGBtoHex routines somewhat.

Could that be a localization issue for the US? Strange but true.

Cheers,
Eric

 

[Mike Krailo]

In VBA the Hex codes have to be reversed as you have found out. That's just the way it works in VBA.

 

[isladogs]

Hey isladogs,

I know this is 3 years on, but I used your Color Picker and Color Converter code in Access 2021 and found a surprise:

In my version, the colors in the Hex color scheme are not listed in RGB order! That is &HFF0000 is not Red. It's Blue.

For some reason, the colors are now BGR. Thus, &H0000FF is Red, &HDDDDFF is pink, and &HFFDDDD is light blue.

Thus, I had to change your HextoRGB and RGBtoHex routines somewhat.

Could that be a localization issue for the US? Strange but true.

Cheers,
Eric

US residents don't have localization issues! Its the rest of the world that has to adjust!

I'm UK based but those functions are standard code which I didn't write
I've heard of people having G & B reversed but never R & B
FWIW, for me the colours you specified are as follows witrh one extra:

HEX	RGB	OLE	Colour
0000FF	0,0,255	16711680	blue
DDDDFF	221,221,255	16768477	pale blue
FFDDDD	255,221,221	14540287	pale pink

FFFF00

255,255,0

65535

yellow

 

[Mike Krailo]

For me in VBA, &HFF0000 is a dark blue. So the Red is the least significant bit and Blue is the most significant bit. Maybe it's a royal blue.

 

[The_Doc_Man]

Actually, if you have colors from Excel and bring them to Access, they have to become RGB - but they started as BGR. Excel "prefers" to use something called "color index" (of which there are 64, some of which overlap). Normally they don't work in RGB OR BGR anyway.