VBA store msoThemeColor in variable

Question

I am looking to store mso theme colors in a variable so that the chart colors and patterns can change dynamically (variable Th in this situation). Here is my current code:

Dim Th As Long
Th = "msoThemeColorAccent" & ActiveCell.Offset(-5, 0)
If ActiveCell = "Realized" Then
    ActiveChart.SeriesCollection(Srs).Select

    With Selection.Format.Fill
        .ForeColor.ObjectThemeColor = Th
        .Solid
    End With
    With Selection.Format.Line
       .ForeColor.ObjectThemeColor = Th
    End With
End If

I'm thinking the problem here is that I am not using the correct Dim categorization. I've seen questions on here on storing RGB as variables (using Dim Long seemed to be the solution) but nothing on msoThemeColors yet. Thanks and let me know if I can provide any other details!

Answer 1

It's hard to tell what's going on, because your code relies heavily on default members. Anyway:

Dim Th As Long

This is correct.

Dim themeColorIndex As MsoThemeColorIndex

This is correct and explicit (see MsoThemeColorIndex on MSDN).

As Scott Holtzman said, the possible values here are Enum values, not strings: you simply cannot generate the correct value by concatenating strings into one of the defined Enum names.

So by doing this:

Th = "msoThemeColorAccent" & ActiveCell.Offset(-5, 0)

My guess is that ActiveCell.Offset(-5, 0) must contain a number between 1 and 6. That's a dangerous assumption to make: ActiveCell could be literally anything. If that value is in a specific cell, refer to it by its address:

themeColorIndex = Sheet1.Range("B12").Value

This is slightly better, but it still assumes that the value in B12 can be implicitly converted to a Long integer.

Dim selectedValue As Variant
selectedValue = Sheet1.Range("B12").Value
If Not IsNumeric(selectedValue) Then 
    MsgBox "Invalid value!"
    Exit Sub
End If

If Sheet1.Range("E12").Value <> "Realized" Then Exit Sub

Dim themeColorIndex As MsoThemeColorIndex
themeColorIndex = selectedValue

If you're only interested in msoThemeColorAccent1 through msoThemeColorAccent6, then you'll want B12 to contain values 5 through 10, which are the underlying enum values that you're looking for.

If your worksheet, for usability reasons, must allow values 1 through 6, then you can do this:

Dim themeColorIndex As MsoThemeColorIndex
'msoThemeColorAccent1 underlying value is 5, so we add 4 to the value:
themeColorIndex = selectedValue + 4

Then you're making another dangerous assumption: you assume that there's an active chart! Assuming the chart exists in Sheet1, you'd much better refer to it, again, explicitly:

Dim theChart As ChartObject
Set theChart = Sheet1.ChartObjects(1)
With theChart.SeriesCollection(srs) 'whatever srs means
    With .Format
        .Fill.ForeColor.ObjectThemeColor = themeColorIndex
        .Fill.Solid
        .Line.ForeColor.ObjectThemeColor = themeColorIndex
    End With
End With