Wanted: ? UnknownFunction("vbOK") returns 1 in VBE Immediate window

[G Lykos]

Thanks!
George

 

[OssieMac]

Hi,

It is not a function; it is a constant. If you lookup msgbox function and
msgbox constants in Help you will find out more about it. The following is a
small example of its use where the users selection is returned to a variable.

Sub test()
Dim Response As Variant

Response = MsgBox("Do you want to continue?", vbOKCancel)

If Response = vbOK Then
MsgBox "User selected OK"
'Insert required code here
Else
MsgBox "User cancelled." & vbLf & _
"Processing terminated."

End If

End Sub

 

[Chip Pearson]

Dim Response As Variant

You'll get Intellisense support and lose the overhead inherent in
Variants if you declare Response as

Dim Response As VbMsgBoxResult


VbMsgBoxResult is the enum that defines vbYes, vbNo, and all the other
MsgBox constants.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Chip Pearson]

IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[G Lykos]

Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that it
appears I need to know the vbConstant's associated enumeration. VBA module
code interpretation, and the VBE Immediate window, don't require such to use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and paste
it into a spreadsheet. First thought was to read those names into an array
as strings, then process it using some UnknownStandardFunction to evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like 'msoShape(1,1) =
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George

IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Thanks!
George

 

[G Lykos]

Chip, ran and executed the suggested code. Searching for a solution to
"programmatic access not trusted" led me to your related thread in 2005,
same subject. Downloaded xlConsts2.zip from your site, looks like it
already does what I was interested in doing. Thanks for making it
available!

Best regards,
George

IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Thanks!
George

 

[Chip Pearson]

If you don't want to include the enumeration name, you can search all
enumerations looking for the desired name. The following code will
search the entire VBA typelib for 'vbYes' and its associated numeric
value. It is not necessary to specify "VbMsgboxResult" because all
enumerations are searched.

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim ValueName As String
Dim ConstInfo As TLI.ConstantInfo
Dim Found As Boolean

ValueName = "vbYes"
Found = False
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)

For Each ConstInfo In TLITypeLibInfo.Constants
For Each MemInfo In ConstInfo.Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Found = True
Exit For
End If
Next MemInfo
If Found = True Then
Exit For
End If
Next ConstInfo

End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that it
appears I need to know the vbConstant's associated enumeration. VBA module
code interpretation, and the VBE Immediate window, don't require such to use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and paste
it into a spreadsheet. First thought was to read those names into an array
as strings, then process it using some UnknownStandardFunction to evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like 'msoShape(1,1) =
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George

IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Thanks!
George

 

[G Lykos]

Excellent - thanks much!

This, with the addition of the two other library files as outlined in your
2005 thread, would appear to lend itself to a function to perform the
in-line evaluation of any VBE constant name (vb, xl, mso) stored in a
variable. Will play around with it, advise all.

Regards,
George

If you don't want to include the enumeration name, you can search all
enumerations looking for the desired name. The following code will
search the entire VBA typelib for 'vbYes' and its associated numeric
value. It is not necessary to specify "VbMsgboxResult" because all
enumerations are searched.

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim ValueName As String
Dim ConstInfo As TLI.ConstantInfo
Dim Found As Boolean

ValueName = "vbYes"
Found = False
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)

For Each ConstInfo In TLITypeLibInfo.Constants
For Each MemInfo In ConstInfo.Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Found = True
Exit For
End If
Next MemInfo
If Found = True Then
Exit For
End If
Next ConstInfo

End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that
it
appears I need to know the vbConstant's associated enumeration. VBA
module
code interpretation, and the VBE Immediate window, don't require such to
use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop
to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and paste
it into a spreadsheet. First thought was to read those names into an
array
as strings, then process it using some UnknownStandardFunction to evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like 'msoShape(1,1)
=
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array
to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a
routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from
the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George

IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






Thanks!
George

 

[G Lykos]

P.S. Misspoke regarding a resource - guidance on connecting to the three
(vb, xl, mso) libraries will come from your web site download file
XLConsts2a.zip. The thread mentioned had led me there.

Excellent - thanks much!

This, with the addition of the two other library files as outlined in your
2005 thread, would appear to lend itself to a function to perform the
in-line evaluation of any VBE constant name (vb, xl, mso) stored in a
variable. Will play around with it, advise all.

Regards,
George

If you don't want to include the enumeration name, you can search all
enumerations looking for the desired name. The following code will
search the entire VBA typelib for 'vbYes' and its associated numeric
value. It is not necessary to specify "VbMsgboxResult" because all
enumerations are searched.

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim ValueName As String
Dim ConstInfo As TLI.ConstantInfo
Dim Found As Boolean

ValueName = "vbYes"
Found = False
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)

