VBA color conversion

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 ³ (= 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. Copy and Paste buttons have also been added, making the tool even more user-friendly.

There are further updates planned for this tool that will further improve the tool's user flexibility and capabilities, so check this page regularly to see if a new version is available.

In the latest version of the tool, VBA Color constants and a color palette have also been added to the form in the tool. In addition to the existing functionality of converting colors, the tool can also be used very well as a color picker.

Color conversion 2.2

Download

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.