Table column in VBA
 

Hi, all,

I am writing some VBA code and want to control an Excel 2010
table-object and a column or headers to a column from it with set
abstraction names rather than just using the real name. I'm having
trouble getting it to work.

So I have a table called "Table2". I have a column called "Key" as
the left column of that.

Dim tblTgt As Range

Set tblTgt = [table2] 'workbook scope

Well, that actually works. But then I can't, later,
select a column such as "Key" or a headder row or cell.
It won't work:

Range(TblTgt[[#Headers],["Key"]]).Select
Range(TblTgt[[#Headers],[Key]]).Select
TblTgt[[#Headers],[Key]].Select
TblTgt[[#Headers],["Key"]].Select

and various other variations on that kind of thing won't
work. I have to go back to:

Range("Table2[[#Headers],[Key]]").Select

That works, but it defeats the point of the abstraction
in the code.

Ideas?

Table column in VBA
 

hi,

the first column address:
firtColumn = Range("Table2").Columns(1).Address

the last column address:
lastColumn = Range("Table2").Columns(Range("Table1").Columns.Co unt).Address

isabelle

Le 2015-04-14 19:19, Sarah H. a écrit :
Hi, all,

I am writing some VBA code and want to control an Excel 2010
table-object and a column or headers to a column from it with set
abstraction names rather than just using the real name. I'm having
trouble getting it to work.

So I have a table called "Table2". I have a column called "Key" as
the left column of that.

Dim tblTgt As Range

Set tblTgt = [table2] 'workbook scope

Well, that actually works. But then I can't, later,
select a column such as "Key" or a headder row or cell.
It won't work:

Range(TblTgt[[#Headers],["Key"]]).Select
Range(TblTgt[[#Headers],[Key]]).Select
TblTgt[[#Headers],[Key]].Select
TblTgt[[#Headers],["Key"]].Select

and various other variations on that kind of thing won't
work. I have to go back to:

Range("Table2[[#Headers],[Key]]").Select

That works, but it defeats the point of the abstraction
in the code.

Ideas?

Table column in VBA
 

oops, correction:

the last column address:
lastColumn = Range("Table2").Columns(Range("Table2").Columns.Co unt).Address

isabelle

Table column in VBA
 

anotherway,

x = Application.Match("Key", [table2].Rows(1), 0)
rangeKey = Range("Table2").Columns(x).Address

isabelle

Le 2015-04-14 23:15, isabelle a écrit :
oops, correction:

the last column address:
lastColumn = Range("Table2").Columns(Range("Table2").Columns.Co unt).Address

isabelle

Table column in VBA
 

isabelle wrote:
anotherway,

x = Application.Match("Key", [table2].Rows(1), 0)
rangeKey = Range("Table2").Columns(x).Address

isabelle

Le 2015-04-14 23:15, isabelle a écrit :
oops, correction:

the last column address:
lastColumn = Range("Table2").Columns(Range("Table2").Columns.Co unt).Address

Interesting ideas. But I wasn't clear about what I am trying to do.

Basically, I have a long set of VBA procedures. I want to set all
the real names to variable names near the top, so if I change my sheets
later I won't have to search all through all the code to find individual
instances of, e.g., "Table2". What If I rename Table2 next year and
call it "MyUpdatedTable"? Then, on lines 435 and 654 of my procedure(s)
somewhere it says "Table2", and the code won't work.

So at the top I wanted

set foo = Range("Table2")

If I change my tables later, I know to just go to the definitions
area of my macros and change the set variables there.

This seems to me to be careful and correct programming technique
for me and my possible successors who won't want to look at
spaghetti code to figure out what I was doing in Spring 2015.

But I can't figure out how to do it for individual columns in a table.

Well, let me amend that. I can do it, but only if I set each column
manually. I don't want that. I want the same flexibility to find
subranges using the abstraction name "foo" that I have with the
actual (current) name "Table2".

Isabelle, I appreciate your kind efforts of help.

May I mention, as an aside, that "v.org" is an actual domain? If
you are not connected with it, I would suggest you use a
legitimate fake address if you want to munge your address in
the group. That's why I choose "example.com": it was created
and reserved for such purposes and is sanctioned for that.
http://en.wikipedia.org/wiki/Example.com

If you do actually work at v.org, then I apologize. :-)

--
/s

Table column in VBA
 

Sarah H. wrote:
isabelle wrote:
anotherway,

x = Application.Match("Key", [table2].Rows(1), 0)
rangeKey = Range("Table2").Columns(x).Address

isabelle

Le 2015-04-14 23:15, isabelle a écrit :
oops, correction:

the last column address:
lastColumn = Range("Table2").Columns(Range("Table2").Columns.Co unt).Address

Interesting ideas. But I wasn't clear about what I am trying to do.

Isabelle, you were closer to right than I gave you credit for. Apologies!

This works (tested in the "Immediate" window):

set foo=[table2]
x=Application.Match("Key", foo.Rows(0), 0)
debug.Print x
1
foo.columns(x).select

So that's pretty much what I want to be able to do. Thanks!
The trick is, Row(0) is the table header. I wonder if this
is documented anywhere.

--
/s

Table column in VBA
 

Also very good, though I haven't yet been able to use it to solve
my exact need:

http://www.thespreadsheetguru.com/bl...t-excel-tables

It has other good refs listed near the bottom, too.

--
/s

Table column in VBA
 

thank you! Sarah, and thank you very much! to have told to me about the "domain"
I did not know about that, so, as you see I have changed my address.

isabelle

Le 2015-04-18 09:31, Sarah H. a écrit :
Also very good, though I haven't yet been able to use it to solve
my exact need:

http://www.thespreadsheetguru.com/bl...t-excel-tables

It has other good refs listed near the bottom, too.

Table column in VBA
 

Also very good, though I haven't yet been able to use it to solve
my exact need:

http://www.thespreadsheetguru.com/bl...t-excel-tables

It has other good refs listed near the bottom, too.

Excellent resource! Thanks for posting this link...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Table column in VBA
 

Thanks you! for this link

anthoner good one:

http://www.jkp-ads.com/articles/Excel2007Tables.asp

isabelle

Le 2015-04-18 09:31, Sarah H. a écrit :
Also very good, though I haven't yet been able to use it to solve
my exact need:

http://www.thespreadsheetguru.com/bl...t-excel-tables

It has other good refs listed near the bottom, too.

Table column in VBA
 

isabelle wrote:
Thanks you! for this link

anthoner good one:

http://www.jkp-ads.com/articles/Excel2007Tables.asp

Yup, it was shown as a recommended link at the bottom of the
one I gave. Very good stuff! Its author is also involved with
the great Name Manager plug-in, which I have used for years.

Oh, cool, I see there's an update as of February 2015:
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp

isabelle

Le 2015-04-18 09:31, Sarah H. a écrit :
Also very good, though I haven't yet been able to use it to solve
my exact need:

http://www.thespreadsheetguru.com/bl...t-excel-tables

It has other good refs listed near the bottom, too.

--
/s

Table column in VBA
 

Thanks for the good word, Garry.

By the way, same comment to you on the domain and address-munging:
"somewhere.net" is a real domain. From the whois data, I am left
to doubt that you have any connection to it. May I humbly suggest
using one of the IANA reserved names instead?

Not actually trying to be a curmudgeon...

Cheers,
Sarah


GS wrote:
Also very good, though I haven't yet been able to use it to solve
my exact need:

http://www.thespreadsheetguru.com/bl...t-excel-tables

It has other good refs listed near the bottom, too.

Excellent resource! Thanks for posting this link...

Table column in VBA
 

Thanks for the good word, Garry.

By the way, same comment to you on the domain and address-munging:
"somewhere.net" is a real domain. From the whois data, I am left
to doubt that you have any connection to it. May I humbly suggest
using one of the IANA reserved names instead?

Not actually trying to be a curmudgeon...

Cheers,
Sarah

Thanks, Sarah! Like Isabelle.., I didn't realize the domain name had
been used since I started using it. (Non-existent way back then!) I'll
change to Isabelle's new domain...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Table column in VBA
 

Le 2015-04-18 20:30, GS a écrit :
I'll change to Isabelle's new domain...

wow! I'll to fart the response score on the net ;-)
isabelle

Table column in VBA
 

Le 2015-04-18 20:30, GS a écrit :
I'll change to Isabelle's new domain...

wow! I'll to fart the response score on the net ;-)
isabelle

He he!
Actually, as an afterthought, I could use my old email address
@netscape.net since it's now defunct!

Also, there is no valid email address " and so no
reason to not continue using it!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Table column in VBA
 

GS wrote:
Also, there is no valid email address " and so no
reason to not continue using it!

The reason is, it's someone's domain. You would be directing
mail improperly to the domain. (The fact that the domain is
parked and for sale is orthogonal to the point.)

Someone could start using that email address legitimately.

Also, the domain was first registered as follows, per whois:
Creation Date: 1998-05-26

There's no Sara H. at your street address, either, but I doubt
you want UPS ringing your bell with packages for me! :-)

--
/s

Table column in VBA
 

GS wrote:
Also, there is no valid email address " and so no
reason to not continue using it!

The reason is, it's someone's domain. You would be directing
mail improperly to the domain. (The fact that the domain is
parked and for sale is orthogonal to the point.)

Someone could start using that email address legitimately.

Also, the domain was first registered as follows, per whois:
Creation Date: 1998-05-26

These are all valid points! No contest on that from me.., I was just
saying that since it was also defunct...

There's no Sara H. at your street address, either, but I doubt
you want UPS ringing your bell with packages for me! :-)

Well.., mail to non-existing email addresses *never* get delivered and
so your last point is mute even though I do 'get it' in concept.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion