?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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
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?
 

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
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:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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.
 

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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.
 

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
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:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,640
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top