xlConstants to numbers?
 

In a cell, say "B3", I have one of the VBA xlConstants as written text, say
'xlDialogActivate'.

Is is possible to get this converted into the number of the xlConstant?

I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

Is is possible to get this converted into the number of the xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's typelib


Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path & "\Excel.exe")

For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written text,
say 'xlDialogActivate'.

Is is possible to get this converted into the number of the xlConstant?

I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

I don't know why. Nether mind, try adding the reference I indicated in Tools
refs, comment all the As Object declarations and uncomment the As TLI. etc

change
Set oTLI = CreateObject("TLI.TLIApplication")
to
Set oTLI = New TLI.TLIApplication

If you couldn't find the ref "TypeLib Information" browse to
c\:windows\system32\tlbinf32.dll

(might be different path for you)

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

If you couldn't find the ref "TypeLib Information" browse to
"c\:windows\system32\tlbinf32.dll"

That might be the problem - I just don't have it :-(



CE




Peter T wrote:
I don't know why. Nether mind, try adding the reference I indicated
in Tools refs, comment all the As Object declarations and uncomment
the As TLI. etc
change
Set oTLI = CreateObject("TLI.TLIApplication")
to
Set oTLI = New TLI.TLIApplication

If you couldn't find the ref "TypeLib Information" browse to
c\:windows\system32\tlbinf32.dll

(might be different path for you)

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

You can use a macro like the following. If you have a string of cells that
you want to evaluate, you can expand this little macro to include a loop and
put the numerical values into cells. HTH Otto
Sub Test()
MsgBox xlDialogActivate
End Sub
"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written text,
say 'xlDialogActivate'.

Is is possible to get this converted into the number of the xlConstant?

I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

I forgot tlbinf32.dll is supplied with VB6. I assume it's freely
distributable but can't find a download link from MS, will look later.

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
If you couldn't find the ref "TypeLib Information" browse to
"c\:windows\system32\tlbinf32.dll"

That might be the problem - I just don't have it :-(



CE




Peter T wrote:
I don't know why. Nether mind, try adding the reference I indicated
in Tools refs, comment all the As Object declarations and uncomment
the As TLI. etc
change
Set oTLI = CreateObject("TLI.TLIApplication")
to
Set oTLI = New TLI.TLIApplication

If you couldn't find the ref "TypeLib Information" browse to
c\:windows\system32\tlbinf32.dll

(might be different path for you)

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

Otto Moehrbach wrote:
You can use a macro like the following. If you have a string of
cells that you want to evaluate, you can expand this little macro to
include a loop and put the numerical values into cells. HTH Otto
Sub Test()
MsgBox xlDialogActivate
End Sub


Yes, I know this - the whole point of my question was to avoid this!

With more than a 1000 xlConstants it would take forever to resolve the
corresponidng numbers this way...

A macro that could loop/list the xlConstants could do this in a jiffy :-)

But, thanks for the suggestion anyway...


CE

xlConstants to numbers?
 

I forgot tlbinf32.dll is supplied with VB6. I assume it's freely
distributable but can't find a download link from MS, will look later.


Found it myself, but apparrently you need VB6, since VBE refuses to make a
reference to the .DLL :-(

Thanks for your effort :-)

My search goes on - anyone?


CE




Regards,
Peter T


"Charlotte E." <@ wrote in message
...
If you couldn't find the ref "TypeLib Information" browse to
"c\:windows\system32\tlbinf32.dll"

That might be the problem - I just don't have it :-(



CE




Peter T wrote:
I don't know why. Nether mind, try adding the reference I indicated
in Tools refs, comment all the As Object declarations and uncomment
the As TLI. etc
change
Set oTLI = CreateObject("TLI.TLIApplication")
to
Set oTLI = New TLI.TLIApplication

If you couldn't find the ref "TypeLib Information" browse to
c\:windows\system32\tlbinf32.dll

(might be different path for you)

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

No you don't need VB6, should work fine in VBA (the code I posted was in
Excel VBA). Probably the dll needs to be installed, ie registered to
windows.

