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

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
I am using the Immediate Window to Read values of Properties.

eg:
Code:
[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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I don't believe that you can, it is a pointer to a value, it is not two-way. There could be (are) a number of VBA constants with the same value, so how would it know which to return?
 
Upvote 0
Hi. Thanks for replying and offering help.

However, I don't really follow what you are saying here...
...it is a pointer to a value, it is not two-way...
It sounds like you think I want to write to the property. I don't. I want to read it but have it expressed as the text version of the constant so I can read it at a glance without having to check help file to see which index is which text value.

There could be (are) a number of VBA constants with the same value, so how would it know which to return?

Re "how would it know", it must know because it's just told me (the numeric value of the property), it just hasn't translated it into the user friendly text version (which it also knows, because the user can use either version in code,
for writable properties, and it has to translate for itself).


I hoped there might be an Additional Property
eg:
Code:
[FONT=Courier New]Debug.Print "CommandType: " &  ActiveSheet.QueryTable(1).CommandType[COLOR=Red] '.Text OR .Name  etc[/COLOR][/FONT]

Or a way of Formatting the Value returned
eg:
Code:
[FONT=Courier New]Debug.Print  "CommandType: " & ActiveSheet.QueryTable(1).CommandType[COLOR=Red] '.NumberFormat[/COLOR][/FONT]


Thanks for your assistance though!

 
Last edited:
Upvote 0
I think if you take a look at the Object Browser you might get an idea of what xld means.

Just a quick browse and I found that the Constant xlAdd= the Constant xlBar = the Constant xlChart4 = the Constant xlClassic2 ... (Got bored when I reached the Cs).:)

All of these constants have the integer value 2.
 
Upvote 0
Hi Norrie, thanks for your reply.

I am aware that loads of constants share the same numerical value (eg "2" in your example).

I understand that if I were asking VBA to work out which constant I am talking about by just feeding it the value "2" that that would be impossible for it.

But I don't think I am. See my code above (in the original post).

I am explicitly stating the Object and Property that I am seeking the Value of:
Code:
[FONT=Courier New]ActiveSheet.QueryTable(1).CommandType[/FONT]
It returns the Numerical Value of the Constant, but surely it is aware of the String / Text version of that specific constant and constant value. (Since either version can be used when coding write operations, VBA needs to be able to interpret whichever version is used, and/or translate from one to the other).

I'm NOT trying to write to the property, but if I were, I could use either of the following couldn't I?

Code:
[FONT=Courier New]ActiveSheet.QueryTable(1).CommandType = 2[/FONT]
Code:
[FONT=Courier New]ActiveSheet.QueryTable(1).CommandType = xlCmdSql[/FONT]
I just want it to deliver the text version rather than the numerical version.

Thanks.
 
Upvote 0
Why do you want the text of the constant?

I really don't see a need for that, or how it could be used later in the code.:)
 
Upvote 0
In this instance, it's not critical. It will not be used later in code. If I were using the returned value in code then I think I would happy to let VBA return either the Numeric or Text Constant to itself, whichever it prefers.

But I would have thought that in the development phase, knowing the Text Version of a Property Value would be required very regularly. I've certainly come across it a few times. Does everyone else just look up the help files or object browser?

In my situation, I'm experimenting with using Queries. Unfortunately, because I'm learning how to do it by trial and error I've got some inconsistencies between queries. I am using the Immediate Window to compare properties of all queries in the workbook. I also came across an old query that I'd forgotten to delete! It's a shame the user interface does not highlight them more obviously, as it does with Named Ranges.

Full Code of Macro:
Code:
[FONT=Courier New]Dim qtQueryTable As QueryTable
Dim wsWorksheet As Worksheet

For Each wsWorksheet In ThisWorkbook.Worksheets
    
    For Each qtQueryTable In wsWorksheet.QueryTables
        
        Debug.Print "Parent.Name:          " & vbTab & qtQueryTable.Parent.Name
        Debug.Print "TypeName:             " & vbTab & TypeName(qtQueryTable)
        Debug.Print "Name:                 " & vbTab & qtQueryTable.Name
        Debug.Print "Connection:           " & vbTab & qtQueryTable.Connection
        Debug.Print "CommandType:          " & vbTab & qtQueryTable.CommandType
        Debug.Print "QueryType:            " & vbTab & qtQueryTable.QueryType
        Debug.Print "ResultRange.Address:  " & vbTab & qtQueryTable.ResultRange.Address
        Debug.Print "RobustConnect:        " & vbTab & qtQueryTable.RobustConnect
        Debug.Print "SourceConnectionFile  " & vbTab & qtQueryTable.SourceConnectionFile
        Debug.Print "SourceDataFile:       " & vbTab & qtQueryTable.SourceDataFile
        Debug.Print "CommandText:          " & vbCrLf & qtQueryTable.CommandText
        Debug.Print vbCrLf
        
    Next qtQueryTable

Next wsWorksheet

Set qtQueryTable = Nothing
Set wsWorksheet = Nothing

End Sub[/FONT]
The question is also linked to a question I posted yesterday that I'm trying to resolve re updating links to the Path and Filename of DataSources if/when they change.

http://www.mrexcel.com/forum/showthread.php?t=459189

I've found some assistance here:

http://www.mrexcel.com/forum/showthread.php?t=384888
http://www.mrexcel.com/forum/showthread.php?t=295837
http://www.mrexcel.com/forum/showthread.php?t=352309
http://www.mrexcel.com/forum/showthread.php?t=315657

and here

http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/moving_database.html
 
Upvote 0
I have honestly never come across a question like this.:)

It's also seems a bit strange that you say you want to do this but then you aren't going to use it in subsequent code.

I think it might be an interesting topic to look into, probably involving enumeration, perhaps a collection/array or two etc.
 
Upvote 0
Hi again Norie.

Re: "unusual question":
It's not the first Ive come up with!
eg see my post on capturing window-scrolling
http://www.mrexcel.com/forum/showthread.php?t=424533

Re: Strangeness of doing this but not using it in code.
I really don't think so.
I'm just trying to find out what parameters / properties the query has set (whether deliberately or accidentally by me, or automatically by Excel, VBA, and/or MS Query).
I'm doing that in order to ensure consistency, and also to look at what tweaking options there are, to help me problem solve now, and to know additional options for the future.

Thanks, by the way, for your input in general on this forum. I used a couple of your "howto" style posts to help with problems I had in the past. (?Maybe AdvancedFilter or Bubble Sort. I can't remember right now.)

Cheers

PS: I meant to mention that, actually, the Object Browser is the only place that you get both the Numeric Constant and the Text Constant together. The Help Files only have text. I rarely use the Object Browser, but should get to know it.
 
Last edited:
Upvote 0
You've honestly lost me - I can see no advantage of getting/knowing the text for the constants.

The important thing about constants are their actual value - that's what you would use in code.

There - might be a way to get their text names, but I'm pretty sure it would involve a whole lot of code.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top