Getting the name of a constant from the value 3

[mlesniak001]

this seems to be a simple question, but i don't know how to do this...

so here is the problem: within the pre-defined VBA constants there are names and coresponding values. When using the constant's name, you have easy access to the value. For instance, the simple command of:

Debug.Print Office.MsoShapeType.msoTextBox

returns the value of:

47

However, what I want to do is the reverse. Once I have the value and know in which specific set of enumerated constants that value exists, I want to decode / get the name of the constant. I would imagine that the code would be something like:

Debug.Print Office.MsoShapeType(47)

but that doesn't work. :-(

FYI: I am doing this as part of building an "inventory" of all objects within a powerpoint presentation including human readable information on the type of objects.

 

[stevexff]

In the .NET world, constants like this are held as Enumerations, and the base class has methods for doing just this. AFIAK, there is no equivalent facility in VBA.

Probably the easiest way would be to load a Scripting.Dictionary of ScriptingDictionaries from a file. The key for the higher level dictionary is the enum name, like Office.MsoShapeType or Office.MsoOrientation. Each of these has a value which is another dictionary, keyed on the number, with the constant names as as the values.

Office.MsoShapeType 47 MsoTextBox
Office.MsoShapeType 2 MsoCallout
Office.MsoOrientation 47 MsoOrientationHorizontal

Then you could maybe

Debug.Print myDict.Item("Office.MsoShapeType").Item(47)

to give you something approaching what you want. But it's a bit clunky...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[fumei]

Clunky maybe, but a pretty good suggestion. I do not know of any real use for such myself, and I am not clear on what the OP wishes to do with it, but a valiant effort to try and help. Worth a star for that.

BTW: these are held as Enumerations in VBA.

Gerry
My paintings and sculpture

 

[mlesniak001]

thanks for the tips and ideas Steve and Gerry. I did some more digging and found (in the microsoft online labrynth, of all places) a "how to", which mentions a "getNames" function, which can be used on Enumerations. (article is here:

http://msdn2.microsoft.com/en-us/library/b1kz44y8.aspx

)

However the journey is not over, since this function is not available to me in my Visual Basic editor...

Digging further, I found a more detailed description of the Enum.GetNames method here:

http://msdn2.microsoft.com/en-us/library/system.enum.getnames.aspx

And this notes that the method is in the assembly "mscorlib (in mscorlib.dll)". The fun part is that "mscorlib.dll" does not exist on my computer - and the versions found on the internet for download will NOT load into my VBA References.

so it looks like a relatively simple method does exist in the VBA world to do what I want, but I just cannot get the library which contains this method into my VBA editor.

Any ideas on what to try next???

 

[strongm]

GetNames is a .NET method

VBA (and VB6) does not have a native method for retrieving enumeration member names.

But it can be done. The following is a minor reworking of an example I put together for the VB5/6 forum. You'll need to add a reference to the Typelib Information library to your project. Then:

[blue]Public Function GetEnumMemberName(strEnumName As String, Optional varVal As Variant) As Variant

    Dim TLIApp As TLIApplication
    Dim myConstant As ConstantInfo
    Dim EnumMember As MemberInfo
    Dim ADOTLI As TypeLibInfo

    Set TLIApp = New TLIApplication
    
    ' Get our type library info from the relevant file
    ' This is the line that makes this example specific to the Office object library
    Set ADOTLI = TLI.TypeLibInfoFromFile("C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL") 
 
    ' Loop through members of libraries constants
    For Each myConstant In ADOTLI.Constants
        If myConstant.TypeKind = TKIND_ENUM And UCase(myConstant.Name) = UCase(strEnumName) Then
            For Each EnumMember In myConstant.Members
                If Not IsMissing(varVal) Then
                    Select Case TypeName(varVal)
                        Case "String"
                            If EnumMember.Name = varVal Then GetEnumMemberName = EnumMember.Value
                        Case "Integer"
                            If EnumMember.Value = varVal Then GetEnumMemberName = EnumMember.Name
                    End Select
                Else
                ' List all members of this enum in debug window if you like
                ' it is left as an exercise to the reader to determine a nice way of returning this info to the calling procedure...
                 Debug.Print EnumMember.Name, EnumMember.Value
                End If
            Next
        End If
    Next
End Function[/blue]

This can then be called like:

[blue]MsgBox GetEnumMemberName("msoShapeType", 17)[/blue]

 

[mlesniak001]

genius, utter genius!

also, one small usage note on strongm's function: in some cases it is helpful to put the value into an interger variable before calling the function (in my case, the function call is nested in a "For Each sh in .Shapes" loop; and calling the function with "sh.AutoShapeType" as the second parameter didn't work). This appears to be a general VBA problem and not anything to do with stongm's function itself.

Cheers to strongm!!!!

 

[stevexff]

strongm's function is good, giving you a primitive kind of reflection capability, which is exactly what's needed here. However, it searches the whole DLL each time which might make it a bit slow if you expect to call it repeatedly. Possible solution:[ol][li]Put it in a class module, and get the data from the DLL once and cache it some way (maybe using the Dictionary scheme above).[/li][li]Have another method that does the actual lookup from the cache (fast, indexed, no I/O)[/li][li]Is there any way to get the location of the DLL from the registry rather than hard coding it?[/li][/ol]

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[strongm]

>it searches the whole DLL each time which might make it a bit slow

Indeed. It is merely an illustrative example rather than a rigorous solution.

 

[fumei]

Love this place. I am constantly learning. strongm, you deserve another star for your persistence in trying to help.

Gerry
My paintings and sculpture