Hi,

Im looking to return the value of an object constant from the string
equivelent.

As an example I am passing "adInteger" as a string, and wish to return the
integer value (from ADO).

I know this can be laid out in a case statement - but I thought there might
be a better way.

I've tried CallByName - but I couldn't get this to work

Any other suggestions?

Many thanks
Rob

not something I've ever done.

x = Evaluate(vbRed)
MsgBox x

"Rob Wills" wrote:

Hi,

Im looking to return the value of an object constant from the string
equivelent.

As an example I am passing "adInteger" as a string, and wish to return the
integer value (from ADO).

I know this can be laid out in a case statement - but I thought there might
be a better way.

I've tried CallByName - but I couldn't get this to work

Any other suggestions?

Many thanks
Rob

Hi,

I tried your example in the immediate window
?evaluate("AdInteger")
- but this gives an error message of 2029...

your example works in this scanario:
?evaluate(adinteger)
but his is the same as:
?adInteger
i.e the eactual constant - and not the string

remove the quotes

having said that, it kinda breaks what you want anyways. no go. sorry.

"Rob Wills" wrote:


Hi,

I tried your example in the immediate window
?evaluate("AdInteger")
- but this gives an error message of 2029...

your example works in this scanario:
?evaluate(adinteger)
but his is the same as:
?adInteger
i.e the eactual constant - and not the string

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, , 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).F ullPath)
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)




On Thu, 17 Sep 2009 05:04:01 -0700, Rob Wills
wrote:

Hi,

Im looking to return the value of an object constant from the string
equivelent.

As an example I am passing "adInteger" as a string, and wish to return the
integer value (from ADO).

I know this can be laid out in a case statement - but I thought there might
be a better way.

I've tried CallByName - but I couldn't get this to work

Any other suggestions?

Many thanks
Rob

On Thu, 17 Sep 2009 08:34:38 -0500, Chip Pearson
wrote:

For N = 0 To .Members.Count - 1
should be
For N = 1 To .Members.Count

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


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, , 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).F ullPath)
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)




On Thu, 17 Sep 2009 05:04:01 -0700, Rob Wills
wrote:

Hi,

Im looking to return the value of an object constant from the string
equivelent.

As an example I am passing "adInteger" as a string, and wish to return the
integer value (from ADO).

I know this can be laid out in a case statement - but I thought there might
be a better way.

I've tried CallByName - but I couldn't get this to work

Any other suggestions?

Many thanks
Rob

Hi,

thanks, but unfotunately I can't get access to that DLL...

So I can't even sign off htis works....

I think I'm going to have to revert to a case statement...

=============
Select Case StrTemp
Case "AdInteger": i= 3
Case "AdChar": i = 129
etc....

end select
================
Thanks of for your help

"Chip Pearson" wrote:

On Thu, 17 Sep 2009 08:34:38 -0500, Chip Pearson
wrote:

For N = 0 To .Members.Count - 1
should be
For N = 1 To .Members.Count

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


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, , 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).F ullPath)
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)




On Thu, 17 Sep 2009 05:04:01 -0700, Rob Wills
wrote:

Hi,

Im looking to return the value of an object constant from the string
equivelent.

As an example I am passing "adInteger" as a string, and wish to return the
integer value (from ADO).

I know this can be laid out in a case statement - but I thought there might
be a better way.

I've tried CallByName - but I couldn't get this to work

Any other suggestions?

Many thanks
Rob

Here's a few for you

' DataTypeEnum
Private Const adArray As Long = 8192
Private Const adBigInt As Long = 20
Private Const adBinary As Long = 128
Private Const adBoolean As Long = 11
Private Const adBSTR As Long = 8
Private Const adChapter As Long = 136
Private Const adChar As Long = 129
Private Const adCurrency As Long = 6
Private Const adDate As Long = 7
Private Const adDBDate As Long = 133
Private Const adDBTime As Long = 134
Private Const adDBTimeStamp As Long = 135
Private Const adDecimal As Long = 14
Private Const adDouble As Long = 5
Private Const adEmpty As Long = 0
Private Const adError As Long = 10
Private Const adFileTime As Long = 64
Private Const adGUID As Long = 72
Private Const adIDispatch As Long = 9
Private Const adInteger As Long = 3
Private Const adIUnknown As Long = 13
Private Const adLongVarBinary As Long = 205
Private Const adLongVarChar As Long = 201
Private Const adLongVarWChar As Long = 203
Private Const adNumeric As Long = 131
Private Const adPropVariant As Long = 138
Private Const adSingle As Long = 4
Private Const adSmallInt As Long = 2
Private Const adTinyInt As Long = 16
Private Const adUnsignedBigInt As Long = 21
Private Const adUnsignedInt As Long = 19
Private Const adUnsignedSmallInt As Long = 18
Private Const adUnsignedTinyInt As Long = 17
Private Const adUserDefined As Long = 132
Private Const adVarBinary As Long = 204
Private Const adVarChar As Long = 200
Private Const adVariant As Long = 12
Private Const adVarNumeric As Long = 139
Private Const adVarWChar As Long = 202
Private Const adWChar As Long = 130

Regards,
Peter T


"Rob Wills" wrote in message
...
Hi,

Im looking to return the value of an object constant from the string
equivelent.

As an example I am passing "adInteger" as a string, and wish to return the
integer value (from ADO).

I know this can be laid out in a case statement - but I thought there
might
be a better way.

I've tried CallByName - but I couldn't get this to work

Any other suggestions?

Many thanks
Rob