Convert RGB Color - in an Array

[dj44]

Hi,

I was wondering how I can I use RGB colors in an array.

I found this that explained I need to convert it to a long

it was on the stack overflow site but I - cant post links yet

I am trying to find some Account IDS - and color it, below is brief script I am putting together



Sub ColorIDS()
Dim oRng As Word.Range
Dim i As Long
Dim ArrayIDS(4) As String
ArrayIDS(1) = "2231"
' ArrayIDS(2) = "2232"
' ArrayIDS(3) = "2345"
'ArrayIDS(4) = "2549"
Dim ArrayFontColor(1) As Long
ArrayFontColor(1) = "RGB(0, 156, 250)"
'ArrayFontColor(1) = " 16423936 " ' this is the long version
ArrayFontColor(i) = Left("i", 6) + Right("i", 1) ' Trying to extract the RGB
ArrayFontColor(i) = Split(ArrayFontColor, ",")(i)
'CLng(Val(str))
' Do the work now
For i = 0 To UBound(ArrayIDS)
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = ArrayIDS(i)
.MatchWholeWord = True
.Font.Color = (i)
.Replacement.Text = ArrayIDS(i) & " TWH-28374"
.Execute Replace:=wdReplaceAll
End With
Next
End Sub


I'm not sure how to go about converting the RGB to long. I would prefer not to convert it to long - If I can use the RGB as it is?

or if there is a better way to do this - I would be happy to know from the talented experts

Thanks alot for your time :grinhalo:

dj

[SamT]

arFontColor(0) = RGB(256, 0, 0)
arFontColor(1) = RGB(0, 256, 0)
arFontColor(2) = RGB(0, 0, 256)
'Alternate
arFontColor = Array(RGB(256, 0, 0), RGB(0, 256, 0), RGB(0, 0, 256))
For i = 0 to 2
MsgBox arFontColor(i)
Next

The Color Property of a Font Object is a number. RGB is a VBA Function that sums the powers of three numbers together to create a number that Word can use to set Font colors.

RGB(n1, n2, n3) = n1^1 + n2^2 + n3^3

Long is a Type that describes how big a number can be. The Color Property requires a big number, bigger than the Integer Type. So big that it is a Long Type. Integer is a really small number, it can only be +- 32,000.

[gmaxey]

I'm not sure exactly what you are tying to do but this might help:


Sub ColorDemos()
Dim oRng As Word.Range
Dim lngIndex As Long, lngColor As Long, R As Long, G As Long, B As Long
Dim arrRGB(3, 2) As Long

arrRGB(0, 0) = 0
arrRGB(0, 1) = 156
arrRGB(0, 2) = 250
arrRGB(1, 0) = 156
arrRGB(1, 1) = 250
arrRGB(1, 2) = 0
arrRGB(2, 0) = 25
arrRGB(2, 1) = 125
arrRGB(2, 2) = 250
arrRGB(3, 0) = 250
arrRGB(3, 1) = 125
arrRGB(3, 2) = 25

ActiveDocument.Range.Text = "TEST"
For lngIndex = 0 To UBound(arrRGB)
ActiveDocument.Characters(lngIndex + 1).Font.Color = RGB(arrRGB(lngIndex, 0), arrRGB(lngIndex, 1), arrRGB(lngIndex, 2))
'RGB to Long
lngColor = RGB(arrRGB(lngIndex, 0), arrRGB(lngIndex, 1), arrRGB(lngIndex, 2))
MsgBox lngColor
'Long to RGB
B = lngColor \ 65536
G = (lngColor - B * 65536) \ 256
R = lngColor - B * 65536 - G * 256
MsgBox "RGB(" & R & ", " & G & ", " & B & ")"
Next
lbl_Exit:
Exit Sub
End Sub

[gmaxey]

SamT,

I hope you won't mind. I just think it might help by declaring variables in your excellent example:


Sub SamTColorCode()
Dim arrLongColors(2) As Long
Dim lngIndex As Long
arrLongColors(0) = RGB(256, 0, 0)
arrLongColors(1) = RGB(0, 256, 0)
arrLongColors(2) = RGB(0, 0, 256)
For lngIndex = 0 To 2
MsgBox arrLongColors(lngIndex)
Next
'Alternate
Dim varLongColors
varLongColors = Array(RGB(256, 0, 0), RGB(0, 256, 0), RGB(0, 0, 256))
For lngIndex = 0 To 2
MsgBox varLongColors(lngIndex)
Next
lbl_Exit:
Exit Sub
End Sub

[dj44]

Hi SamT & Greg,

Gentlemen you overwhelm me with your kindness.:grinhalo:

Greg's Function - Nicely applied the color to each character .

SamT - I have added the array colors below.

I got stuck - as in my rush I picked and mixed the code - yikkes - so start again from below.



Sub ColorIDS()
Dim oRng As Word.Range
Dim i As Long
Dim ArrayIDS(3) As String
ArrayIDS(0) = "2231"
ArrayIDS(1) = "2232"
ArrayIDS(2) = "2345"
ArrayIDS(3) = "2549"
Dim ArrayFontColor(3) As Long
ArrayFontColor(0) = RGB(0, 156, 250)
ArrayFontColor(1) = RGB(256, 0, 0)
ArrayFontColor(2) = RGB(0, 256, 0)
ArrayFontColor(3) = RGB(0, 0, 256)
For i = 0 To UBound(ArrayIDS)
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = ArrayIDS(i) ' Find the Account ID's
.MatchWholeWord = True
.Font.Color = ArrayFontColor(i) ' Apply the Font Color
' The function here - could not work out how to apply it
.Replacement.Text = ArrayIDS(i) & " TWH-28374" 'Add Some Additional Text
.Execute Replace:=wdReplaceAll
End With
Next
End Sub


