where to find all excel constants?

[Ryan H.]

Hi,

I'm wondering where I can find all the excel contants listed and their
explanation? For example, I learned a couple of days ago og the xlBlanks
constant, and how to use it to find blank cells. What if I want to find out
if there is such a constant for finding filled up cells. Is there a webpage
out there or a resource I can look up to find these constants?

Thanks

 

[Tom Ogilvy]

xlBlanks is a constant for pivot tables according to the object browser.
The constant you are actually using is xlCellTypeBlanks as an argument to
specialcells. xlBlanks is actually the pre-excel 97 constant used with
specialcells, so MS has preserved the value so they both evaluate to 4.

So you see documentation might not be a definitive as you would expect.

In later versions of excel I believe there is a list of constants in the
Excel VBA help file.

 

[Michel Pierron]

Hi Ryan,
If you have TLBInf32.dll:
Sub ExcelConstantsList()
Application.ScreenUpdating = False
Dim R, Member, i As Long, oPath As String
oPath = Application.Path & "\excel.exe"
With CreateObject("TLI.typelibinfo")
..ContainingFile = oPath
Workbooks.Add
For Each R In .Constants
i = i + 1
Cells(i, 1) = R.Name: Cells(i, 1).Font.Bold = True
For Each Member In R.Members
i = i + 1
Cells(i, 1) = Member.Name
Cells(i, 2) = Member.Value
Next Member
Next R
End With
Columns("A:B").Columns.AutoFit
End Sub

Or for one constant:
Sub xlConstantValue()
Application.Run "'iValue " & InputBox("Enter the constant name :" _
, "Excel constants", "xlBlanks") & "'"
End Sub

Sub iValue(I$)
MsgBox I, 64
End Sub

MP

 

[Michel Pierron]

Ryan, the constants are indexed in the .olb files to the version xl2000.
Using TLBInf32.dll and according to the version of Excel:
Sub ExcelConstantsList()
Dim Version As String
Select Case Val(Application.Version)
Case Is >= 10: Version = "excel.exe"
Case Else: Version = "excel" & Val(Application.Version) & ".olb"
End Select
Application.ScreenUpdating = False
Dim R, Member, i As Long
With CreateObject("TLI.typelibinfo")
..ContainingFile = Application.Path & "\" & Version
'...

Regards,
MP

 

[Keith Willshaw]

Hi,

I'm wondering where I can find all the excel contants listed and their
explanation? For example, I learned a couple of days ago og the xlBlanks
constant, and how to use it to find blank cells. What if I want to find out
if there is such a constant for finding filled up cells. Is there a webpage
out there or a resource I can look up to find these constants?

Thanks

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlhowConstants.asp

Keith