Did you simply copy it to your system32 folder, that's not enough.

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I forgot tlbinf32.dll is supplied with VB6. I assume it's freely
distributable but can't find a download link from MS, will look later.


Found it myself, but apparrently you need VB6, since VBE refuses to make a
reference to the .DLL :-(

Thanks for your effort :-)

My search goes on - anyone?


CE




Regards,
Peter T


"Charlotte E." <@ wrote in message
...
If you couldn't find the ref "TypeLib Information" browse to
"c\:windows\system32\tlbinf32.dll"

That might be the problem - I just don't have it :-(



CE




Peter T wrote:
I don't know why. Nether mind, try adding the reference I indicated
in Tools refs, comment all the As Object declarations and uncomment
the As TLI. etc
change
Set oTLI = CreateObject("TLI.TLIApplication")
to
Set oTLI = New TLI.TLIApplication

If you couldn't find the ref "TypeLib Information" browse to
c\:windows\system32\tlbinf32.dll

(might be different path for you)

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

To register the dll, in the command prompt or Start/Run

regsvr32 c:\windows\system32\tlbinf32.dll

adjust path to suit

I also forgot if you are using xl97/2000 the typelib is in the *.olb
Following should cater for all versions -

Sub dumpConstants()
''' with a ref to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object

Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long
Dim ver As Long, sFile As String

Set oTLI = CreateObject("TLI.TLIApplication")

ver = Left$(Application.Version, 2)
Select Case ver
Case 8: sFile = "excel8.olb"
Case 9: sFile = "excel9.olb"
Case Else: sFile = "excel.exe"
End Select

Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path & "\" & sFile)

For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Note there are new named constants with successive version.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
No you don't need VB6, should work fine in VBA (the code I posted was in
Excel VBA). Probably the dll needs to be installed, ie registered to
windows.

Did you simply copy it to your system32 folder, that's not enough.

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I forgot tlbinf32.dll is supplied with VB6. I assume it's freely
distributable but can't find a download link from MS, will look later.


Found it myself, but apparrently you need VB6, since VBE refuses to make
a reference to the .DLL :-(

Thanks for your effort :-)

My search goes on - anyone?


CE




Regards,
Peter T


"Charlotte E." <@ wrote in message
...
If you couldn't find the ref "TypeLib Information" browse to
"c\:windows\system32\tlbinf32.dll"

That might be the problem - I just don't have it :-(



CE




Peter T wrote:
I don't know why. Nether mind, try adding the reference I indicated
in Tools refs, comment all the As Object declarations and uncomment
the As TLI. etc
change
Set oTLI = CreateObject("TLI.TLIApplication")
to
Set oTLI = New TLI.TLIApplication

If you couldn't find the ref "TypeLib Information" browse to
c\:windows\system32\tlbinf32.dll

(might be different path for you)

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

To register the dll,

in the command prompt
regsvr32 c:\windows\system32\tlbinf32.dll

or via Start/Run
regsvr32 "c:\windows\system32\tlbinf32.dll"

note the quotes in the Run dialog

Peter T

xlConstants to numbers?
 

Tried both methods, but both failed, with an error message?


CE



Peter T wrote:
To register the dll,

in the command prompt
regsvr32 c:\windows\system32\tlbinf32.dll

or via Start/Run
regsvr32 "c:\windows\system32\tlbinf32.dll"

note the quotes in the Run dialog

Peter T

xlConstants to numbers?
 

Maybe you like XLConst on Chip's download page
http://www.cpearson.com/excel/download.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Peter T" <peter_t@discussions wrote in message ...
Is is possible to get this converted into the number of the xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's typelib


Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path & "\Excel.exe")

For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written text,
say 'xlDialogActivate'.

Is is possible to get this converted into the number of the xlConstant?

I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

ver = Left$(Application.Version, 2)
Select Case ver
Case 8: sFile = "excel8.olb"
Case 9: sFile = "excel9.olb"
Case Else: sFile = "excel.exe"

No need to test the version. You can get the typelib from the VBE
reference:

Dim TypeLibName As String
TypeLibName = ThisWorkbook.VBProject.References("EXCEL").FullPat h

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 6 Dec 2008 16:01:12 -0000, "Peter T" <peter_t@discussions
wrote:

To register the dll, in the command prompt or Start/Run

regsvr32 c:\windows\system32\tlbinf32.dll

adjust path to suit

I also forgot if you are using xl97/2000 the typelib is in the *.olb
Following should cater for all versions -

Sub dumpConstants()
''' with a ref to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object

Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long
Dim ver As Long, sFile As String

