Converting a string representation of a constant into a constant?

Question

I'm trying to accept a formatting constant from a data cell, so I have a string "xlTotalsCalculationAverage". How can I translate that into the Excel constant it represents; the constant xlTotalsCalculationAverage is equal to the integer 2.

ActiveSheet.ListObjects(1).ListColumns(RemainingHeader).TotalsCalculation = xlTotalsCalculationAverage

is a static representation.

TargetTotal = something("xlTotalsCalculationAverage")
ActiveSheet.ListObjects(1).ListColumns(RemainingHeader).TotalsCalculation = TargetTotal

is my goal.

I could make a huge case or switch statement, but it seem silly to duplicate all the possible values.

How can I make Excel convert this string to a known constant value?

Answer 1

There's always this:

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

But really - don't do that.