Application.Evaluate (works with SIN but not RGB)

M

Matthew Herbert

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
 
M

michdenis

Hi,

And this :

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




"Matthew Herbert" <[email protected]> a écrit dans le message de
groupe de discussion : (e-mail address removed)...
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
 
M

Matthew Herbert

Hi,

And this :

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

"Matthew Herbert" <[email protected]> a écrit dans le message de
groupe de discussion : (e-mail address removed)....
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
Click to expand...

The MS newsgroup doesn't appear to be posting replies, so if this
Google Groups post comes through as a redundant post, then you'll know
why. I'm sure that either my original MS newsgroup post or this
Google Groups post will make it through.

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
 
M

Matthew Herbert

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
 
M

michdenis

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" <[email protected]> a écrit dans le message de
groupe de discussion : (e-mail address removed)...
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count shaded cells with a gradient fill 3
Excel VBA 1
Run select query from VBA code where criteria comes from a multi select ListBox 2
Alternatives to Application.evaluate(Long Formula) 2
Setting cell colors using RGB( ) function 1
User defined data type (losing its data) 2
Error 2015 with Application.Evaluate 3
Moving The Cursor By Code 4

Top