CODEWITHSUNDEEP
excelvba
Demosthenex
Demosthenex

Reputation: 4451

Converting a string representation of a constant into a constant?

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?

Upvotes: 3

Views: 4177

Answers (2)

Jbjstam
Jbjstam

Reputation: 884

Since there is no really practical solution, you're generally stuck writing your own switch statements. Since the enums of the .NET Interop Libraries and VBA are, as far as I know, always the same, I wrote a program that generates VBA modules for each public enum inside a (list of) dll's. Here's the output for my Office.Core, PowerPoint, Word, Excel, Publisher and Outlook dll's:

https://gitlab.com/jbjurstam/VbaHelpers/tree/master/GenerateVbaEnumHelpers/bin/Release/output

Here's an example of the code it generates for each enum:

Attribute VB_Name = "wMsoHyperlinkType"
Function MsoHyperlinkTypeFromString(value As String) As MsoHyperlinkType
    If IsNumeric(value) Then
        MsoHyperlinkTypeFromString = CInt(value)
        Exit Function
    End If

    Select Case value
        Case "msoHyperlinkRange": MsoHyperlinkTypeFromString = msoHyperlinkRange
        Case "msoHyperlinkShape": MsoHyperlinkTypeFromString = msoHyperlinkShape
        Case "msoHyperlinkInlineShape": MsoHyperlinkTypeFromString = msoHyperlinkInlineShape
    End Select
End Function

Function MsoHyperlinkTypeToString(value As MsoHyperlinkType) As String
    Select Case value
        Case msoHyperlinkRange: MsoHyperlinkTypeToString = "msoHyperlinkRange"
        Case msoHyperlinkShape: MsoHyperlinkTypeToString = "msoHyperlinkShape"
        Case msoHyperlinkInlineShape: MsoHyperlinkTypeToString = "msoHyperlinkInlineShape"
    End Select
End Function

Admittedly, it's not practical to include thousand modules in your project, but it's quite rare that you really need this kind of functionality, so I'd just include the things I need at the particular occasion.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166790

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.

Upvotes: 9

Related Questions