Is there a VBA function that expects the name of a build-in constant and returns the corresponding value?

Question

I'm writing a macro to automate the formatting of certain cell ranges. In order to make it as versatile as possible, I would like to make some of the formatting decisions at runtime by entering strings.

Currently I use a select-case-statement, which seems very tedious to me.

Dim strInput As String
strInput = "xlDashDotDot"

Dim nConstVal As Integer

Select Case strInput
    Case "xlContinuous"
        nConstVal = xlContinuous

    Case "xlDash"
        nConstVal = xlDash

    Case "xlDashDot"
        nConstVal = xlDashDot

    Case "xlDashDotDot"
        nConstVal = xlDashDotDot

    Case "xlDot"
        nConstVal = xlDot

    Case "xlDouble"
        nConstVal = xlDouble

    Case "xlLineStyleNone"
        nConstVal = xlLineStyleNone

    Case "xlSlantDashDot"
        nConstVal = xlSlantDashDot
End Select

[...]

rng.Borders.LineStyle = nConstVal

Is there a function that takes the name of a build-in constant and returns the corresponding value?

Dim strInput As String
strInput = "xlDashDotDot"

Dim nConstVal As Integer

nConstVal = GetConstVal(strInput)

What problem are you trying to solve? Are you trying to get the underlying value of named constants for documentation purposes? That's already on learn.microsoft.com; all these constants can be found in the Object Browser (F2), and Ctrl+i on the identifier name should give you a tooltip with the underlying value. In what situation are you dealing with a string value holding a programmatic identifier name? The single most likely thing, as @GSerg alluded to, is that you are solving the wrong problem. — Mathieu Guindon, Jan 17 '20 at 21:15
Assuming the actual `strInput` is coming from a worksheet that contains your formatting metadata, a better way to do this would be to make the worksheet give you the actual underlying values rather than the names. You could have a lookup table that makes it more user-friendly. At the end of the day this is *data*, not code - and data doesn't belong in code, that's why it's tedious =) — Mathieu Guindon, Jan 17 '20 at 21:19
https://stackoverflow.com/questions/10036711/converting-a-string-representation-of-a-constant-into-a-constant — Siddharth Rout, Jan 18 '20 at 09:19
https://stackoverflow.com/questions/14491613/can-i-evaluate-an-excel-vb-constant-that-is-in-string-format — Siddharth Rout, Jan 18 '20 at 09:20
@SiddharthRout https://stackoverflow.com/questions/57526534/is-there-a-way-to-save-macro-script-into-a-variable-as-string-in-visual-basic/57527540#comment101519893_57526534 — GSerg, Jan 18 '20 at 17:55

score 1 · Answer 1 · answered Jan 17 '20 at 21:29

Tools - References - TypeLib Information.

Then:

Option Explicit

' Cache this variable on module level to not reload it on each call
Private mLib As TLI.TypeLibInfo

Private Function EnumValueFromString(ByVal Constants As TLI.Constants, ByVal EnumName As String, ByVal EnumValueName As String) As Variant
  EnumValueFromString = Constants.NamedItem(EnumName).GetMember(EnumValueName).Value
End Function


Sub Test()
  With New TLI.TLIApplication
    Set mLib = .TypeLibInfoFromFile(Application.Path & "\EXCEL.EXE")
  End With


  Debug.Print EnumValueFromString(mLib.Constants, "XlLineStyle", "xlDashDot")
  Debug.Print EnumValueFromString(mLib.Constants, "XlLineStyle", "xlDashDotDot")
  Debug.Print EnumValueFromString(mLib.Constants, "XlLineStyle", "xlDot")
End Sub

@MathieuGuindon Ah yes, built in. – GSerg Jan 17 '20 at 21:45 — GSerg, Jan 17 '20 at 21:45

Is there a VBA function that expects the name of a build-in constant and returns the corresponding value?

1 Answers1