?How To: Return Built-In Constants in Text / String form (not Numeric / Index form)

[Default300]

I am using the Immediate Window to Read values of Properties.

eg:

[FONT=Courier New]Debug.Print "[/FONT][FONT=Courier New]CommandType[/FONT][FONT=Courier New]: "  & ActiveSheet.QueryTable(1).[/FONT][FONT=Courier New]CommandType[/FONT]

Some Property Values are Built-In Constants...
(eg XlCmdType)

PROBLEM:
The problem is that in the immediate window, they are returned in the Numeric / Index Form...
(eg: 0, 1, 2, 3 etc)

QUESTION:
How can I get it to return the Text / String Form?
(eg: xlCmdCube, xlCmdDefault, xlCmdSql, xlCmdTable)

TIA

 

[Default300]

I don't know how to express it more clearly. I'll just end up repeating myself (again!) and frustrating both of us.

I mean, I keep saying that I don't want to use it in code, at least definitely not directly or immediately.

I'm just listing the values of parameters to help troubleshoot. The same way that I check the Properties Window to compare 2 UserForm Controls for consistency after I've fiddled with one and can't work out what is different.

If I ask VBA / the Immediate Window what's the value of parameter / property CommandType, and it tells me it's "2", then that's pretty useless to me as I don't know which of the ?4/?5 Text Constants is "2", so I don't know what CommandType is and therefore cannot rule that property in or out when troubleshooting.

Before you mentioned the Object Browser, I would have relied on the help files for my information. But if I tried to guess which Text Value matched which Numeric Value, I would have assumed that it was either alphabetical, or in the order listed.

With this constant, I would have been very wrong though:
1 = xlCmdCube
4 = xlCmdDefault
5 = xlCmdList
2 = xlCmdSql
3 = xlCmdTable

Incidentally, the Help File doesn't even mention option 5 / xlCmdList !!

Aaagh!!

 

[Norie]

If all you want is a list of the constants and their values why didn't you say so.

I know it's probably not available in VBA Help but I also know it is if you check out the Object Model for Excel at MSDN.

I did that earlier, exported the data/constants/values to Excel.

I manipulated the data a bit and was going to post it, or a link to it, but there were 2000+ constants - oh and my dinner was about to burn.

 

[Default300]

That is not what I want.

It's what I'll settle for, now that I realise that what I want, I won't get.

I want to ask the Immediate Window what the Property Value is and get an intelligible reply. Not "2". And not "2, and go look it up on the Web".

Sorry, my nerves are getting a little frayed!! Thought that it would be a quick question with a simple solution. Also thought it would have been something so many people would need that the solution would be straightforward, or at least a well trodden workaround path. Thanks for your time and input, and your persistence in trying to understand what I want when really you have nothing to gain from this except the reward of knowing you are helping and the thanks of those you assist.

Incidentally, I just looked up Yaren. Had never heard of it. 20 kmsq island. Love the scale of the National Airport on Wikipedia. Cheers.