Try this :

In a general module :
'------------------------------
Sub test()
MsgBox MyRgb("RGB(242, 242, 242)")
End Sub
'------------------------------
Function MyRgb(Funct As String) As Long
Dim S As Variant, R As Integer
Dim G As Integer, B As Integer
S = Split(Replace(Replace(Funct, "RGB", "", _
1, , vbTextCompare), " ", ""), ",")
R = Right(S(0), 3)
G = S(1)
B = Left(S(2), 3)
MyRgb = CLng(RGB(R, G, B))
End Function
'------------------------------




"Matthew Herbert" a écrit dans le message de
groupe de discussion : ...
Michdenis,

This works in the sense that "myString = RGB(242, 242, 242)" creates a
string variable of the Long value "15921906", i.e. RGB runs its function.
The String "15921906" does evaluate in "Evaluate(CLng(myString))". However,
in my situation, the user has entered a literal string "RGB(242, 242, 242)"
for which I need the Long value, i.e. myString = "RGB(242, 242, 242)" won't
evaluate to "15921906". You can see the function I created below as my work
around. Again, I wasn't sure if there was a way to get myString = "RGB(242,
242, 242)" to evaluate in order to return 15921906 without creating a custom
function.

Thanks for the suggestion, and I hope my comments above make sense.

Matt

The code beneath will illustrate what I'm trying to do without creating a
rather lengthy list of sub procedures and functions.

'---------------------------------------------------------------------
'cut code
varArrFilter = Array("Ticker|<|xlAnd||", "Market Cap (millions)|RGB(242,
242, 242)|xlFilterCellColor||")

varArrItem = Split(varFilter, "|")

'start of loop section, but I'm using a hard-coded index numbers for
clarity's sake

If varArrItem(2) = "xlFilterCellColor" Then
varArrItem(1) = GetRGBLongFromString(CStr(varArrItem(1)))
End If

'end of loop section
'---------------------------------------------------------------------

Private Function GetRGBLongFromString(strRGB As String) As Long

Dim lngPosStart As Long
Dim lngPosEnd As Long
Dim varArr As Variant
Dim strMid As String

lngPosStart = InStr(1, strRGB, "(", vbTextCompare)
If lngPosStart = 0 Then
GetRGBLongFromString = 0
Exit Function
End If

lngPosEnd = InStr(lngPosStart + 1, strRGB, ")", vbTextCompare)
If lngPosStart = 0 Then
GetRGBLongFromString = 0
Exit Function
End If

lngPosStart = lngPosStart + 1
lngPosEnd = lngPosEnd - 1

strMid = Mid(strRGB, lngPosStart, lngPosEnd - lngPosStart + 1)

If (Len(strMid) - Len(Replace(strMid, ",", ""))) < 2 Then
GetRGBLongFromString = 0
Exit Function
End If

varArr = Split(strMid, ",", , vbTextCompare)

GetRGBLongFromString = RGB(Trim(varArr(0)), Trim(varArr(1)), Trim(varArr(2)))

End Function

"michdenis" wrote:

Hi,

And this :

MyString = RGB(242, 242, 242)
strRgb = Evaluate(CLng(MyString))




"Matthew Herbert" a écrit dans le message de
groupe de discussion : ...
All,

Direct Question: Is it possible to get the Long result of RGB from a string
expression (reference Evalute) using built-in VBA functionality?

The code below (comments included) illustrates that the SIN function works
properly with Evaluate, but the RGB function does not. (See VBE help for
more details on the Evaluate method). I believe that RGB does not work
because, as far as I can tell, RGB is a VBA function and not a worksheet
function. (Also, as far as I can tell, SIN is both a VBA and worksheet
function).

So, is it possible to get the Long result of RGB from a string expression
using built-in VBA functionality? (If I need to build a custom function to
do this for me, then that is not a problem because I can write the syntax,
and because I'm sure there is code out there for converting the RGB integer
values to a Long. I simply want to see if there is built-in functionality
that would prevent me from having to create a custom function to achieve the
same result).

Thanks,

Matthew Herbert

Sub EvaluateStringQuestion()
Dim strSin As String
Dim dblSin As Double
Dim strRgb As String
Dim lngRGB As Long
Dim varRgb As Variant

'----------------------------------------------------------
'SIN Related (Shown to prove Evaluate does work)
strSin = "SIN(45)"

'The following is 0.850903524534118:
dblSin = Application.Evaluate(strSin)
'----------------------------------------------------------

'----------------------------------------------------------
'RGB Related (Doesn't seem to work)
strRgb = "RGB(242, 242, 242)"

'The following is 15921906 (which is what I expect
' from varRgb):
lngRGB = RGB(242, 242, 242)

'The following returns "Error 2029", hence the Variant
' data type:
varRgb = Application.Evaluate(strRgb)

'The following do not work:
' lngRGB = Application.Evaluate(strRgb)
' lngRGB = Application.Evaluate("RGB(242, 242, 242)")
'----------------------------------------------------------

End Sub