Parse enum values by name in VBA

Question

I want to write some VBA code that should parse MSForms-constant names (given as a string, like "fmTextAlignLeft") into their actual value. Since there is no native way to do so I was considering to put the name of the constant into a powershell code that will then be executed and return the result.

Private Sub ParseEnumByName(EnumConst As String)
    Dim WScript As New WshShell
    Dim PSCode As String
    Dim Result
    PSCode = "(some code)" & EnumConst & "(more code with exit $Value statement)"

    Result = WScript.Run("Powershell -command """ & PSCode & """", 0, True)

    ParseEnumByName = Result
End Sub

This should be feasible by iterating through all enums in the MSForms library and get the values out of them with something like [System.Enum]::GetNames( [System.Windows.Forms.FormBorderStyle] ) or maybe something like explained here: How to convert a string to a enum?

The problem is that the System.Windows.Forms library contains totally different enums and typenames than the MSForms library available in VBA.

I tried to Add-Type -Path "C:\Windows\SysWOW64\FM20.DLL" where the MSForms library is stored but it returns an error saying the file or assembly or some related file could not be found.

How may I get a reference to MSForms in Powershell?

Edit: I have actually found a demi-native way in VBA (Excel VBA only) to solve this issue without passing values to external script hosts. Please see below.

Given that the enumerated form MSForms are unlikely to change that much, you could just hard-code the values into your program in an array or something. That’s not really going to be much worse than launching an external powershell process and running a script to do the lookups. You could even use the powershell script to generate the hard-coded values that you embed into your program. — mclayton
– mclayton, Commented Jan 31, 2020 at 22:46
Related/half duplicate: Is there a way to get the enums in VBA? — GSerg
– GSerg, Commented Aug 3, 2024 at 8:18
Duplicate with worse but more universally working solutions: How do I convert a String to its Enum value similar to Java's .valueOf? — GSerg
– GSerg, Commented Aug 3, 2024 at 8:19
Related/half duplicate: Is there a way to count elements in a VBA enum? — GSerg
– GSerg, Commented Aug 3, 2024 at 8:20

GuidoT · Accepted Answer · 2020-02-03 14:41:25Z

3

Here's the function I figured out. So far it seems to work with all pre-defined enums and constants and also self defined enums in Excel. The function must be placed in a module!

Static Function ParseValue(StringValue) As Variant
    Dim ParseValueBuffer As Variant

    If IsEmpty(ParseValueBuffer) Then
        ParseValueBuffer = 1
        Application.Run ("'ParseValue " & StringValue & "'")
        ParseValue = ParseValueBuffer
        ParseValueBuffer = Empty
    Else
        ParseValueBuffer = StringValue
    End If
End Function

Sub TestMe()
    MsgBox "First line" & ParseValue("vbcrlf") & "Second line"
    MsgBox ParseValue("fmTextAlignCenter") 'Should return "2" (if MSForms is referenced)
    MsgBox ParseValue("rgbblue") 'Should return 16711680
End Sub

edited Feb 3, 2020 at 14:41

answered Feb 3, 2020 at 8:08

GuidoT

3222 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jacob spitzer Over a year ago

Works great! But, only in Excel VBA. Tested in Access VBA - not working

Collectives™ on Stack Overflow

Parse enum values by name in VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related