When setting RGB values, 'Evaluate' gives different results when the same code is repeated...

[DavidWHouse]

Dear friends,

First post. Sorry for any mistakes in netiquette, etc.

I am trying to work on the programmatic creation of objects that have foreground and background colors. The value of the dot referenced (and other) properties is stored in a worksheet and retrieved. Setting RGB values of .forecolor et al when these are stored as strings such as "RGB(200, 150, 125)" gives very odd results, in the sense that when Evaluate is called once with such a string, it offers one [incorrect] result, and if called a second time with the same syntax it gives a second [correct] result, which thereafter does not vary.

Thus:

Sub tstEvalRGBString()
    Dim shtActive As Excel.Worksheet
    Dim shpTest As Shape
        '
        '
    Set shtActive = ActiveWorkbook.ActiveSheet
    Set shpTest = shtActive.Shapes.AddShape(Type:=msoShapeRectangle, Left:=1, Top:=1, Width:=100, Height:=100)
        '
    With shpTest.Fill.ForeColor
        Evaluate (".RGB = RGB(200, 150, 125)")
        Debug.Print .RGB & " from Eval"         ' prints "13998939 from Eval"   on my system
        .RGB = RGB(200, 150, 125)
        Debug.Print .RGB & " direct"            ' prints "8230600 direct"       on my system
        Evaluate (".RGB = RGB(200, 150, 125)")
        Debug.Print .RGB & " from Eval #2"      ' prints "8230600 from Eval #2" on my system
        Evaluate (".RGB = RGB(200, 150, 125)")
        Debug.Print .RGB & " from Eval #3"      ' prints "8230600 from Eval #2" on my system
    End With
End Sub

Any ideas about why this happens, and (if I've made some mistake in code), what to do to correct it?

d.

 

[DavidWHouse]

Added notes:

I did search for an answer before posting, but did not mention it.What may be happening is that first time Evaluate is used, it does nothing, so the reported RGB is simply the default color.
There is a slight mistake in the comments in my code. The second "#2" should really be "#3".

 

[JoeMo]

Don't think you can use Evaluate to return the value of a VBA function like that. So, the Evaluate lines are doing nothing. You get the right answer after the first Evaluate b/c you have correctly set the color with this line:

.RGB = RGB(200, 150, 125)

after the first Evaluate.

 

[DavidWHouse]

Joe,

You are absolutely right, as my further testing revealed to me: Evaluate does nothing in this instance. (I was/am tardy in posting about those results...) Thus, or so it presently seems, Evaluate is not the tool to use in order to set dot notated parameters.

And of course, in most instances, one can set dot notated parameters by using either numbers (if the .param is of the form ".param = <NumericValueRetrievedFromACell>"), or a string where appropriate and used similarly, but how would one store an wd or mso constant as a string in a cell ("msoPattern5Percent"; "wdColorTurquoise", a constant from Word) and then properly set the .param? Or is this a fool's errand?

As far as the matter of why one would want to do this, well if it can be done, then it would be far more self-documenting. That is, it seems to me that, again where one has a set of values stored in a set of cells and intended to assign to a given object with dot notation, it would in many instances be a far more transparent means of designating such values. For example, when one encounters 16776960 in such a cell, what is that? Well, it's <wdColorTurquoise> (again, at least in Word), a designator that needs no further explanation or cell comment. Likewise, in many contexts, .left can be designated in points, but it also can accept wd constants, which would look bizarre, as raw numbers.


d.

 

[DavidWHouse]

Sorry: I'm new and getting used to the thread software. I used the wrong chars to set off the last word in the phrase

...(if the .param is of the form ".param = {NumericValueRetrievedFromACell}"),...

 

[JoeMo]

Suppose the color you want is represented by this: 16776960 and you store it in cell A1. Then you could do this:

Dim myRGBColor as Long
myRGBColor = Range("A1").Value

 

[DavidWHouse]

...Answering my own (revised, rather larger) question, following some further spelunking...

Nothing simple works. There is apparently no elegant solution. However, from excel - Converting a string representation of a constant into a constant? - Stack Overflow we have:

<code>Sub Tester()
    MsgBox WhatIsTheValue("xlTotalsCalculationAverage")
    MsgBox WhatIsTheValue("xlTotalsCalculationCountNums")
End Sub



Function WhatIsTheValue(s As String) As Variant

        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("modTemp")
        Set CodeMod = VBComp.CodeModule

        With CodeMod
            .DeleteLines 1, .CountOfLines
            .InsertLines 1, "Public Function GetEnumVal()"
            .InsertLines 2, "GetEnumVal = " & s
            .InsertLines 3, "End Function"
        End With
        WhatIsTheValue = Application.Run("GetEnumVal")

End Function</code>

I note that immediately after sharing the above code, the author says "But really— don't do that."

In order to use the above, a reference (Tools/References) must be set to "Microsoft Visual Basic for Applications Extensibility" (which on my install is v 5.3). (Or alternatively, see vba - Late Binding VBIDE.VBE in excel - Stack Overflow.) Also, one must create a VBA module called "modTemp" (note the reference in the above code).

I have not tested this extensively, but it appears to work for any constant that Excel knows about...

Finally, the redoubtable Chip Pearson offers another approach: https://groups.google.com/forum/#!topic/microsoft.public.excel.programming/3T7gzz8vt4g




d.

 

[DavidWHouse]

Joe,

Appreciated, sir.

And, yes. That is similar to the approach I have taken to get values from cells, except I use cell naming to identify ranges, and I have a set of routines to name/rename those cells (or the cell to the right, which stores the value of the variable being sought) according to the string they contain.

Thus, for example, where I want to do what I've been talking about— creating collections of values, each of which will generate a different object with it's own attributes, I set up the first or second column in the worksheet as the "title" of the attribute, where columns following in the same row and to the right are identified by further VBA code as to their column number. I can then retrieve the .row from the title of the attribute/param, and the .column using similar means. Thus for example all values pertaining to .left (in one collection or another) are in the same row, with a different column.

Where the titles in the rows have the same names as the variables I use in the code, then I can simply feed a var name into a function, along with a column designation, and it returns the value that pertains to that param for that instance of the object. If the title is bolded, then I take that as a flag that the param itself is required. Thus, some few of the routines (simplified) would be:

Public Function retVarFromExcel(strVarCallout As String) As Variant
    retVarFromExcel = shtVars.Range(strVarCallout).Value
End Function

Public Function retColOfVarFromExcel(strVarCallout As String) As Integer
    retColOfVarFromExcel = shtVars.Range(strVarCallout).Column
End Function

Public Function retRowOfVarFromExcel(strVarCallout As String) As Integer
    retRowOfVarFromExcel = shtVars.Range(strVarCallout).row
End Function

Public Function retShapeVarFromExcel(intColShapevarSet As Integer, strVarCallout As String)
    Dim intRow As Integer
        '
    intRow = retRowOfVarFromExcel(strVarCallout)
    retShapeVarFromExcel = shtVars.Cells(intRow, intColShapevarSet).Value
End Function

Public Function retShapeVarRequired(strVarCallout As String) As Boolean
    Dim intBoldCol As Integer
    Dim intBoldRow As Integer
        '
        '
        '   Get the row, col of the shapeVar title...
    intBoldCol = retColOfVarFromExcel(strVarCallout) - 1
    intBoldRow = retRowOfVarFromExcel(strVarCallout)
        '
        '   If the _title_ of this shapeVar is bolded, then the var is REQUIRED
    retShapeVarRequired = shtVars.Range(Cells(intBoldRow, intBoldCol), Cells(intBoldRow, intBoldCol)).Font.Bold
End Function

d.