First Published 6 Oct 2026

This article contains functions to easily convert between various colour formats: HEX, RGB, OLE (long) and the Office version of HSL.
I use many of these functions in my Colour Converter and Colour Picker example apps.

Colour Converter	Colour Picker

Convert to HEX:

HEX colours are string values with 3 pairs of characters between 0 and F for the red/green/blue components usually preceded by a #.
For example, red is #FF0000, green is #00FF00, blue is #0000FF, black is #000000 and white is #FFFFFF.

'===============================================
'Functions to convert to HEX
'===============================================

Public Function OLEtoHEX(ByVal OLE As Long) As String
    Dim r As Byte, g As Byte, b As Byte
    
On Error GoTo ErrHandler
    
    ' Extract individual RGB components from the Windows Long
    ' Windows Color Long formats store values inversely as Blue-Green-Red (BGR), extracted via bitmasking and integer division.
    r = OLE And &HFF
    g = (OLE \ &H100) And &HFF
    b = (OLE \ &H10000) And &HFF
    
    ' Format as a clean CSS style hex string (#RRGGBB)
    ' Converts each byte to hex, prepends a leading zero to handle single digits, and drops the result down to a strict 2-character limit.
    OLEtoHEX = "#" & Right$("0" & Hex$(r), 2) & _
                           Right$("0" & Hex$(g), 2) & _
                           Right$("0" & Hex$(b), 2)
                           
ExitHandler:
    Exit Function
    
ErrHandler:
    MsgBox "Invalid OLE color format", vbCritical, "Conversion error"
    Resume ExitHandler
    
End Function

Public Function RGBtoHEX(r As Byte, g As Byte, b As Byte) As String

     Dim HR As String, HG As String, HB As String
     
    ' R,G,B components each in range 0-255
    ' convert each component to 2 char HEX string e.g. 15 => 0F; 255 => FF
    If r < 16 Then
        HR = 0 & Hex(r)
    Else
        HR = Hex(r)
    End If
    
    If g < 16 Then
        HG = 0 & Hex(g)
    Else
        HG = Hex(g)
    End If
    
    If b < 16 Then
        HB = 0 & Hex(b)
    Else
        HB = Hex(b)
    End If
    
    'concatenate string
    RGBtoHEX = "#" & HR & HG & HB
    
End Function

Example Usage:




Convert to OLE (long):

OLE colours are long integer values between 0 (black) and 16777215 (white).
For example, red is 255, green is 65280 and blue is 16711680.

'===============================================
'Functions to convert to OLE (long)
'===============================================

Public Function HEXToOLE(ByVal Hex As String) As Long

' Converts a HEX color string ("#RRGGBB" or "RRGGBB") to an OLE_COLOR Long value

' In Access, OLE_COLOR values are a Long integer in BGR order, not RGB.
' Conversion process:
' Parse the hex string into R, G, B components,
' Rearrange them into BGR order
' Return the result as a Long.

    Dim r As Long, g As Long, b As Long
    
On Error GoTo ErrHandler
    
    ' Remove leading "#" if present
    Hex = Replace(Hex, "#", "")
    
    ' Validate length (must be exactly 6 hex digits)
    If Len(Hex) <> 6 Or Not Hex Like "[0-9A-Fa-f]*" Then
        Err.Raise vbObjectError + 513, , "Invalid HEX color format."
    End If
    
    ' Extract RGB components from hex
    r = CLng("&H" & Mid$(Hex, 1, 2))
    g = CLng("&H" & Mid$(Hex, 3, 2))
    b = CLng("&H" & Mid$(Hex, 5, 2))
    
    ' Convert RGB to OLE_COLOR (BGR order)
    HEXToOLE = RGB(r, g, b)
    
ExitHandler:
    Exit Function
    
ErrHandler:
    MsgBox "Invalid HEX color format", vbCritical, "Conversion error"
    Resume ExitHandler
    
End Function

Public Function RGBtoOLE(r As Byte, g As Byte, b As Byte) As Long

   'Just use VBA's built-in RGB function to get OLE color
    RGBtoOLE = RGB(r, g, b)
End Function

Example Usage:




Convert to RGB:

RGB colours use integer values between 0 and 255 for the red, green and blue components.
For example, red is RGB(255,0,0), green is RGB(0,255,0), blue is RGB(0,0,255), black is (0,0,0) and white is (255,255,255).

'===============================================
'Functions to convert to RGB
'===============================================

Public Function HEXtoRGB(Hex As String) As String

    Dim r As Byte, g As Byte, b As Byte
    
    'remove leading '#' if present
    Hex = Replace(Hex, "#", "")

    r = CByte("&H" & Left(Hex, 2))
    g = CByte("&H" & Mid(Hex, 3, 2))
    b = CByte("&H" & Mid(Hex, 5, 2))

    'display as string e.g. 128, 128, 128
    HEXtoRGB = "RGB(" & r & "," & g & "," & b & ")"

End Function

Public Function OLEtoRGB(OLE As Long) As String
    'Converts long integer color to RGB
    ' Applies isolated bitmasking operators (\ and And) to map standard 24-bit color numbers into separate 8-bit RGB channels.
    Dim r As Long, g As Long, b As Long
    
    r = OLE And &HFF
    g = (OLE \ &H100) And &HFF
    b = (OLE \ &H10000) And &HFF
    
    OLEtoRGB = "RGB(" & r & "," & g & "," & b & ")"
    
