1

I'm trying to set the interior color of some cells using HSL instead of RGB.

Judging by this documentation from Microsoft, it's very simple: https://learn.microsoft.com/en-us/office/client-developer/visio/hsl-function

The syntax is simply HSL(** hue **, ** saturation **, ** luminosity ** )

Why, then, is my VBA telling me the sub or function is not defined?

The same error occurs for both of these lines of code:

Range("A1").Interior.Color = HSL(160, 240, 120)

Range("A1").Interior.ColorIndex = HSL(160, 240, 120)
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
squidgeny
  • 41
  • 5
  • 2
    That's Visio, not Excel. – BigBen Oct 17 '19 at 12:42
  • [This](https://www.mrexcel.com/forum/excel-questions/861949-hsl-rgb.html) might possibly be useful. – BigBen Oct 17 '19 at 12:53
  • 1
    Use the [RGB function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rgb-function) instead. HSL does not exist in Excel. – Pᴇʜ Oct 17 '19 at 12:53

1 Answers1

2

Here's an alternative method for converting between RGB → HSL in VBA, using a Windows API. Since it's a "Classic Windows" function it has a couple idiosyncrasies.

RGB to HSL with ColorRGBToHLS

ColorRGBToHLS is an Windows API function which therefore uses an outdated color range that's been carried forward since the 1980's (originally used in a long-defunct program called MS Chart). You may be familiar with converting R/G/B values between percentages and values out of 255.

  • However in this case the conversion must be to/from values out of 240, not 255.
  • Just to further confuse us here in the future, they used notation of HLS instead of more-common HSL (and hex color strings are 0xBBGGRR instead of #RRGGBB).

tl;dr

Option Explicit

Public Declare PtrSafe Sub ColorRGBToHLS Lib "shlwapi.dll" (ByVal clrRGB As Long, _
    wHue As Integer, wLuminance As Integer, wSaturation As Integer)

Function rgb_to_hsl(r As Integer, g As Integer, b As Integer)
  Dim h As Integer, s As Integer, l As Integer
  ColorRGBToHLS RGB(r, g, b), h, l, s
  h = 360 * (h / 239)
  s = 100 * (s / 240)
  l = 100 * (l / 240)
  rgb_to_hsl = "hsl(" & h & "," & s & "%," & l & "%)"
End Function

...and a demo:

Sub test()
  Debug.Print rgb_to_hsl(255, 180, 63)  'returns "hsl(36,100%,62%)"
End Sub

By the way the API also has a ColorHLSToRGB function.

The 80's was a crazy (often confusing) time for computer nerds. Fun fact: Bill Gates married the first female programmer he hired.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105