Preface:
Color space representations are important for graphics rendering and a thorough understanding can help creators to effectively use them. Digital artists often are familiar with the standard RGB in sRGB or opRGB color space and its hexadecimal integer representation of the normally qualia-esque thing that is color perception. R, G, and B components then represent the long, mid, and short photosensitive cone ranges respectively. This approximates most colors a human can feasibly see, while keeping data rather small. However, this isn’t the only way to understand color and light.
HSV similarly stores 3 values that each represent light perceived, but instead represent the hue, saturation, and value as the channels. Files often do not store in color data HSV, instead opting to use RGB or color indexing, but HSV is arguably more useful artistically. Since tones and shades are modifications of the variables saturation and value, the exact effect of changing those is immediately obvious. Decreasing saturation will make the color less saturated. This same property does not easily apply in RGB, as decreasing the saturation while maintaining the general color value by small margins is nearly impossible without altering the hue. This is also true in lower color depth situations regardless of color space representation.
Most modern software is well equipped to go between the spaces no problem. That is with the exception of the scripted side of Office (2016 & prior at least). Excel (and other Office applications) will let you adjust the HSV while selecting a color through the UI, but it will store the color as a hexadecimal integer of RGB (stored in BGR order). Adjusting all the colors I want to the specific saturation and value necessary to all items manually is extremely time consuming and mind numbing. This is where VBA comes in handy. We have a problem that can be automated, or more accurately two problems.
Problem 1: Convert RGB values to HSV space
Ironically the easiest part of this how thing.
- Split up the single hexadecimal integer [values ranging from 0x000000 to 0xFFFFFF] into 3 floats [values ranging from 0 to 1] (doubles as named in VBA, though singles would likely suffice)
- Find the minimum, maximum, and range of those floats
- Given order cycle[R, G, B, R, G]
- Where the first instance of maximum is indexed as
i
- Where the difference of maximum and minimum is
d
hue = {d≤0: 0, d>0: (60°*(cycle[i+1}-cycle[i+2]))/d+i*120°) mod 360°saturation = d/cycle[i]value = cycle[i]
This all seems simple enough, building functions that effectively represent each step.
Problem 1.1: Interpreting RGB
This problem is basically built into Excel. Start by transferring the decimal long returned using the worksheet function Dec2Hex with the optional minimum return length as 6. The returned string will be 0xBBGGRR, splitting it with the function Mid, and converting it back to decimal with the worksheet function Hex2Dec before dividing it by 255. I recommend adding the optional argument for reversing the order of the input so you can use it for 0xRRGGBB order as well.
Problem 1.2: Hue Calculation
Calculating minimum, maximum, and range is so light weight, there is little reason to separate this as its own step. As well, my as-described functions work well in Python, the lack of lists makes this type of instancing a little less helpful. However, building a switch-case where you compare the maximum to each input instead is effectively the same. I also recommend adding 360° before doing function modulo on these values as function modulo behaves strangely when given negative floats to find the remainder of. While I elected to return a double for hue, I have only seen that function produce integers.
Problem 1.3: Saturation and Value Calculation
It’s honestly harder to get Office to not autocomplete sat as Saturday than calculate these. Value is simply the maximum return on inputs R, G, B while saturation is the ratio of range over maximum of those input.
Problem 2: Going back to RGB
I, as many would assume, thought this step would be easy. For just as many techniques posted only for going from RGB to HSV, there are as many bad techniques for going to RGB from HSV. Whether these functions are bad because of how VBA handles them or just because they were designed before 8-bit color depth is beyond me. This section will read a bit different than the last.
Problem 2.1: The piecewise function approach
- Where
h' = floor(hue/60°)
- Where
c = val*sat
- Where
x = c*(1-|(h' mod 2)-1|
- Where
m = v-c
0 ≤ h' < 1 ⇒ (R₁, G₁, B₁) = (c, x, 0)1 ≤ h' < 2 ⇒ (R₁, G₁, B₁) = (x, c, 0)2 ≤ h' < 3 ⇒ (R₁, G₁, B₁) = (0, c, x)3 ≤ h' < 4 ⇒ (R₁, G₁, B₁) = (0, x, c)4 ≤ h' < 5 ⇒ (R₁, G₁, B₁) = (x, 0, c)5 ≤ h' < 6 ⇒ (R₁, G₁, B₁) = (c, 0, x)(R, G, B) = (R₁ + m, G₁ + m, B₁ + m)
Why this doesn’t work, I actually have no idea. As you will see with my actual solution, the idea cuts a slice of the color wheel pie into six sections. Each section represents the rise of one channel with the fall of another. The results I was seeing flattened results to the 6 slice edges.
Problem 2.2: The function of n approach
- Where
k(n) = (n + hue/60°) mod 6
- Where
f(n) = val - val*sat*max(0, min(k(n),4 - k(n), 1)
(R, G, B) = (f(5), f(3), f(1))
Similarly, I cannot figure out why this wouldn’t simply work. Everything about the function makes some sense, but it resulted in 60° separations rather than actual hues. Maybe it has to do with how VBA does the function modulo.
Problem 3: Dissecting the problem
There was no use of modifying the hue, saturation, and value if there was no way to go back to RGB components. Logically, I needed to visualize the problem. Using Desmos, an online free graphing tool, I rebuilt the hue space graph from the piecewise function. Or more correctly recreate the graph it was emulating. It’s not that hard to recreate it in text, ▔╲▁▁╱▔. It represents over the circular period how much red is present in a given hue. Green is just that offset by 240° and blue by 120°. If I can accurately make that and have it function, we’d have our answer.
Problem 3.1: With Hue
As described before, red can be calculated from a piecewise function characterized by alternating highs and lows through a linear reduction between. This piecewise is periodic with relation to the cycle length, in our case 360° or 2π radians. Naturally, our first step is to find the modulo (remainder) of the input hue: h' = hue mod 360°. For added flexibility, I changed the period to be denoted as c: c = 360° ⇒ h' = hue mod c. This may seem silly, but altering hue space is the end goal, allowing ourselves the future ability to change the period entirely should be the case. We have found our input for the piecewise function, now to figure out what that piecewise function is.
Segments as you find online described as 6 slices of 60°, but why is that? There is always going to be high state, one in low state, and one in state change. We can then assume that there are 3 segments of high each 120° that are then bisected by which other channel is in state change. The appearance of all three functions overlapping is reminiscent of a suspension bridge and triangle wave forms.
Problem 3.2: High and low states
Next, what is the high state? This is as suggested by the name, the channel that is the strongest or maximum. This just so happens to be our value as calculated before, so t(h') = val ⇐ val ∈ [0, 1]. That last part does seem a bit confusing but is an unfortunate requirement of these equations. In theory, this all still works with 3 floating point values for R, G, and B where they exist outside of 0 to 1, but no sources I found online suggest this would work.
Low state is naturally, the opposite of high state, being the weakest or minimum channel. This won’t come as freely as high state, but it is embedded in our saturation, being the ratio between difference and maximum. Multiplying saturation by value returns our difference, multiplying by negative one and adding our value will return the minimum, simplified as f(h') = val*(1 - sat) ⇐ val ∈ [0, 1] & sat ∈ [0, 1].
Our favorite restrictions are back, but here, they begin to make much more sense. With a negative saturation, the minimum would be greater than our maximum, which doesn’t make sense, so restriction could be sat ∈ ℕ. However, if value and or saturation is greater than 1, the output of (R, G, B) ∉ [0,1]³. People dealing with realistic lighting scenarios would have no problem with this; gleam or highlights can exceed 1 and subblacks can be clipped out. This does not fit within our 8-bit color restriction that exists in Office applications. So sandwiching a worksheet function Min with 1 and a worksheet function Max with 0 will keep those numbers in check.
Problem 3.3: Transitional states
As prior stated, these are linear transitions between t(h') and f(h') over c/6, well kind of. Looking at solving this for pure red slowly fading to pure yellow, the duration of change from 0 to 1 for the green channel is 60°, so aG(h') = h'/60°. Red will now decrease from 1 to 0 over 60°, so slope is -h'/60°. Plugging this in won’t have the results we desire, however. It needs to be offset by 2 to account for the shift by 60°, making d(h') = 2 - h'/60° ⇐ val ∈ [0, 1] & sat ∈ [0, 1]. Those last restrictions can be removed if we consider how transforming this function actually works. The slope is descending 1 unit that is the maximum or val. Multiplying our entire equation, decreasing our requirements to d(h') = val*(2 - h'/60°) ⇐ sat ∈ [0, 1]. As with any algebraic function, you can add another function to it, so naturally, you would assume d(h') ⊢= val*(2 - h'/60°) + f(h'). This assumption is wrong, as multiplying the slope only by the maximum does not account for when there is low saturation, as slope will continue to be entirely based on full variance. Multiplying the unique part of the function by saturation leaves it entirely based on the difference, making d(h') = val*sat*(2 - h'/60°) + f(h') or d(h') = val*sat*(2 - h'/60°) + val(1 - sat).
Future proofing with assuming that a cycle isn’t 360° is genuinely easy. h'/60° is a representation of 6h'/360°. Replacing 360° with c returns d(h') = val*sat*(2 - 6h'/c) + f(h') or d(h') = val*sat*(2 - 6h'/c) + val(1 - sat).
The ascending transitional state is also fairly trivial given our knowledge of when the states occur and the duration of ascension. When at zero, aG(h') didn’t need any offsets. When at the first sixth, d(h') needed an offset of 2, and descent will need to always be increased by an additional one to align with [0,1] space. We should expect at 240° that d(h') = val*sat*(6h'/c - 4) + f(h') or d(h') = val*sat*(6h'/c - 4) + val(1 - sat).
Problem 3.4: Piecewise referencing
Writing a piecewise function in VBA would be silly given the scripting language already has logic systems in place. For those math lovers, here’s what the function looks like given Desmos/Office notation: r(h') = {0 ≤ h' < c/6: t(h'), c/6 ≤ h' < c/3: d(h'), c/3 ≤ h' < 2c/3: f(h'), 2c/3 ≤ h' < 5c/6: a(h'), 5c/6 ≤ h' < c: t(h')}. As stated before g(h') = r((h' + 2c/3) mod c) and b(h') = r((h' + c/3) mod c). After setting this up in Desmos, you can really see how hue, saturation, and value shifting actually work. When you compare this to the piecewise solution described before, it becomes more confusing why it didn’t work. Maybe it has to do with when variables are assigned, causing it to become a rounding error. Breaking it down this way does make it far easier to understand why things need to be a certain way.
Bonus: Pseudosinusoidal ≠ Sinusoidal
Throughout this piece, you may recognize the continuous use of referring to the transitional state as linear, but why can’t truncated cosine wave function? They have the same period, peaking every rotation of a circle. To our eyes, rc(h') = val*sat*max(0, min(1, ½(2cos(h') + 1)) + val(1 - sat), but it only approximates the correct value. The peaks and valleys are 100% accurate, but transitioning is not as pretty. The ratio between this approximation and reality depends on saturation, becoming more extreme as it increases. Its gentler transition may be worth the margin of error for some.
Legal
Zachary Yarnot and DualVission do not hold any rights to these owners’ contents.
Desmos is a product of Amplify and Desmos Studio.
Desmos Studio and Desmos are trademarks of Amplify.
Microsoft Excel and Visual Basic for Applications are products of Microsoft Corporation.
Microsoft, Microsoft Office, and Microsoft Excel are registered trademarks of Microsoft Corporation.