End Function

Example Usage:




Convert to HSL (Office):

HSL colours use a completely different system based on hue, saturation and luminosity.
The modern HSL colour system uses a 0-360 degrees range for H, with both S & L being percentage values between 0 and 100.

However, for reasons of backwards compatibility, Office apps such as Access and Excel, still use a legacy system based on a 0 to 255 scale for H, S and L.

Colour dialog - RGB	Colour dialog - HSL

The Office HSL format also has a further quirk whereby the default hue for grey is 170 (not 0).
This makes conversion to Office HSL both complex and prone to rounding errors whereby one or more values may be 'out by 1'.

Examples of Office HSL: black is HSL(170, 0, 0), white is HSL(170, 0, 255), red is HSL(0,255,128), green is HSL(85,255,128) and blue is HSL(170,255,128).

'===============================================
'Functions to convert to HSL for Office apps
'===============================================

' Converts RGB (0–255 each) to HSL (0–255 each) using Office apps' internal scale/behavior (same in Excel/Access)
Public Function RGBtoHSLOffice(r As Long, g As Long, b As Long) As Variant
    Dim rNorm As Double, gNorm As Double, bNorm As Double
    Dim maxVal As Double, minVal As Double, delta As Double
    Dim H As Double, S As Double, L As Double

    ' Validate inputs
    If (r < 0 Or r > 255) Or (g < 0 Or g > 255) Or (b < 0 Or b > 255) Then
        RGBtoHSLOffice = Null
        Exit Function
    End If

    ' Normalize RGB to 0–1 range
    rNorm = r / 255#
    gNorm = g / 255#
    bNorm = b / 255#

    ' Find min and max
    maxVal = rNorm
    If gNorm > maxVal Then maxVal = gNorm
    If bNorm > maxVal Then maxVal = bNorm

    minVal = rNorm
    If gNorm < minVal Then minVal = gNorm
    If bNorm < minVal Then minVal = bNorm

    L = (maxVal + minVal) / 2
    delta = maxVal - minVal

    ' Access behavior: does NOT zero hue for greys
    If delta = 0 Then
        S = 0
        H = 2 / 3 ' Access's default hue for greys (˜170 on 0–255 scale)
    Else
        ' Saturation
        If L < 0.5 Then
            S = delta / (maxVal + minVal)
        Else
            S = delta / (2 - maxVal - minVal)
        End If

        ' Hue calculation
        Select Case maxVal
            Case rNorm
                H = (gNorm - bNorm) / delta
                If gNorm < bNorm Then H = H + 6
            Case gNorm
                H = (bNorm - rNorm) / delta + 2
            Case bNorm
                H = (rNorm - gNorm) / delta + 4
        End Select

        H = H / 6
    End If

    ' Convert to Access's 0–255 scale with rounding and clamping
    Dim hue255 As Long, sat255 As Long, lum255 As Long
    hue255 = CLng(H * 255# + 0.5) Mod 256
    sat255 = CLng(S * 255# + 0.5)
    lum255 = CLng(L * 255# + 0.5)

    If sat255 > 255 Then sat255 = 255
    If lum255 > 255 Then lum255 = 255
    
    RGBtoHSLOffice = "HSL(" & hue255 & "," & sat255 & "," & lum255 & ")"
    
End Function

'derived function
Public Function HEXtoHSLOffice(ByVal Hex As String) As String
    Dim RGB() As String, rgbTemp As String, r As Long, g As Long, b As Long
    
    'strip the RGB prefix and the () brackets if present:
    rgbTemp = Replace(HEXtoRGB(Hex), "RGB(", "")
    rgbTemp = Replace(rgbTemp, ")", "")
    
    'split into components
    RGB = Split(rgbTemp, ",")

    r = RGB(0)
    g = RGB(1)
    b = RGB(2)
    
    HEXtoHSLOffice = RGBtoHSLOffice(r, g, b)

End Function

'derived function
Public Function OLEtoHSLOffice(ByVal OLE As Long) As String
    Dim RGB() As String, r As Long, g As Long, b As Long
    
    r = OLE And &HFF
    g = (OLE \ &H100) And &HFF
    b = (OLE \ &H10000) And &HFF
    
    OLEtoHSLOffice = RGBtoHSLOffice(r, g, b)

End Function

Example Usage:


I have not deliberately provided code to convert back from Office HSL to any of the other formats.
This is because, despite many hours of effort and research, I have not been able to obtain reliable results that work in all cases.

If anyone reading this article is able to provide reliable functions to convert Office HSL to RGB, OLE or HEX, please email me using the contact form below.



Download

All the code used in this article is available in the module modConversionCode of the attached database.
Some additional procedures are also supplied in other modules including several unsuccessful functions to convert from HSL Office to other formats.

Click to download: Colour Conversion Code    ACCDB     approx 0.4 MB (zipped)

As is the case for all files downloaded from the internet, first unblock the downloaded file, unzip then save to a trusted location.
For more details, see my article: Unblock downloaded files by removing the Mark of the Web



Feedback

Please use the Email button in the contact form below to let me know whether you found this article useful or if you have any questions.

Please also consider making a donation towards the costs of maintaining this website. Thank you

Colin Riddington	Mendip Data Systems	Last Updated 6 Oct 2026

Return to Code Samples Page

Return to Top