Excel VB Constants (2003 and before)

Topic

New Reply

Hi John

This isn’t the complete list ?
How did you get this list., just interested thanks ………

Reply | Quote

Replies

See my response to AlanMiller. Let me know what’s missing.

Reply | Quote

See my response to AlanMiller. Let me know what’s missing.

Reply | Quote

I can never remember how to find these in the Help, so here’s a tab-delimited text file which can be imported into Excel to serve as a reference.

(Post made in the era of Excel 2000; these are easier to find in Excel 2003 as the VB Help Reference section has an Enumerations subsection.)

Reply | Quote

I can never remember how to find these in the Help, so here’s a tab-delimited text file which can be imported into Excel to serve as a reference.

(Post made in the era of Excel 2000; these are easier to find in Excel 2003 as the VB Help Reference section has an Enumerations subsection.)

Reply | Quote

Hi John

Nice list, and I think there are many constants that simply aren’t referenced in the Help files at all. I’m presuming you extracted these from the Object Browser window. Mind sharing the method you used to achieve this?

Alan

Reply | Quote

From the VBE Help. AFAIK it’s the complete list of Excel specific constants, it doesn’t include the Office-wide Constants, and it doesn’t include things such as the Dialogs (I should probably add those – I already have that list). It has always been an annoyance to me that you can’t click on a Constant and tree up to the Parent Methods or Properties.

Reply | Quote

Can you tell us how you found them in VBE Help? I can never find the constants in help (in XL2K).

Reply | Quote

In Excel 2002, you can use the Object Browser to view those constants: press F2 to activate the Object Browser, then type xl into the search box and press Enter.

Reply | Quote

But the question is still, “What’s the extraction method from the object browser?” Or maybe John had to revert to the manual “read then type” technique… that’s so labour-intensive I’ve forgotten how it’s done now.

Alan

Reply | Quote

But the question is still, “What’s the extraction method from the object browser?” Or maybe John had to revert to the manual “read then type” technique… that’s so labour-intensive I’ve forgotten how it’s done now.

Alan

Reply | Quote

In Excel 2002, you can use the Object Browser to view those constants: press F2 to activate the Object Browser, then type xl into the search box and press Enter.

Reply | Quote

Guys, I’m learning as I go here.

In 2003, here’s how I got the list: In the VBE help ‘Search Help’ window I entered “excel intrinsic constants”, and the top item listed was “Microsoft Excel Constants”, which is exactly what I originally posted. (Searching for “Microsoft Excel Constants” ranks the same topic about 11th down the topic list!). I checked the Properties for the list and it is not, as I thought, on MSDN, it’s part of my local Help file as (the full Properties listing) “mk:@MSITStore:C:Program FilesMicrosoft OfficeOFFICE111033vbaxl10.chm::/html/xlhowConstants.htm#”

The list is also at:

MSDN 2003 xlConstants

And you can also obtain it in the file ‘vbaxl10.chm’ which can be downloaded from

Excel VBA Language Reference

The fact that it’s so exasperating to find these is the reason for my post.

Reply | Quote

Thanks. I was hoping to find a way to find them in Help that I could use when in the VBE. They are in the object browser, but when you hilite them and press F1 you don’t get any help.

Reply | Quote

Legare, I did a little more research, see my now edited post 413257 on how I found the list in the 2003 VBE help.

Reply | Quote

Legare, I did a little more research, see my now edited post 413257 on how I found the list in the 2003 VBE help.

Reply | Quote

Thanks. I was hoping to find a way to find them in Help that I could use when in the VBE. They are in the object browser, but when you hilite them and press F1 you don’t get any help.

Reply | Quote

Hi John, thanks for those useful links.

OK, I got 167 diff between yours and mine.
An interesting one that wasn’t on my list but on yours was xlXMLData = 47
Doing a debug print in the imediate Window yielded nothing ?

