Excel Vba RGB to Hex,Long – Hex to Rgb,Long – Color Code Conversions

Convert VBA Color Codes to RGB or HEX

To convert the Excel vba color index list to RGB, HEX, Long/Decimal use these functions.

This covers almost all type of Color code conversions required in Vba code.

  1. To RGB:
    1. Convert Color Code to RGB
    2. Convert Hex to RGB
  2. To HEX: (Hex Color Converter)
    1. Convert RGB to HEX
    2. Convert Long to HEX
  3. To Long/Decimal:
    1. Convert RGB to Decimal
    2. Convert HEX to Decimal

Also Read: How To Change Excel Cell Background Color?

1.a Convert Excel VBA Color Codes in Long to RGB

You get the color code from Cell.Interior.Color. Excel wil return a decimal/long numeric value. Convert this to RGB as below.

Function VBA_Long_To_RGB(lColor As Long) As String
    Dim iRed, iGreen, iBlue
    
    'Convert Decimal Color Code to RGB
    iRed = (lColor Mod 256)
    iGreen = (lColor \ 256) Mod 256
    iBlue = (lColor \ 65536) Mod 256
    
    'Return RGB Code
    VBA_Long_To_RGB = "(" & iRed & ";" & iGreen & ";" & iBlue & ")"
End Function

1.b How To Convert Hex to RGB

Consider you have a hex code for a cell background color in Excel. #FFFFFF – This corresponds to white color. To convert this Excel VBA color codes to RGB, follow this method.

Function VBA_Hex_To_RGB(hColor As String) As String
    Dim iRed, iGreen, iBlue
    
    'Convert HEx to RGB
    hColor = VBA.Replace(hColor, "#", "")
    hColor = VBA.Right$("000000" & hColor, 6)
    iBlue = VBA.Val("&H" & VBA.Mid(hColor, 1, 2))
    iGreen = VBA.Val("&H" & VBA.Mid(hColor, 3, 2))
    iRed = VBA.Val("&H" & VBA.Mid(hColor, 5, 2))
    
    'Return RGB Code
    VBA_Hex_To_RGB = "(" & iRed & ";" & iGreen & ";" & iBlue & ")"
End Function

2.a How To Convert RGB code to Hex

Either this can be done by convert the RGB code to decimal using 1.a and then Long to Hex.

Function VBA_RGB_To_HEX(iRed As Integer, iGreen As Integer, iBlue As Integer) As String
    Dim sHex As String
    sHex = "#" & VBA.Right$("00" & VBA.Hex(iBlue), 2) & VBA.Right$("00" & VBA.Hex(iGreen), 2) & VBA.Right$("00" & VBA.Hex(iRed), 2)
    VBA_RGB_To_HEX = sHex
End Function

2.b How To Convert Decimal to Hex

To get Hex value from a decimal number, we can use the Excel worksheet function DEC2HEX or the below VBA code.

Function VBA_Long_To_Hex(lColor As Long) As String
    Dim hexCode As String
    
    'Excel Formula to Convert Decimal to Hex
    '=DEC2HEX(Decimal or Long Value)
    
    'Convert Long to Hex code and Return
    hexCode = VBA.Right$("000000" & VBA.Hex$(lColor), 6)
    VBA_Long_To_Hex = "#" & hexCode
    
End Function

3.a How To Convert RGB code to Decimal/Long

To get RGB code from a Excel color code, we have the inbuilt function VBA.RGB ( Red, Blue, Green).

Function VBA_RGB_To_Long(iRed As Integer, iGreen As Integer, iBlue As Integer) As Long
    VBA_RGB_To_Long = VBA.RGB(iRed, iGreen, iBlue)
End Function

3.b How To Convert Hex to Long

Convert Excel Color codes from Hex to Long.

Function VBA_Hex_To_Long(hColor As String) As Long
    
    'Convert Hex to Long
    hColor = VBA.Replace(hColor, "#", "")
    hColor = VBA.Right$("000000" & hColor, 6)
    
    'Return Long Excel VBA Color Code
    VBA_Hex_To_Long = Application.WorksheetFunction.Hex2Dec(hColor)
End Function

Enjoy these conversions from any to any. Remember that these color code conversion is specific to Excel VBA color codes. Oher programming languages may have different representation of the RGB & Hex values for the color palette.