| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to reconcile numbers accounting Harlan Grove code doesn't work for negative numbers | Excel Programming | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
| Identifying cell types in column using .SpecialCells(xlConstants, xlNumbers) | Excel Programming | |||
![Charlotte E.[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