I believe ?? the diff are that these Const are Microsoft Excel 5.0, 7.0 const and that your ones
are from XL97 onwards??

Any way here is my list.

Thanks again for your links.

Reply | Quote

You are probably right, and it’s also possible that the Excel Help for these items is incorrect, that would not be uncommon for Microsoft (especially whan they incorrectly include Mac items in Windows version help). Do you have any concern if I incorporate your list into my text list and retain the Object headings?

Reply | Quote

Absolutely …. go for it……

and thanks for your list…..it highlighted the fact that they didn’t include the prior versions (<Xl97) Const . which means
they probably won't support ??/ backwards compatibilty for these version. But this is just a guess.

Thanks again John

Reply | Quote

Ivan, due to both my lack of time and lack of depth in this stuff, I may need some help; see my PM to you.

Reply | Quote

Ivan, due to both my lack of time and lack of depth in this stuff, I may need some help; see my PM to you.

Reply | Quote

Absolutely …. go for it……

and thanks for your list…..it highlighted the fact that they didn’t include the prior versions (<Xl97) Const . which means
they probably won't support ??/ backwards compatibilty for these version. But this is just a guess.

Thanks again John

Reply | Quote

You are probably right, and it’s also possible that the Excel Help for these items is incorrect, that would not be uncommon for Microsoft (especially whan they incorrectly include Mac items in Windows version help). Do you have any concern if I incorporate your list into my text list and retain the Object headings?

Reply | Quote

Hi John, thanks for those useful links.

OK, I got 167 diff between yours and mine.
An interesting one that wasn’t on my list but on yours was xlXMLData = 47
Doing a debug print in the imediate Window yielded nothing ?

I believe ?? the diff are that these Const are Microsoft Excel 5.0, 7.0 const and that your ones
are from XL97 onwards??

Any way here is my list.

Thanks again for your links.

Reply | Quote

Well, right off the bat I guess I am in deep do in XL2K. I don’t have the choice of the “Search Help” window, I can only get tht “Office Assistant.” Typing “excel intrinsic constants” (intuitively obvious search argument that) gets me a few things, none of which have any resemblance to excel intrinsic constants.

Reply | Quote

Well, right off the bat I guess I am in deep do in XL2K. I don’t have the choice of the “Search Help” window, I can only get tht “Office Assistant.” Typing “excel intrinsic constants” (intuitively obvious search argument that) gets me a few things, none of which have any resemblance to excel intrinsic constants.

Reply | Quote

Guys, I’m learning as I go here.

In 2003, here’s how I got the list: In the VBE help ‘Search Help’ window I entered “excel intrinsic constants”, and the top item listed was “Microsoft Excel Constants”, which is exactly what I originally posted. (Searching for “Microsoft Excel Constants” ranks the same topic about 11th down the topic list!). I checked the Properties for the list and it is not, as I thought, on MSDN, it’s part of my local Help file as (the full Properties listing) “mk:@MSITStore:C:Program FilesMicrosoft OfficeOFFICE111033vbaxl10.chm::/html/xlhowConstants.htm#”

The list is also at:

MSDN 2003 xlConstants

And you can also obtain it in the file ‘vbaxl10.chm’ which can be downloaded from

Excel VBA Language Reference

The fact that it’s so exasperating to find these is the reason for my post.

Reply | Quote

Can you tell us how you found them in VBE Help? I can never find the constants in help (in XL2K).

Reply | Quote

From the VBE Help. AFAIK it’s the complete list of Excel specific constants, it doesn’t include the Office-wide Constants, and it doesn’t include things such as the Dialogs (I should probably add those – I already have that list). It has always been an annoyance to me that you can’t click on a Constant and tree up to the Parent Methods or Properties.

Reply | Quote

Hi John

Nice list, and I think there are many constants that simply aren’t referenced in the Help files at all. I’m presuming you extracted these from the Object Browser window. Mind sharing the method you used to achieve this?

Alan

Reply | Quote

