Converting color codes in VBA

Ulodesk · #1 10-29-2013, 01:53 PM

I have been unable to find an explanation of Word's (or VBA's, perhaps)7- or 8-digit color codes. Some references suggest a hexadecimal system, but what I see doesn't appear to match this.

For instance, an RGB 225/225/225 gray in hex is E1E1E1, but in VBA it appears as 'color pattern" 5131854. This is a macro used for filling selected table cells with color.

I would like to be able to change a color in a macro quickly without having to re-record the macro, but I would need an RGB converter, or at least a hex converter, to whatever this system is.

Can anyone point me in the right direction, please? Thanks.

macropod · #2 10-29-2013, 10:06 PM

Hi Ulodesk,

Is there a reason you can't work with the RGB values directly? For example:

Code:

Sub Demo()
Selection.Cells(1).Shading.BackgroundPatternColor = RGB(225, 225, 225)
End Sub

Ulodesk · #3 10-30-2013, 06:15 AM

No, no reason, now that I see that this is possible. I know so little of coding (can you tell?), I simply assumed that whatever is there in the recorded macro must be what Word is looking for. Thank you.

However, I'm still curious as to the origin of the 7- or 8-digit number.

gmaxey · #4 10-30-2013, 07:20 AM

That number doesn't have to be 7 or 8 digits. It can be three and it represents a long data type.

Experiment with this in a new document:

Code:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
ActiveDocument.Range.Text = "ABC...XYZ"
'Assign color using constants.
ActiveDocument.Range.Characters(1).Font.Color = wdColorRed
ActiveDocument.Range.Characters(2).Font.Color = wdColorGreen
ActiveDocument.Range.Characters(3).Font.Color = wdColorBlue
'Color property returns a long data type
Debug.Print ActiveDocument.Range.Characters(1).Font.Color
Debug.Print ActiveDocument.Range.Characters(2).Font.Color
Debug.Print ActiveDocument.Range.Characters(3).Font.Color
'Assign color using long values.
ActiveDocument.Range.Characters(4).Font.Color = 255
ActiveDocument.Range.Characters(5).Font.Color = 32768
ActiveDocument.Range.Characters(6).Font.Color = 16711680
'What are the RGB values?
Debug.Print fcnLongToRGB(255) 'Red
'Green has a long value 32768
Debug.Print fcnLongToRGB(32768) 'Green
'Blue as a long value 16711680
Debug.Print fcnLongToRGB(16711680) 'Blue
'Assign color using RGB values
ActiveDocument.Range.Characters(7).Font.Color = RGB(255, 0, 0)
ActiveDocument.Range.Characters(8).Font.Color = RGB(0, 128, 0)
ActiveDocument.Range.Characters(9).Font.Color = RGB(0, 0, 255)
End Sub

Code:

Function fcnLongToRGB(ByRef lngColor As Long) As String
Dim lngRed As Long, lngGreen As Long, lngBlue As Long
lngRed = lngColor Mod 256
lngGreen = (lngColor \ 256) Mod 256
lngBlue = (lngColor \ 256 \ 256) Mod 256
fcnLongToRGB = "RGB(" & lngRed & "," & lngGreen & "," & lngBlue & ")"
End Function

Ulodesk · #5 10-30-2013, 07:48 AM

Thank you, Greg. I have visited your site a number of times and have gained from it. My own weaknesses, however, have kept me from gaining enough to tell from the macro you have just kindly supplied me what I should do with it to experiment. I tried simply running it in a document of several paragraphs, and then tried first selecting a number of words and running it. Both returned "compile" errors, undoubtedly Word's polite way of suggesting that I am clearly incompetent, a suggestion with which, respecting The Dominions of VBA, I readily agree without reservation.
Learning coding, after several failed attempts on my own over the years, is not at the top of my list; my work does not require it, and I am obliged to attend to other priorities. Again, however, I do appreciate your taking the time and concern to assist me with this issue, for which Paul's solution will meet my limited needs.

Best,
Philip

macropod · #6 10-30-2013, 10:55 PM

Quote:

Originally Posted by Ulodesk

I simply assumed that whatever is there in the recorded macro must be what Word is looking for.

The macro recorder is as dumb as a box of rocks. There is far more to VBA than the macro recorder will ever acknowledge. Plus, for the most part, the code the macro recorder produces is inflexible and inefficient.

Ulodesk · #7 10-31-2013, 06:04 AM

I discovered long ago that the recorder is quite limited, since it needed help with the one-line macro for pasting text unformatted, and I have seen at least three versions of a working macro for this. But I do like your epithet, Paul.

Best,
Philip

Hoggle42 · #8 11-24-2014, 04:15 AM

As a companion to that function (very useful btw, thanks)

This one converts a VBA colour code to a six character hex code useable in CSS etc. Not sure if the blue and green are wrongly labelled, but the order is correct (it produces the right colour). The Hex function returns "0" rather than "00" so it needs padding.

Code:

Private Function fcnLongToHex(ByRef lngColor As Long) As String
    Dim hRed, hGreen, hBlue
    hRed = Hex(lngColor Mod 256)
    If Len(hRed) = 1 Then hRed = "0" & hRed
    hGreen = Hex((lngColor \ 256) Mod 256)
    If Len(hGreen) = 1 Then hGreen = "0" & hGreen
    hBlue = Hex((lngColor \ 256 \ 256) Mod 256)
    If Len(hBlue) = 1 Then hBlue = "0" & hBlue
    fcnLongToHex = hRed & hBlue & hGreen
End Function

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Codes Popping Up In My Text?	Faedrie	Word	1	11-08-2012 01:00 AM
Codes for Word Textbox Font Color?	tinfanide	Word VBA	7	10-23-2012 03:13 PM
Losing VBA codes in .dotm	tinfanide	Word VBA	15	04-12-2012 12:39 AM
Outline Codes	eliz.bell	Word	4	03-28-2012 07:27 PM
Why run-time is always different from stepping into the codes?	tinfanide	Excel Programming	1	03-04-2012 12:04 AM