CODEWITHSUNDEEP
excelvba
Geo Koro
Geo Koro

Reputation: 75

Excel VBA - Dynamic Variant

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.

Upvotes: 1

Views: 206

Answers (1)

FunThomas
FunThomas

Reputation: 29171

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

Upvotes: 4

Related Questions