RAnge as Table
 

Hi,
This question has probably been answered before.
I'm trying to use a range as a table, so that I can refer to columns be
their name

for example:
Set myrange = sheets("orders").Range("A10:N100")
For Each row in my range
myrange.ordertotal = myrange.subtotal + myrange.freight
Next

Wouldn't it be cool to say
myrange.hasheader = true (first row of the range is the column name)
Or even better
myrange.columnnames = array("col1", "ColName"...) or
myrange.columnNames = Range("A1:N1")

This will give me a lot of flexibility - I can move the range to another
location with little effect on the code.
Also makes the code a little more self-documenting.

Thanks in advance
Habib




--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
--------------------------------------------

RAnge as Table
 

if the workbook is closed, then you can reference your range using ADO and
treat it as a table thus utilizing SQL to manipulate/reference the data
therein.

--
Regards,
Tom Ogilvy


"HSalim[MVP]" wrote in message
...
Hi,
This question has probably been answered before.
I'm trying to use a range as a table, so that I can refer to columns be
their name

for example:
Set myrange = sheets("orders").Range("A10:N100")
For Each row in my range
myrange.ordertotal = myrange.subtotal + myrange.freight
Next

Wouldn't it be cool to say
myrange.hasheader = true (first row of the range is the column name)
Or even better
myrange.columnnames = array("col1", "ColName"...) or
myrange.columnNames = Range("A1:N1")

This will give me a lot of flexibility - I can move the range to another
location with little effect on the code.
Also makes the code a little more self-documenting.

Thanks in advance
Habib




--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
--------------------------------------------

RAnge as Table
 

Tom,
Thanks for the reply. I know about ADO.
I was hoping there was a something similar in the XL VBA object model.
as a workaround, I created an enumeration, and use that for column names.

For example
enum Orders
OrderID = 4
customerID = 5
...
end enum
for rownum = 2 to Sheet("Orders").Range("D65536").End(xlUp).Row
x = cells(rownum, Orders.OrderID)

If there is a better way, I'd love to know.

Regards
Habib

--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
--------------------------------------------
"Tom Ogilvy" wrote in message
...
if the workbook is closed, then you can reference your range using ADO and
treat it as a table thus utilizing SQL to manipulate/reference the data
therein.

--
Regards,
Tom Ogilvy


"HSalim[MVP]" wrote in message
...
Hi,
This question has probably been answered before.
I'm trying to use a range as a table, so that I can refer to columns be
their name

for example:
Set myrange = sheets("orders").Range("A10:N100")
For Each row in my range
myrange.ordertotal = myrange.subtotal + myrange.freight
Next

Wouldn't it be cool to say
myrange.hasheader = true (first row of the range is the column name)
Or even better
myrange.columnnames = array("col1", "ColName"...) or
myrange.columnNames = Range("A1:N1")

This will give me a lot of flexibility - I can move the range to another
location with little effect on the code.
Also makes the code a little more self-documenting.

Thanks in advance
Habib




--
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
--------------------------------------------