xlConstants to numbers?

C

Charlotte E.

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
 
P

Peter T

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
 
C

Charlotte E.

I get a runtime error # 429 in this line:

Set oTLI = CreateObject("TLI.TLIApplication")

???
 
P

Peter T

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")
Click to expand...
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
 
C

Charlotte E.

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

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



CE
 
O

Otto Moehrbach

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
 
P

Peter T

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
 
C

Charlotte E.

Otto said:
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
Click to expand...


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
 
C

Charlotte E.

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.
Click to expand...


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
 
P

Peter T

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
 
P

Peter T

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
 
P

Peter T

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
 
C

Chip Pearson

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

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

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

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

Ron de Bruin

I like to do this when I want a xlContant

Alt F11

Type this
?xlDialogActivate
And press Enter

You see the number now
 
R

Ron de Bruin

Oops I forgot one step

Alt F11
Ctrl g

Type this in the Immediate window
?xlDialogActivate
And press Enter

You see the number now
 
P

Peter T

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").FullPath

Regards,
Peter T
 
P

Peter T

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
 
Top