是否有一个 VBA 函数需要内置常量的名称并返回相应的值?

Question

我正在编写一个宏来自动设置某些单元格范围的格式。为了使它尽可能通用，我想通过输入字符串在运行时做出一些格式决定。

目前我使用选择大小写语句，这对我来说似乎非常乏味。

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

是否有一个函数采用内置常量的名称并返回相应的值？

Dim strInput As String
strInput = "xlDashDotDot"
Dim nConstVal As Integer
nConstVal = GetConstVal(strInput)

Answer 1

工具 -参考资料 -TypeLib Information.

然后：

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