Structured Table Refs in VBA

[colin_e]

I'm confused about the relationship between tables (using structured
references) and ranges.

I can work on individual cells from a table column, similar to working with
a range, using-

Dim oSh As Worksheet
Set oSh = ActiveSheet
Dim r As Range

For Each r In oSh.Range("MyRange[ColumnName]")
r.Value = 10
Next

But if I try to create a range object from a table, using any of-

r = oSh.Range("MyRange")
r = oSh.Range("MyRange[#Data]")
r = oSh.Range("MyRange[ rowvariable, [ColumnName]]")

I think I am failing to see some basic principle here. I see lots of
reference material on how structured table refs work in the Excel UI. Can
someone point me to something simialr, or even just some working examples,
for VBA code?

 

[joel]

I think you are simply missing the the Word Set before each object.
Set is used when you want a variable equivalent to an object. withou
set it is simply setting your variable to the value (not object) of th
object and not the object itself.

from
r = oSh.Range("MyRange")
r = oSh.Range("MyRange[#Data]")
r = oSh.Range("MyRange[ rowvariable, [ColumnName]]")


to

set r = oSh.Range("MyRange")
set r = oSh.Range("MyRange[#Data]")
set r = oSh.Range("MyRange[ rowvariable, [ColumnName]]")


You code is simply returning the value from each of the loctions. Th
word Set says your variable r is equivalent to the object on the righ
side of the equal sign.