ACCESS FEED: RE: [MS_AccessPros] Re: VBA Color

Jumat, 29 November 2013

RE: [MS_AccessPros] Re: VBA Color

Graham,

Thanks for the insightful explanation of why vba color code is “different” than access properties color code.

I think I’ll use the RGB method and forgo the math (easier to document)

Thanks,

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Graham Mandeno
Sent: Thursday, November 28, 2013 2:11 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: VBA Color

Hi Bryan

Just to expand a little, colour values are stored as 32-bit (4-byte) long integers. If the most significant byte has the value zero, then the other three bytes contain the intensities of red, green and blue, respectively. Red is stored in the least significant byte, then green, then blue. Each of these intensities is represented by a value from 0 (00 in hexadecimal or “hex”) up to 255 (FF in hex).

[If the most significant byte is not zero, then the whole value is interpreted differently, as a system colour.]

It is customary when representing numbers to write the most significant digits on the left and the least significant on the right (as in the decimal number 1234 – the “1” [thousands] is more significant than the “4” [ones])

As an example, consider a colour where the red intensity is 161 (A1 in hex), the green is 178 (&hB2), and the blue is 195 (&hC3). The colour value can be calculated as follows:

161 + (178 * 16^2) + (195 * 16^4) = 12825249
(you can verify this by typing ?161 + (178 * 16^2) + (195 * 16^4) into the VBA immediate window)

We can get this result in a number of other ways – for example:

? &hA1 + (&hB2 * 16^2) + (&hC3 * 16^4)
? RGB( 161, 178, 195 )
? RGB( &hA1, &hB2, &hC3 )

[Note that the RGB function, as John pointed out, provides an easy way to combine the three values, and that the values can be represented as either decimal or hex]

We can look at the hex representation of our whole colour value using the Hex() function:

? Hex(12825249) = C3B2A1
and, going the other way
? &hC3B2A1 = 12825249

Note that the three individual intensity values are intact, but with red on the right.

The properties box used to display colour values in decimal, but this is pretty meaningless, and since Access 2007 they have been displayed using HTML format:

#RRGGBB (where RR, GG and BB are the hex representations of the intensity values for red, green and blue, respectively)

Note that red is now on the left, so the HTML representation for 12825249 (or &hC3B2A1) is #A1B2C3. Glenn is correct that the positions are reversed, but it is in the VBA value they appear as B-G-R, not in the HTML format

If you wish to work with colours in HTML format, here are some useful functions for converting values:

================ start code =================
Public Function ColourToHTML(lColour As Long) As String
Dim bRed As Byte, bGreen As Byte, bBlue As Byte
If (lColour And &HFF000000) <> 0 Then
ColourToHTML = "<System Colour!>"
Else
bRed = lColour And &HFF
bGreen = (lColour \ &H100) And &HFF
bBlue = lColour \ &H10000
ColourToHTML = RGBtoHTML(bRed, bGreen, bBlue)
End If
End Function

Private Function Hex2(bValue As Byte) As String
Hex2 = Hex(bValue)
If Len(Hex2) = 1 Then Hex2 = "0" & Hex2
End Function

Public Function RGBtoHTML(bRed As Byte, bGreen As Byte, bBlue As Byte) As String
RGBtoHTML = "#" & Hex2(bRed) & Hex2(bGreen) & Hex2(bBlue)
End Function

Public Function HTMLtoRGB(sHTML As String) As Long
On Error GoTo ProcErr
If Len(sHTML) <> 7 Or Left(sHTML, 1) <> "#" Then Err.Raise 5
HTMLtoRGB = RGB("&h" & Mid(sHTML, 2, 2), "&h" & Mid(sHTML, 4, 2), "&h" & Mid(sHTML, 6, 2))
Exit Function
ProcErr:
HTMLtoRGB = -1
End Function
================ end code =======================

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Glenn Lloyd
Sent: Friday, 29 November 2013 03:56
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: VBA Color

If I recall correctly, the HEX couplets are not in the expected order R-G-B. I found I could get the colour I wanted by switching the order but I can’t recall now which order worked. It may have been B-G-R.

Glenn