CODEWITHSUNDEEP
vbaexcelrgb
Ciri2
Ciri2

Reputation: 21

Converting String into Long for RGB() values

I am writing a macro that recognizes the RGB value of a cell and then passes it as an argument to conditional formatting. The only issue is that using below:

RGBcolor1 = "RGB(" & CInt("&H" & Right(HEXcolor1, 2)) & _
", " & CInt("&H" & Mid(HEXcolor1, 3, 2)) & _
", " & CInt("&H" & Left(HEXcolor1, 2)) & ")"

where: 

HEXcolor1 = Right("000000" & Hex(Sheet1.[LowColour].Interior.Color), 6)

The RGB value is a string, whereas in order to pass it as .Color, I need it to be a Long (Color = rgb(255, 0, 0)).

I am aware solutions exist where using Debug window is recommended to retrieve ?rgb(255,0,0), however, I would like to automate the process. I tried Clng() as well as .Evaluate() but they did not work.

Any help greatly appreciated!

Upvotes: 2

Views: 4082

Answers (2)

Bond
Bond

Reputation: 16311

You'll have to parse the string. You could use a regex or just make some simple replacements to isolate just the digits. For example:

strColor = "RGB(123, 0, 234)"
strColor = Replace(strColor, "RGB", "")
strColor = Replace(strColor,   "(", "")
strColor = Replace(strColor,   ")", "")
strColor = Replace(strColor,   " ", "")

Dim a As Variant, c As Long
a = Split(strColor, ",")
c = a(0) * &H10000 + a(1) * &H100 + a(2)

Range("A1").Interior.Color = c

Or, with a regex (you'll have to add a reference to the Microsoft VBScript Regular Expressions 5.5 library):

With New RegExp
    .Global = True
    .Pattern = "[^\d,]"    ' Remove anything that's not a digit or comma
    Dim a As Variant, c As Long
    a = Split(.Replace(strColor, ""), ",")
    c = a(0) * &H10000 + a(1) * &H100 + a(2)
End If

Range("A1").Interior.Color = c

Edit:

Here's a quick but hacky way, using Eval() from the Microsoft Script Control:

With CreateObject("MSScriptControl.ScriptControl")
    .Language = "VBScript"
    Range("A1").Interior.Color = .Eval(strColor)
End With

Upvotes: 1

MatthewD
MatthewD

Reputation: 6761

You can convert it by using the val() function

Dim l as long
dim str as string

str = "111111"
l = val(str)

or 

CLng(Val(str))

Upvotes: 0

Related Questions