For Each ConstInfo In TLITypeLibInfo.Constants
For Each MemInfo In ConstInfo.Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Found = True
Exit For
End If
Next MemInfo
If Found = True Then
Exit For
End If
Next ConstInfo

End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that
it
appears I need to know the vbConstant's associated enumeration. VBA
module
code interpretation, and the VBE Immediate window, don't require such to
use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop
to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and
paste
it into a spreadsheet. First thought was to read those names into an
array
as strings, then process it using some UnknownStandardFunction to
evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like
'msoShape(1,1) =
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array
to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a
routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from
the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George


IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






Thanks!
George

 

[Chip Pearson]

The library that is searched for the value is assigned in the
following line of code:

Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)

Here, the VBA library is hard coded into the function.

For constants such as xlValues, you'd have to switch to the Excel
library, since that is where they are defined. You could write code
that would examine the first 2 or 3 letters of the constant name and
based on that set the typelib info to the file pointed to by the
approriate Reference:

Dim RefName As String
Dim ConstName As String
' other code

ConstName = "xlValues"
If StrComp(Left(ConstName, 2), "xl", vbTextCompare) = 0 Then
RefName = "EXCEL"
ElseIf StrComp(Left(ConstName, 2), "vb", vbTextCompare) = 0 Then
RefName = "VBA"
ElseIf StrComp(Left(ConstName, 3), "mso", vbTextCompare) = 0 Then
RefName = "OFFICE"
' and so on for various libraries
End If

Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References(RefName).FullPath)

This isn't a bullet-proof method, but it would get you 95% of the way
there.



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

P.S. Misspoke regarding a resource - guidance on connecting to the three
(vb, xl, mso) libraries will come from your web site download file
XLConsts2a.zip. The thread mentioned had led me there.

Excellent - thanks much!

This, with the addition of the two other library files as outlined in your
2005 thread, would appear to lend itself to a function to perform the
in-line evaluation of any VBE constant name (vb, xl, mso) stored in a
variable. Will play around with it, advise all.

Regards,
George

If you don't want to include the enumeration name, you can search all
enumerations looking for the desired name. The following code will
search the entire VBA typelib for 'vbYes' and its associated numeric
value. It is not necessary to specify "VbMsgboxResult" because all
enumerations are searched.

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim ValueName As String
Dim ConstInfo As TLI.ConstantInfo
Dim Found As Boolean

ValueName = "vbYes"
Found = False
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)

For Each ConstInfo In TLITypeLibInfo.Constants
For Each MemInfo In ConstInfo.Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Found = True
Exit For
End If
Next MemInfo
If Found = True Then
Exit For
End If
Next ConstInfo

End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




Chip, thanks for the guidance. Will be tomorrow before I can experiment
with this. A concern regarding this solution for my intended use is that
it
appears I need to know the vbConstant's associated enumeration. VBA
module
code interpretation, and the VBE Immediate window, don't require such to
use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell comments,
and wish to set the comment box shape to something special. I set it via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants for
various shapes, e.g. msoShape24pointStar. I'd like to write a small loop
to
cycle through them, setting the box as I go, so I can see what they look
like. I can relatively easily find a list of the constant names and
paste
it into a spreadsheet. First thought was to read those names into an
array
as strings, then process it using some UnknownStandardFunction to
evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like
'msoShape(1,1) =
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the array
to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this case)
take the AutoShapeType enumeration (is it one?), feed its name to a
routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly from
the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George


IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library (email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






Thanks!
George

 

[G Lykos]

Function EvalConst(Constant As String) As Variant
'requires 'TypeLib Info' reference library



Dim TLIApp As TLI.TLIApplication
Dim TypeLibInfo As TLI.TypeLibInfo
Dim ConstantInfo As TLI.ConstantInfo
Dim MemberInfo As TLI.MemberInfo



Dim Library As Variant, Prefix As Variant
Dim Reference As String
Dim Found As Boolean
Dim i As Integer



EvalConst = CVErr(xlErrRef) 'not-found error return

Library = Array("ACCESS", "EXCEL", "OFFICE", "OUTLOOK", "VBA", "WORD")
Prefix = Array("ac", "xl", "mso", "ol", "vb", "wd")
For i = 0 To UBound(Library)
If LCase(Constant) Like (Prefix(i) & "*") Then Reference = Library(i)
Next i
If Reference = vbNullString Then Exit Function 'didn't recognize library

Set TLIApp = New TLI.TLIApplication
Set TypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References(Reference).FullPath)

For Each ConstantInfo In TypeLibInfo.Constants
For Each MemberInfo In ConstantInfo.Members
If StrComp(Constant, MemberInfo.Name, vbTextCompare) = 0 Then
EvalConst = CStr(MemberInfo.Value): Found = True: Exit For
End If
Next MemberInfo