Using this kb article , I created this little utility.

Reply | Quote

Thanks, that’s neat!

Reply | Quote

Thanks, that’s neat!

Reply | Quote

Is ‘tlbinf.dll’ only supplied with one of Visual Studio 6.0, Visual Basic Professional Edition for Windows 6.0, or Visual Basic Enterprise Edition for Windows 6.0? I don’t find it on my XPOS / Office 03 installation.

Except for my question about the TypeLib dll that is an EXTREMELY neat utility, and I think perhaps you should repost it in GOS and we’ll nominate it for a star.

My only regret is that it doesn’t appear to include a list of the parent Properties (or does it?), which is what is so frustrating about the structure of the Help files. is there a way to add a Help file into an existing MS Help file system?

Reply | Quote

I was going to post the error I got when I ran it but I see you beat me to it. I get a report of a missing reference for TLBINF32.DLL. I’m running Office 2002 and Win2k. The code points to a declaration of “x as TypeLibInfo” in Private Sub GetAppConstants (Module1).

WHen I first ran it, it stopped at a call to Left() and when I OK’d that it then stopped at the above variable.

I did a search and didn’t see this DLL (or similar one) on my computer.

Deb

Reply | Quote

ON my system it is in the Windows/System32 folder.

You can get help files about this dll here
And from this page :

TlbInf32.chm provides documentation for the redistributable TypeLib Information object library (TlbInf32.dll), which ships with Visual Studio 6.0. Place this file in the system directory with TlbInf32.dll to enable context-sensitive Help.

Get the tlb file here

Reply | Quote

ON my system it is in the Windows/System32 folder.

You can get help files about this dll here
And from this page :

TlbInf32.chm provides documentation for the redistributable TypeLib Information object library (TlbInf32.dll), which ships with Visual Studio 6.0. Place this file in the system directory with TlbInf32.dll to enable context-sensitive Help.

Get the tlb file here

Reply | Quote

I was going to post the error I got when I ran it but I see you beat me to it. I get a report of a missing reference for TLBINF32.DLL. I’m running Office 2002 and Win2k. The code points to a declaration of “x as TypeLibInfo” in Private Sub GetAppConstants (Module1).

WHen I first ran it, it stopped at a call to Left() and when I OK’d that it then stopped at the above variable.

I did a search and didn’t see this DLL (or similar one) on my computer.

Deb

Reply | Quote

Hi John
Here is a cut down version of how I got the Constants. I just changed one variable to get what you want. ie the Parent Property as well as the constants
I was meaning to post this (Well NOT this version as this is a smaller one) to my site a while ago. I just never got around to it.
HTH

Reply | Quote

Is ‘tlbinf.dll’ only supplied with one of Visual Studio 6.0, Visual Basic Professional Edition for Windows 6.0, or Visual Basic Enterprise Edition for Windows 6.0? I don’t find it on my XPOS / Office 03 installation.

Except for my question about the TypeLib dll that is an EXTREMELY neat utility, and I think perhaps you should repost it in GOS and we’ll nominate it for a star.

My only regret is that it doesn’t appear to include a list of the parent Properties (or does it?), which is what is so frustrating about the structure of the Help files. is there a way to add a Help file into an existing MS Help file system?

Reply | Quote

Can I ask what the purpose of having this list is?

The whole point of having constants and enumerators is that you can reference them by name and not have to worry about the underlying value they represent.

In any case, if you really want to know the value just hit F2 and find the constant/enumerator in the object browser, but I still can’t see what the benefit is of knowing.

Reply | Quote

>In any case, if you really want to know the value just hit F2 and find the constant/enumerator in the object browser, but I still can’t see what the benefit is of knowing.

I’m always looking for a better way – maybe I need help with using the Object Browser. In the Object Browser, how many steps does it take you to find the Intrinsic Constants for the Type Property of .Validation?