Set oTLI = CreateObject("TLI.TLIApplication")

ver = Left$(Application.Version, 2)
Select Case ver
Case 8: sFile = "excel8.olb"
Case 9: sFile = "excel9.olb"
Case Else: sFile = "excel.exe"
End Select

Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path & "\" & sFile)

For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Note there are new named constants with successive version.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
No you don't need VB6, should work fine in VBA (the code I posted was in
Excel VBA). Probably the dll needs to be installed, ie registered to
windows.

Did you simply copy it to your system32 folder, that's not enough.

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I forgot tlbinf32.dll is supplied with VB6. I assume it's freely
distributable but can't find a download link from MS, will look later.


Found it myself, but apparrently you need VB6, since VBE refuses to make
a reference to the .DLL :-(

Thanks for your effort :-)

My search goes on - anyone?


CE




Regards,
Peter T


"Charlotte E." <@ wrote in message
...
If you couldn't find the ref "TypeLib Information" browse to
"c\:windows\system32\tlbinf32.dll"

That might be the problem - I just don't have it :-(



CE




Peter T wrote:
I don't know why. Nether mind, try adding the reference I indicated
in Tools refs, comment all the As Object declarations and uncomment
the As TLI. etc
change
Set oTLI = CreateObject("TLI.TLIApplication")
to
Set oTLI = New TLI.TLIApplication

If you couldn't find the ref "TypeLib Information" browse to
c\:windows\system32\tlbinf32.dll

(might be different path for you)

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

Maybe you like XLConst on Chip's download page
http://www.cpearson.com/excel/download.htm

Well, running the macros still brings me the problem with the TypeLib
reference, but the sheets at least contains the xlContants :-)

Thanks...


CE







"Peter T" <peter_t@discussions wrote in message
...
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe") For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

I like to do this when I want a xlContant

Alt F11

Type this
?xlDialogActivate
And press Enter

You see the number now


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Charlotte E." <@ wrote in message ...
Maybe you like XLConst on Chip's download page
http://www.cpearson.com/excel/download.htm

Well, running the macros still brings me the problem with the TypeLib
reference, but the sheets at least contains the xlContants :-)

Thanks...


CE







"Peter T" <peter_t@discussions wrote in message
...
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe") For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

Oops I forgot one step

Alt F11
Ctrl g

Type this in the Immediate window
?xlDialogActivate
And press Enter

You see the number now


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Charlotte E." <@ wrote in message ...
Maybe you like XLConst on Chip's download page
http://www.cpearson.com/excel/download.htm

Well, running the macros still brings me the problem with the TypeLib
reference, but the sheets at least contains the xlContants :-)

Thanks...


CE







"Peter T" <peter_t@discussions wrote in message
...
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe") For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

Yes that's probably better. Actually a much better way to get the Office
file (for the mso-constants) which is not necessarily in the Office folder

TypeLibName = ThisWorkbook.VBProject.References("OFFICE").FullPa th

Regards,
Peter T



"Chip Pearson" wrote in message
...

ver = Left$(Application.Version, 2)
Select Case ver
Case 8: sFile = "excel8.olb"
Case 9: sFile = "excel9.olb"
Case Else: sFile = "excel.exe"

No need to test the version. You can get the typelib from the VBE
reference:

Dim TypeLibName As String
TypeLibName = ThisWorkbook.VBProject.References("EXCEL").FullPat h

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 6 Dec 2008 16:01:12 -0000, "Peter T" <peter_t@discussions
wrote:

To register the dll, in the command prompt or Start/Run

regsvr32 c:\windows\system32\tlbinf32.dll

adjust path to suit