If Found Then Exit For
Next ConstantInfo

End Function

The library that is searched for the value is assigned in the
following line of code:

Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)

Here, the VBA library is hard coded into the function.

For constants such as xlValues, you'd have to switch to the Excel
library, since that is where they are defined. You could write code
that would examine the first 2 or 3 letters of the constant name and
based on that set the typelib info to the file pointed to by the
approriate Reference:

Dim RefName As String
Dim ConstName As String
' other code

ConstName = "xlValues"
If StrComp(Left(ConstName, 2), "xl", vbTextCompare) = 0 Then
RefName = "EXCEL"
ElseIf StrComp(Left(ConstName, 2), "vb", vbTextCompare) = 0 Then
RefName = "VBA"
ElseIf StrComp(Left(ConstName, 3), "mso", vbTextCompare) = 0 Then
RefName = "OFFICE"
' and so on for various libraries
End If

Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References(RefName).FullPath)

This isn't a bullet-proof method, but it would get you 95% of the way
there.



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

P.S. Misspoke regarding a resource - guidance on connecting to the three
(vb, xl, mso) libraries will come from your web site download file
XLConsts2a.zip. The thread mentioned had led me there.

Excellent - thanks much!

This, with the addition of the two other library files as outlined in
your
2005 thread, would appear to lend itself to a function to perform the
in-line evaluation of any VBE constant name (vb, xl, mso) stored in a
variable. Will play around with it, advise all.

Regards,
George

If you don't want to include the enumeration name, you can search all
enumerations looking for the desired name. The following code will
search the entire VBA typelib for 'vbYes' and its associated numeric
value. It is not necessary to specify "VbMsgboxResult" because all
enumerations are searched.

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim ValueName As String
Dim ConstInfo As TLI.ConstantInfo
Dim Found As Boolean

ValueName = "vbYes"
Found = False
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)

For Each ConstInfo In TLITypeLibInfo.Constants
For Each MemInfo In ConstInfo.Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Found = True
Exit For
End If
Next MemInfo
If Found = True Then
Exit For
End If
Next ConstInfo

End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




Chip, thanks for the guidance. Will be tomorrow before I can
experiment
with this. A concern regarding this solution for my intended use is
that
it
appears I need to know the vbConstant's associated enumeration. VBA
module
code interpretation, and the VBE Immediate window, don't require such
to
use
the constants directly.

Allow me to briefly explain my interest. I'm working with cell
comments,
and wish to set the comment box shape to something special. I set it
via
Cell.Comment.Shape.AutoShapeType. There are 139 related vbConstants
for
various shapes, e.g. msoShape24pointStar. I'd like to write a small
loop
to
cycle through them, setting the box as I go, so I can see what they
look
like. I can relatively easily find a list of the constant names and
paste
it into a spreadsheet. First thought was to read those names into an
array
as strings, then process it using some UnknownStandardFunction to
evaluate
the constants on the fly.

In the absence of such, I took the names in the spreadsheet and using
leading and trailing text cells to concatenate VB code like
'msoShape(1,1) =
"msoShape24pointStar" : msoShape(1,2) = msoShape24pointStar' (139 lines
worth) and copied/pasted it into a module code block, then fed the
array
to
a small display loop. This worked, but sure seems crude.

Perhaps an alternative, more elegant approach would be to (in this
case)
take the AutoShapeType enumeration (is it one?), feed its name to a
routine
like you outlined, and just cycle through all members to see names and
values. That would effectively be dumping out the library directly
from
the
source.

Incidentally, I do see 'TypeLib Information' in my VBE list of
available
references. It appears the suggested code would find all flavors of
VBE-available constants, i.e. vbYes, msoShape24pointStar, and so forth.

Thoughts?

Thanks,
George


IF you have the TypeLib Info DLL installed on your computer, you can
do this. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If that appears in the list, check it. If it
isn't in the list, you probably don't have the required library
(email
me for more info). The file name you need to find is TLBINF32.dll.
With the reference in place, you can use code similar to the
following:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim EnumName As String
Dim ValueName As String

EnumName = "VBMsgBoxResult"
ValueName = "vbYes"

Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile( _
Filename:=ThisWorkbook.VBProject.References("VBA").FullPath)
For Each MemInfo In
TLITypeLibInfo.Constants.NamedItem(EnumName).Members
If StrComp(ValueName, MemInfo.Name, vbTextCompare) = 0 Then
Debug.Print "Value Name: " & ValueName & _
" is equal to: " & CStr(MemInfo.Value)
Exit For
End If
Next MemInfo
End Sub

This looks up the string "vbYes" in the "VBMsgBoxResult" enumeration
and returns the numeric value (6) which is assigned to vbYes.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






Thanks!
George