Warm tip: This article is reproduced from stackoverflow.com, please click
forms powershell vba

Parse enum values by name in VBA

发布于 2020-04-07 10:10:54

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.

Questioner
GuidoT
Viewed
31
GuidoT 2020-02-03 22:41

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