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?
source to share
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.
source to share
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.
source to share