I also forgot if you are using xl97/2000 the typelib is in the *.olb
Following should cater for all versions -

Sub dumpConstants()
''' with a ref to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object

Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long
Dim ver As Long, sFile As String

Set oTLI = CreateObject("TLI.TLIApplication")

ver = Left$(Application.Version, 2)
Select Case ver
Case 8: sFile = "excel8.olb"
Case 9: sFile = "excel9.olb"
Case Else: sFile = "excel.exe"
End Select

Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path & "\" & sFile)

For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Note there are new named constants with successive version.

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
No you don't need VB6, should work fine in VBA (the code I posted was in
Excel VBA). Probably the dll needs to be installed, ie registered to
windows.

Did you simply copy it to your system32 folder, that's not enough.

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I forgot tlbinf32.dll is supplied with VB6. I assume it's freely
distributable but can't find a download link from MS, will look later.


Found it myself, but apparrently you need VB6, since VBE refuses to
make
a reference to the .DLL :-(

Thanks for your effort :-)

My search goes on - anyone?


CE




Regards,
Peter T


"Charlotte E." <@ wrote in message
...
If you couldn't find the ref "TypeLib Information" browse to
"c\:windows\system32\tlbinf32.dll"

That might be the problem - I just don't have it :-(



CE




Peter T wrote:
I don't know why. Nether mind, try adding the reference I indicated
in Tools refs, comment all the As Object declarations and uncomment
the As TLI. etc
change
Set oTLI = CreateObject("TLI.TLIApplication")
to
Set oTLI = New TLI.TLIApplication

If you couldn't find the ref "TypeLib Information" browse to
c\:windows\system32\tlbinf32.dll

(might be different path for you)

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???



Peter T wrote:
Is is possible to get this converted into the number of the
xlConstant?
you'd need to programmatically write code to a module

But you can get one or all of the constants directly from Excel's
typelib

Sub dumpConstants()
''' with a reference to TypLib Information
'Dim oTLI As TLI.TLIApplication
'Dim oInfo As TLI.TypeLibInfo
'Dim oConst As TLI.ConstantInfo
'Dim oMembers As TLI.Members

''' without the reference use As Object
Dim oTLI As Object
Dim oInfo As Object
Dim oConst As Object
Dim oMembers As Object

Dim i As Long, c As Long

Set oTLI = CreateObject("TLI.TLIApplication")
Set oInfo = oTLI.TypeLibInfoFromFile(Application.Path &
"\Excel.exe")
For Each oConst In oInfo.Constants
i = i + 1
Cells(i, 1) = oConst.Name
Set oMembers = oConst.Members
For c = 1 To oMembers.Count
i = i + 1
Cells(i, 2) = oMembers(c).Name
Cells(i, 3) = oMembers(c).Value
Next
Next

Columns("A:C").EntireColumn.AutoFit
End Sub

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
In a cell, say "B3", I have one of the VBA xlConstants as written
text, say 'xlDialogActivate'.

Is is possible to get this converted into the number of the
xlConstant? I've tried...


MsgBox Evaluate(Range("B3").value)


But with no luck :-(


Any other way to get this done?


TIA,

CE

xlConstants to numbers?
 

Assuming you've got the right file in the right folder it's probably
something simple (though perhaps in Vista would need to be under admin with
the UAC off).

Actually in Start/Run it should have worked even without any quotes if no
spaces exist in the path. Try browsing to the dll in the Run dialog.

I should have known Chip would provided a full utility with the TLB (or
rather I knew but forgot) and sounds like you've got all the constants you
need (note the list differs between versions).

However if still unable but interested to get that reference working, either
for the simple macro I posted or for use with Chip's, post back and I'll
suggest another way to register the file.

Regards,
Peter T


"Charlotte E." <@ wrote in message
...
Tried both methods, but both failed, with an error message?


CE



Peter T wrote:
To register the dll,

in the command prompt
regsvr32 c:\windows\system32\tlbinf32.dll

or via Start/Run
regsvr32 "c:\windows\system32\tlbinf32.dll"

note the quotes in the Run dialog

Peter T