change language:

Colors can be defined in different ways. Office uses notation in RGB, Hex and Long in particular. Other color codes that are widely used in addition to Office are CMYK and HSL. There are tools on the internet that can convert the color code from one format to another format. This article discusses a number of VBA functions that can be used to convert colors from one format to another. At the end of this article there is also a download available with which colors can be conveniently converted to the different color coding systems. All the different codes can be seen at a glance. The Internet is then no longer necessary to convert colors.

RGB color code

With the RGB color code, colors are defined by means of 3 different numbers. These numbers can have a value from 0 to 255. The first number represents the color Red, the second number represents the color Green and the third number represents the color Blue.

RGB colors

For example, the color red is RGB(255, 0, 0) and the color yellow is RGB 255, 255, 0). In this way 256 3 (= 16,777,216)) different colors can be defined.

Hex color code

Hex color codes are based on the RGB system, only the color is not defined with 3 different numbers, but with 1 hexadecimal number. A hexadecimal number is based on a 16-digit number system. For this the numbers 0 to 9 and the letters A to F are used. Instead of a number from 0 to 255, each color is now defined with 2 characters. The number 0 is represented by 00 in this format and the number 255 by FF. By combining the 3 character pairs of the colors Red, Yellow and Blue, you get 1 hexadecimal number of 6 characters.

For example, the color red in hexadecimal format is #FF0000 and the color yellow is #FFFF00. In some cases, a shortened format can also be used for colors on websites. If the 3 character pairs each consist of identical characters, 1 character per color is sufficient. So # FF00CC can be written as # F0C and # 222222 as # 222.

Long color code

The Long color code is yet another notation for the RGB color code. This can be used in VBA, among other things, to define a color. The RGB color code is displayed in the form of 1 number. This number is obtained by adding the number for Red with the number for Green x 256 and the number for Blue x 256 x 256. This creates a unique number for every possible color.

For example, YellowGreen is RGB(154, 205, 50). In Hex notation this is #9ACD32. The Long for this color can then be calculated as follows: 154 + 205 x 256 + 50 x 256 x 256 = 3,329,434.

VBA conversion functions for RGB - Hex - Long

This section contains a few VBA conversion functions that can be used for RGB, Hex and Long. The Color Conversion file that can be downloaded at the end of this article contains additional conversion functions.

Public Function ConvertRGBToHEX(iRed As Integer, iGreen As Integer, iBlue As Integer) As String
    ConvertRGBToHEX = "#" & Right("0" & Hex(iRed), 2) & Right("0" & Hex(iGreen), 2) & Right("0" & Hex(iBlue), 2)
End Function

Public Function ConvertHexToRGB(sHexColor As String, sRGB As String) As Integer
    sHexColor = Replace(sHexColor, "#", "")
    Select Case UCase(sRGB)
        Case "R"
            ConvertHexToRGB = CInt("&h" & Left(sHexColor, 2))
        Case "G"
            ConvertHexToRGB = CInt("&h" & Mid(sHexColor, 3, 2))
        Case "B"
            ConvertHexToRGB = CInt("&h" & Right(sHexColor, 2))
    End Select
End Function

Public Function ConvertRGBToLong(iRed As Integer, iGreen As Integer, iBlue As Integer) As Long
    ConvertRGBToLong = RGB(iRed, iGreen, iBlue)
End Function

Public Function ConvertLongToRGB(lColor As Long, sRGB As String) As Integer
    Select Case UCase(sRGB)
        Case "R"
            ConvertLongToRGB = lColor Mod 256
        Case "G"
            ConvertLongToRGB = lColor \ 2 ^ 8 Mod 256
        Case "B"
            ConvertLongToRGB = lColor \ 2 ^ 16 Mod 256
    End Select
End Function

Public Function ConvertLongToHex(lColor As Long) As String
    Dim sRed As String, sGreen As String, sBlue As String
    sRed = Right("00" & Hex(lColor Mod 256), 2)
    sGreen = Right("00" & Hex(lColor \ 2 ^ 8 Mod 256), 2)
    sBlue = Right("00" & Hex(lColor \ 2 ^ 16 Mod 256), 2)
    ConvertLongToHex = "#" & sRed & sGreen & sBlue
End Function

Public Function ConvertHexToLong(sHexColor As String) As Long
    Dim sRed As String, sGreen As String, sBlue As String
    sHexColor = Replace(sHexColor, "#", "")
    sRed = Left(sHexColor, 2)
    sGreen = Mid(sHexColor, 3, 2)
    sBlue = Right(sHexColor, 2)
    ConvertHexToLong = CLng("&h" & sBlue) * 2 ^ 16 + CLng("&h" & sGreen) * 2 ^ 8 + CLng("&h" & sRed)
End Function

CMYK color code

With the CYMK color code, the colors are determined by means of 4 numbers.This concerns the 4 colors Cyan, Yellow, Magenta and Black (Key). These numbers can have a value between 0% and 100%. This color code is mainly used in the graphics industry.

CMYK color

The CMYK color code cannot be used in Office packages.

HSL color code