The above code it ran - but nothing applied - as I know I have not applied the conversion function correctly.

This Dim lngIndex As Long - I came across it another time - but never knew how to apply it.

If the above code makes sense - please do let me know

thank you :grinhalo:

DJ

[gmaxey]

I prefer to use descriptive names in my variables:


Sub ColorIDS()
Dim oRng As Word.Range
Dim lngIndex As Long
Dim arrAcctIDs() As String
Dim varLongColors '(3) As Long
arrAcctIDs = Split("2231,2232,2345,2549", ",")
varLongColors = Array(RGB(0, 156, 250), RGB(256, 0, 0), _
RGB(0, 256, 0), RGB(0, 0, 256))
For lngIndex = 0 To UBound(arrAcctIDs)
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = arrAcctIDs(lngIndex)
'.Format = True
.MatchWholeWord = True
.Replacement.Font.Color = varLongColors(lngIndex)
.Replacement.Text = arrAcctIDs(lngIndex) & " TWH-28374"
.Execute Replace:=wdReplaceAll
End With
Next
lbl_Exit:
Exit Sub
End Sub

[dj44]

Hi Greg,

works like a charm - thank you :grinhalo:

I will try to give you a quick explanation - it may seem counter intuitive what i was trying to initially do -

"vanity of vanities; all is vanity " - for the array


Right here goes - I was trying to copy the RGB Array colors from an excel spreadsheet - it is much easier for me to just copy and paste a column of ready made RGB Values - that was the reason -I had it set up this way - for aesthetic and usability reasons.

Also to prevent others from inputting the wrong values - in a long array string - when it goes vertically across the VBA module.
Never can find what I am looking for.

Below is a basic example of joining the 2 arrays - in vain - I couldn't get the RGB array set out vertically to work, hence coming to post here.



Sub ArrayJoin()
Dim oRng As Word.Range
Dim i As Long
Dim ArrayNumbers(4) As String
ArrayNumbers(1) = "22345"
ArrayNumbers(2) = "23234"
ArrayNumbers(3) = "24234"
ArrayNumbers(4) = "25445"
Dim ArrayWords(4) As String
ArrayWords(1) = "John"
ArrayWords(2) = "Helen"
ArrayWords(3) = "Sarah"
ArrayWords(4) = "Tom"
For i = 0 To UBound(ArrayNumbers)
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = ArrayNumbers(i)
.MatchWholeWord = True
.Replacement.Text = "Account Details: " & ArrayWords(i) & ArrayNumbers(i)
.Execute Replace:=wdReplaceAll
End With
Next
End Sub


It's such a shame I can't get the RGB colors in the array vertically to behave the same, when doing the replacement.

It's a lot more complicated than I anticipated - with those complex functions.

I can always transpose the column horizontally in excel - to get the long array string

thanks so much for helping Greg - Top Champion :grinhalo:

DJ

[gmaxey]

The only thing that I see wrong with your vain attempt is that arrays are indexed from 0 not 1:


Sub ArrayJoin()
Dim oRng As Word.Range
Dim i As Long
Dim ArrayNumbers(3) As String
Dim ArrayWords(3) As String

ArrayNumbers(0) = "22345"
ArrayNumbers(1) = "23234"
ArrayNumbers(2) = "24234"
ArrayNumbers(3) = "25445"
ArrayWords(0) = "John"
ArrayWords(1) = "Helen"
ArrayWords(2) = "Sarah"
ArrayWords(3) = "Tom"
For i = 0 To UBound(ArrayNumbers)
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = ArrayNumbers(i)
.MatchWholeWord = True
.Replacement.Text = "Account Details: " & ArrayWords(i) & ArrayNumbers(i)
.Execute Replace:=wdReplaceAll
End With
Next
End Sub

[dj44]

oh yes, how did that happen - it wasn't me -

Well spotted :grinhalo:

Cheers again!

[SamT]

Greg,

Does Word have the ColorIndex Property like Excel?

I always Declare all Variables in my own Code. Using VBA Options "Require Variable Declaration" forces it.

Using i, j, & k for simple indices and counters is a programming industry wide acceptable practice in all languages.

@ DJ44,

I made an error in the RGB explanation in my other post. it should read
RGB(N1, N2, N3) = N1 + (N2 * 256) + (N3 * 256*256)

Since N can be 255 the largest binary number for colors is
256 + 2562 + 256^3, subtracting 1 gives a 8 byte number in binary
Max N1 = xxxxxxxxxxxxxxxxx.11 111 111
Max N2 = xxxxxxxx1 111 111 .100 000 000
Max N3 = 111 111 110 000 000 000 000 000
Max RGB = 111 111 111 111 111 111 111 111

Note: that is for illustrative purposes. I did not subtract 1 from N2 and N3 to use the actual binary numbers

[gmaxey]

SamT,

Yes it does: Selection.Font.ColorIndex = wdBrightGreen

I know i,j,k etc. is used extensively. I just don't like it myself.

[SamT]

Thanks, I ws just curious and toooo lazy to look it up meselfies :o: