RAnge as Table

[HSalim[MVP]]

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

 

[Tom Ogilvy]

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.

 

[HSalim[MVP]]

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