In the HSL color code, colors are defined in three numbers. The first number is the Hue and this must be specified in degrees (between 0 and 360). The next 2 numbers are respectively Saturation and Lightness. These last 2 numbers must be entered in a percentage between 0% and 100%.

In Excel HSL can be used when, for example, a background color is set. To do this, select a cell and choose Format Cells - & gt; Fill- & gt; More colors.... If you then click on the Custom tab, HSL can be selected for color model. By default, the color model is RGB. Excel does use different numbers compared to HSL. Instead of degrees and percentages, all 3 values ​​must now be entered in numbers between 0 and 255.

Color conversion tool

In order to make converting colors easy, an Excel file has been created where the values ​​in these color codes can be determined very easily.

color conversion

Each field on the form can be changed manually, after which the consequences of this change are automatically reflected in the other fields on the form.

The file also contains various conversion functions where all color codes can be converted to a Long and vice versa. With the help of these conversion functions all color conversions can be calculated.

In the latest version of the tool, the color codes used on VBA userforms have also been added. This also allows so-called system colors (that are colors that start with &H80) to be converted to the other color codes. The latter only works in Windows.

Download the Color Conversion tool:
zip-12Color conversion 1.1
 

Questions / suggestions

Hopefully, this article helped you convert colors with VBA. If you have any questions about this topic or suggestions for improvement, please post a comment below.

Comments  
# Jacco Bos 2022-09-25 21:07
Hi Manfred van den Noord,

(I am new to VBA)
I'm trying to incorporate your amazing tool into a workbook.
I have exported and imported modules and class modules. However, in the section Microsoft Excel Objects, there are two items:shColorNames(ColorNames) and shWorksheetsVBA(WorksheetsVBA) . When I export these and try to import them, they get imported as cls file and place in the Class Modules and the toll does not run.
Could you please help on this.

Thanks
Reply
# Manfred van den Noort 2022-09-26 16:56
Hi Jacco,
Thanks for your comment.
The 2 objects you refer to, are worksheets.
The easiest way to copy these worksheets is to unhide them first, then open both workbooks and right click on a worksheettab. Then choose the option move or copy. In the next form, choose the destination workbook and check Make copy.
Reply
# Jacco Bos 2022-09-26 17:12
Hi Manfred,
Thans for your quick response.
I have found it and all works fine.
Absolutely a great tool!
I didn't know the very hidden option.

Regards.
Reply
# Bruno Braga 2022-05-18 07:45
Hi, awesome tool! Can you adapt to use in CorelDraw?
Reply
# Manfred van den Noort 2022-05-18 17:11
I have never used CoralDraw.
Can you tell me where I can find more information about the colors that ColorDraw uses.
Then maybe I can add it to a future update of the tool.
Reply
# Bruno Braga 2022-05-19 00:19
It uses vba colors, which you already have there in your tool. I just wanted to be able to open it as a macro so like in Excel.
Reply
# Manfred van den Noort 2022-05-21 14:54
Ah, I thought that the reason of your request for adaption was that CoralDraw uses different color numbers.
Unfortunately I can't help you with open it from CoralDraw because I don't have CoralDraw and I don't have experience with it.
Reply
# Bruno Braga 2022-05-28 02:00
I understand, but could you do it as an executable? That when I click it open without EXCEL?
Reply
# Manfred van den Noort 2022-06-02 23:01
With VBA it is normally not possible to create exe files.
Converting the tool into an exe file is beyond the scope of this website.
Reply
# Jorge Cabral 2021-08-03 14:09
Hi Manfred van den Noort
I discovered your "Color Conversion tool" and found it fantastic, covering almost all conversions between color systems.
As an improvement, I ask if it would also be possible to convert to the color format that the USERFORM's use, that is, in the "&HxxFFFFFF&" format, where xx can be 00 or 80?
That is, using the same logic as your program, when I introduce, eg) a value in RGB, the program will also convert me to the "format" of USERFORM, if I introduced the value in the USRFORM format it would return the value to me in RGB and other formats.
Of course, the program would have to check if what was introduced in the USERFORM format respects the pattern of the userform color format, that is, "&HxxFFFFFF&".
Thank you very much
Jorge Cabral
Reply
# Manfred van den Noort 2021-08-03 19:32
Hi Jorge,
So glad you like the color conversion tool.
Thank you for your suggestion for improvement of the tool.
For UserForm colors you can use the long format. When you enter the long in a UserForm, this value is automatically converted to a UserForm format.
But maybe I'll add this format as a separate value in a future update of the tool.
Reply
# Jorge Cabral 2021-08-03 19:44
Hi Manfred van den Noort

Thank you very much for your quick response.

I know I can put it in Long format, and it works in userform, but the opposite I can't do now, ie when I want to know what is the RGB of a certain color of the Userform I don't know how to convert to RGB? That's why I thought it would be interesting to have this option too.
I'll be looking forward to your update for the Color Conversion Tool.
Thank you
Jorge
Reply
# Manfred van den Noort 2021-12-28 12:26
I have updated the tool and added the VBA UserForm colorcode.
Reply
# Jorge Cabral 2021-12-28 21:25
Thank you very much.
I was waiting for this update, it works spectacularly
Reply
arrow_up