If you use tables in Excel 2007, you may find this useful to know: The scope
of a table is "WorkBook", not "WorkSheet". This is unexpected. In VBA, to
refer to a table (a "list" in earlier versions of Excel), its worksheet
object must be specified, as in the following:
Debug.Print ActiveSheet.ListObjects("Schedule").Name
This suggests that the scope is WorkSheet. However, the Name Manager, on
the Formulas tab, shows that the scope is WorkBook. To demonstrate that that
is the case, copy a worksheet containing a table (leave both versions in the
same workbook). The tables in the copy are renamed. For example, in my
case, the table "Schedule" was renamed "Schedule10" on the copied sheet.
Consequently, the line of code above won't work on a copy of the original
sheet. That makes tables less powerful than they could have been, at least
from the point of view of VBA programming. I wish Microsoft would consider
changing that behavior in a Service Pack.
Chris