Clever Geek Handbook

Converting the string representation of a constant to a constant?

I am trying to accept a formatting constant from a data cell, so I have the string "xlTotalsCalculationAverage". How can I translate this into the Excel constant it represents; constant xlTotalsCalculationAverage

is equal to integer 2.

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

- static representation.

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

- my goal.

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

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

+3


source to share


2 answers


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 it.

+9


source


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

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

Here is the sample 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

It is admittedly impractical to include thousands of modules in your project, but it is quite rare that you really need this kind of functionality, so I would just include what I need in a specific case.

0


source






More articles:


All Articles