Converting a string representation of a constant into a constant?

excel, vba

Solution

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.

Problem

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?

Original source