My purposes in posting the list had to do with my experience of how inconvenient the Object Browser key is. F2 doesn’t jump to the selected Property, you have to search the Browser, walk the tree, and right click to Copy. Sometimes I like to copy all the Constants into my code as comments for future reference, and that’s a pain with the OB. Also:

1. Intellisense doesn’t always suggest the Constants. Why not? (.Validation.Type = …, again)
2. The Help is not always complete, depending on the Excel version. In 2003 VBE Help does not list the Constants for .Validation.Type (yet again).
3. The recorder sometimes produces the numeric value rather than the constant name, and the constant name is much more helpful in self documenting code (your point); the list provides a crosswalk.
4. Once in a while I run into a Constant and want to know it’s Parent; the list provides a crosswalk.

Reply | Quote

OK, I can understand why it makes things more convenient.

I previously thought that the main purpose was to list all the numeric values and I couldn’t see a reason for that, but point 3 shows why that may be useful.

I was under the impression that Intellisense never suggests the enumerators for right hand side of a property assignment. Do you have an example of it doing so?

The reason that it sometimes suggests them for method arguments and sometimes doesn’t is that sometimes arguments are declared as variants even though a particular enumerator member is expected. In these instances there is no way for Intellisense to know which enumerator to suggest.

Reply | Quote

>Intellisense never suggests the enumerators

Thanks for the reminder on Variants, but many of them aren’t and still don’t get suggested.

Those with True/False arguments immediately come to mind, such as Application.ScreenUpdating = …,
Also Application.DefaultSaveFormat = … suggests the constants, but
ActiveSheet.Selection.Font.Underline = … doesn’t seem to

Poke around and try some more.

Reply | Quote

>Intellisense never suggests the enumerators

Thanks for the reminder on Variants, but many of them aren’t and still don’t get suggested.

Those with True/False arguments immediately come to mind, such as Application.ScreenUpdating = …,
Also Application.DefaultSaveFormat = … suggests the constants, but
ActiveSheet.Selection.Font.Underline = … doesn’t seem to

Poke around and try some more.

Reply | Quote

OK, I can understand why it makes things more convenient.

I previously thought that the main purpose was to list all the numeric values and I couldn’t see a reason for that, but point 3 shows why that may be useful.

I was under the impression that Intellisense never suggests the enumerators for right hand side of a property assignment. Do you have an example of it doing so?

The reason that it sometimes suggests them for method arguments and sometimes doesn’t is that sometimes arguments are declared as variants even though a particular enumerator member is expected. In these instances there is no way for Intellisense to know which enumerator to suggest.

Reply | Quote

>In any case, if you really want to know the value just hit F2 and find the constant/enumerator in the object browser, but I still can’t see what the benefit is of knowing.

I’m always looking for a better way – maybe I need help with using the Object Browser. In the Object Browser, how many steps does it take you to find the Intrinsic Constants for the Type Property of .Validation?

My purposes in posting the list had to do with my experience of how inconvenient the Object Browser key is. F2 doesn’t jump to the selected Property, you have to search the Browser, walk the tree, and right click to Copy. Sometimes I like to copy all the Constants into my code as comments for future reference, and that’s a pain with the OB. Also:

1. Intellisense doesn’t always suggest the Constants. Why not? (.Validation.Type = …, again)
2. The Help is not always complete, depending on the Excel version. In 2003 VBE Help does not list the Constants for .Validation.Type (yet again).
3. The recorder sometimes produces the numeric value rather than the constant name, and the constant name is much more helpful in self documenting code (your point); the list provides a crosswalk.
4. Once in a while I run into a Constant and want to know it’s Parent; the list provides a crosswalk.

Reply | Quote

Can I ask what the purpose of having this list is?

The whole point of having constants and enumerators is that you can reference them by name and not have to worry about the underlying value they represent.

In any case, if you really want to know the value just hit F2 and find the constant/enumerator in the object browser, but I still can’t see what the benefit is of knowing.

Reply | Quote