Excel VBA - Dynamic Variant

Question

Not sure if the title is correct as I'm not a experience.

I have the following code, which I don't understand why it doesn't work.

Sub AddEvent2()

    Dim p As String
    Dim rgb1, rgbULP, rgbPULP, rgbSPULP, rgbXLSD, rgbALPINE, rgbJET, rgbSLOPS As Variant

    p = Sheets("AdminSheet").Cells(4, 23).Value 'Product

    rgbULP = rgb(177, 160, 199)
    rgbPULP = rgb(255, 192, 0)
    rgbSPULP = rgb(0, 112, 192)
    rgbXLSD = rgb(196, 189, 151)
    rgbALPINE = rgb(196, 215, 155)
    rgbJET = rgb(255, 255, 255)
    rgbSLOPS = rgb(255, 0, 0)
    rgb1 = "rgb" & p
    ActiveSheet.Range("A1").Value = rgb1

End Sub

I guess is basic knowledge but I would like to have the value of the variant (etc. rgbULP = rgb(177, 160, 199) or rgbPULP = rgb(255, 192, 0)) in Range("A1") but not sure what's wrong with it.

Can someone pls explain me?

Regards, George.

You haven't said what is happening? The code looks ok, although the fact that RGB hasn't capitalised itself makes me think you've used rgb as the name of something. If p is equal to say ULP and you want to get the value of the rgbULP variable in A1, your approach is not going to work. You need a dictionary or collection or similar that can store name/value pairs. — Rory
– Rory, Commented Mar 9, 2021 at 8:29

FunThomas · Accepted Answer · 2021-03-15 07:07:37Z

4

Your last statement, ActiveSheet.Range("A1").Value = rgb1 will simply write the content of rgb1 into the cell - that is for example the string rgbALPINE. There is no way to force the VBA compiler to interpret the content of rgb1 as a variable name and look into that variable.

As you have a simple list product name - color, you could use a VBA collection - a collection is basically a list of pairs. Every pair has a name (that is the key) and a value - the value can be anything (Variant). When you know the name (the key), you can easily get the value. You know collections from many things in VBA, eg the Workbooks or the Worksheets list.

The following function returns the RGB-Value for a product. It uses a static variable for the collection. static means that the variable "survives" the end of the routine so that the collection must be initialized only once. Note that RGB-Values are internally stored as Long, this is the reason the function returns a Long.

Function getColor(prod As String) As Long
    Static colorList As Collection
    If colorList Is Nothing Then
        ' Build the collection if it doesn't exist
        Set colorList = New Collection
        colorList.Add RGB(177, 160, 199), "ULP"
        colorList.Add RGB(255, 192, 0), "PULP"
        colorList.Add RGB(0, 112, 192), "SPULP"
        colorList.Add RGB(196, 189, 151), "XLSD"
        colorList.Add RGB(196, 215, 155), "ALPINE"
        colorList.Add RGB(255, 255, 255), "JET"
        colorList.Add RGB(255, 0, 0), "SLOPS"
    End If
    On Error Resume Next         ' Prevent runtime error if prod is not part of list
    getColor = colorList(prod)
    On Error GoTo 0
    
End Function

Your current AddEvent2-routine is very short. However, I wonder if you really want to write the color number into the cell. I assume that you want to use the color as cell color.

Sub AddEvent2()
    Dim p As String, color as Long
    p = Sheets("AdminSheet").Cells(4, 23).Value 'Product
    color = getColor(p)
    If color <> 0 Then
        ActiveSheet.Range("A1").Value = color           ' This writes the color number into the cell
        ActiveSheet.Range("A1").Interior.Color = color  ' This set the cell color
        ActiveSheet.Shapes(1).Fill.ForeColor.RGB = color ' This sets the color of a Shape
    End If
End Sub

edited Mar 15, 2021 at 7:07

answered Mar 9, 2021 at 8:52

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Geo Koro Over a year ago

Thank you @FunThomas for the detailed and informative answer. Collection looks very handy tool, I need to understand them more. I did not explain what I am doing, but that code was part of [link] (stackoverflow.com/questions/66530461/…). So, I want to colour shapes according to the product. Despite that, trying to understand and use your function, I'm getting just a big number not the RGB for that product. Am i missing something? George.

FunThomas Over a year ago

RGB's are just numbers, stored in a long, that's okay. Assign this number to Fill.ForeColor.RGB-property of a shape. See the updated code.

Geo Koro Over a year ago

Thank you so much @FunThomas for your time!! Works perfect!

Collectives™ on Stack Overflow

Excel VBA - Dynamic Variant

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related