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.
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