You can't use CallByName because that requires a reference to an
object, and what you need if not contained in an object.
I think the only way to go it is to use the TypeLibInfo DLL, IF you
have it installed on your computer. In VBA, go to the Tools menu,
choose References, and scroll down in the list until you find "TypeLib
Information". Check that item. If you don't find it in the list,
search your drive for a file named TLBINF32.DLL. If you can't find it,
then you don't have it and my code won't work. If you do find the
file, open the References dialog in VBA, click Browse, navigate to the
file containing TLBINF32.DLL and select that file. I believe that
TLBINF32 is available on MSDN.
The code below takes as input:
ReferenceName
the name of the reference to search, e.g. "ADODB"
GroupName
the name of the enum containing the value, e.g.,
"DataTypeEnum". You can find the enum name in the object browser by
searching for the value name.
ValueName
the name of the value to return, e.g, "adInteger"
If any of these are empty or not found, the function returns Null. You
can then call the code below from your code with something like
Sub ABC()
Dim V As Variant
V = GetNamedValue("ADODB", "DataTypeEnum", "adInteger")
If IsNull(V) Then
Debug.Print "not found"
Else
Debug.Print "Value: " & CStr(V)
End If
End Sub
Function GetNamedValue(ReferenceName As String, _
GroupName As String, _
ValueName As String) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetNamedValue
' Chip Pearson, (e-mail address removed),
www.cpearson.com
' Return the value of a specified enum name.
' ReferenceName = Name of Reference to search.
' GroupName = Enum name containing ValueName.
' ValueName = Name of value in GroupName to return.
' Returns Null if error or not found.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim TLIApp As TLI.TLIApplication
Dim TLibInfo As TLI.TypeLibInfo
Dim Consts As TLI.Constants
Dim ConstEnum As TLI.ConstantInfo
Dim MemInfo As TLI.MemberInfo
Dim N As Long
On Error Resume Next
If ReferenceName = vbNullString Then
GetNamedValue = Null
Exit Function
End If
If GroupName = vbNullString Then
GetNamedValue = Null
Exit Function
End If
If ValueName = vbNullString Then
GetNamedValue = Null
Exit Function
End If
Err.Clear
Set TLIApp = New TLI.TLIApplication
Set TLibInfo = TLIApp.TypeLibInfoFromFile( _
ThisWorkbook.VBProject.References(ReferenceName).FullPath)
If Err.Number <> 0 Then
GetNamedValue = Null
Exit Function
End If
Set Consts = TLibInfo.Constants
Set ConstEnum = Consts.NamedItem(GroupName)
If ConstEnum Is Nothing Then
GetNamedValue = Null
Exit Function
End If
With ConstEnum
For N = 0 To .Members.Count - 1
Err.Clear
Set MemInfo = .Members.Item(N)
If Err.Number = 0 Then
If StrComp(MemInfo.Name, ValueName, vbTextCompare) = 0
Then
GetNamedValue = MemInfo.Value
Exit Function
End If
End If
Next N
End With
ErrH:
GetNamedValue = Null
End Function
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
