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.
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.
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 -> Fill -> 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.
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.
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.
Would you allow me to publish on our website www.corelnaveia.com, with due initial credits for the project?
Thank you in advance and success there always!
Ps: Very useful tool for our audience too!
If you authorize the CorelDRAW version with closed code and free of charge, the interface will have links to your website and credits, in addition to showing your website on our Youtube channel in the project presentation!
If you can, reply to us by email,
as it is more convenient for these procedures.
(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 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.
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.
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.
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.
Converting the tool into an exe file is beyond the scope of this website.
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
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.
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.
I was waiting for this update, it works spectacularly