Referencing Excel2007 Tables

[MrEPM]

I have a worksheet with several tables defined and need to find out information about the tables and data contained in the tables using basic Excel and VBA.

The referenced table is named tlObjectNames
<TABLE style="WIDTH: 433pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=576 border=0><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 238pt; mso-width-source: userset; mso-width-alt: 11593" width=317><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #820024; BORDER-LEFT: #f0f0f0; WIDTH: 94pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #820024 none" width=125 height=20>cdObjectNames</TD><TD class=xl68 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #820024; BORDER-LEFT: #f0f0f0; WIDTH: 37pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #820024 none" width=49>cdONOrder</TD><TD class=xl68 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #820024; BORDER-LEFT: #f0f0f0; WIDTH: 64pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #820024 none" width=85>cdObjectType</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #820024; BORDER-LEFT: #f0f0f0; WIDTH: 238pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #820024 none" width=317>cdONLenght</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #ff6792; BORDER-LEFT: #f0f0f0; COLOR: #3c3c3c; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FF6792 none" height=20>fnPrjAssumptions</TD><TD class=xl68 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #ff6792; BORDER-LEFT: #f0f0f0; COLOR: #3c3c3c; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FF6792 none">1</TD><TD class=xl68 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #ff6792; BORDER-LEFT: #f0f0f0; COLOR: #3c3c3c; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FF6792 none">Field</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #ff6792; BORDER-LEFT: #f0f0f0; COLOR: #3c3c3c; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FF6792 none">#VALUE!</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #ffb3c9; BORDER-LEFT: #f0f0f0; COLOR: #3c3c3c; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFB3C9 none" height=20>fnPrjBU</TD><TD class=xl68 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #ffb3c9; BORDER-LEFT: #f0f0f0; COLOR: #3c3c3c; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFB3C9 none">2</TD><TD class=xl68 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #ffb3c9; BORDER-LEFT: #f0f0f0; COLOR: #3c3c3c; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFB3C9 none">Field</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #ffb3c9; BORDER-LEFT: #f0f0f0; COLOR: #3c3c3c; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFB3C9 none">#VALUE!</TD></TR></TBODY></TABLE>

COL cdObjectNames contain names of other tables
I want to calculate the number of rows of the table named in the first COL and store it in the COL named cdONLength

Problem #1
I use the following formula in a cell to find the number of rows in a table
=Rows(tlObjectNames) and it returns the correct number of rows.

If I replace the table name with a cell reference containing the name of the table, the calculation returns a value of 1
Cell A1 contains the text tlObjectNames
=Rows(A1) returns 1

How can I get the formula to evaluate correctly?

Problem #2
I'm getting #Value! errors when passing a string reference to a function call. The function is...

Public Function GetObjectLength(ObjectName As String) As Integer
Dim Object As ListObject

GetObjectLength = 0
Set Object = ActiveWorkbook.Worksheets("Tables").ListObjects(ObjectName)
MsgBox (Object.ListRows.Count)
GetObjectLength = Object.ListRows.Count

End Function

The function work using a cell formula of =GetObjectLenght("tlObjectNames")

I get the #Value! error in the cell if I change the formula to reference a cell containing the name of the table.

Cell A2 contains the text tlObjectNames

=GetObjectLenght(A2) returns #Value!

What could be causing the error?

 

[mvptomlinson]

For problem #1, use:

=ROWS(INDIRECT(A1))

 

[MrEPM]

Using cell formula of =INDIRECT(F26) returns a #Ref! error until I use this formual version =INDIRECT("F26") where it returns the text.

If I use =ROWS(INDIRECT("F26")) it returns a value of 1 again.

 

[MrEPM]

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
The formual of =INDIRECT(F26) returns a works if the value in cell F26 contanins text specifying another cell

F26=A1
A1= Me

=INDIRECT(F26) returns the text Me