In recent versions of Access we can enter HEX values in the property sheet to set the various color properties of controls. That said, this is not true in VBA. No clue as to why Microsoft does not allow it, nor do they provide any functions to convert HEX values into the format expected (Long).

In my past post on the subject:

Access - Let's Talk Colors

Just a quick post to discuss colors in Access. Colors can be an asset to designers, if used correctly, to greatly enhance your application(s), no doubt! But how can we define colors is the question I thought I’d quickly touch upon today. All too often I see in code people defining thing along the lines…

Continue reading →

I discussed why using Val() is not reliable with HEX values and offered an old utility of mine which provides a couple functions that you could chain together to convert HEX to OLE.

Today, helping someone out in a forum, I merged the 2 into a single function and thought it made sense to post it here as this is a common requirement and I figured it could help some of you.

So if you wish to use HEX values (HTML colors) in Access VBA when the properties are expecting OLE Long values, you can simply use the following function:

'---------------------------------------------------------------------------------------
' Procedure : HEXtoOLE
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Convert a HEX value to OLE Long value
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sHEX  - HEX Color Code value, # is optional/not required
'
' Usage:
' ~~~~~~
' HEXtoOLE("#6897bb") -> 12293992
' HEXtoOLE("f0460b") -> 739056
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2019-08-12              Initial Release, forum help
' 2         2022-09-12              Found edge cases that failed (c6c6c6), add CDec to
'                                   resolve them
'---------------------------------------------------------------------------------------
Public Function HEXtoOLE(ByVal sHEX As String) As Long
    Dim R As Byte
    Dim G As Byte
    Dim B As Byte
    
    On Error GoTo Error_Handler

    If Left(sHEX, 1) = "#" Then sHEX = Replace(sHEX, "#", "")
    
    R = CByte("&H" & Left(sHEX, 2))
    G = CByte("&H" & Mid(sHEX, 3, 2))
    B = CByte("&H" & Mid(sHEX, 5, 2))
    
    HEXtoOLE = CLng(CDec(R) + (CDec(G) * 256) + (CDec(B) * 65536))

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Source: HEXtoOLE" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Then you could use it to set a form’s BackColor by doing something along the lines of

Me.Section(acDetail).BackColor = HEXtoOLE("#6897bb")

Although created for Access it can be use in any VBA environment (Access, Excel, Word, PowerPoint, …) as it is not dependent on any Access specific references.