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.