CODEWITHSUNDEEP
vbafunctionconstants
AFoeee
AFoeee

Reputation: 751

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

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)

Upvotes: 0

Views: 183

Answers (1)

GSerg
GSerg

Reputation: 78185

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

Upvotes: 1

Related Questions