Structured table references are pretty easy to generate in Excel. You know, the ones that look like:
I recently realized you can use those structured references in a VBA Evaluate function to build strings based on the active row of a table. This makes for shorter code and avoids the use of Intersect, ListRow and ListColumn functions.
For example, let’s pretend you have a table of Oscar winners by year and you want to build a short sentence for each row of the table. Maybe you get a lot of emails from people asking what movie won the award in a given year. With a table like the one below you can simply choose the applicable row of column C and copy and paste it into your reply.
This works okay, but it’s clunky. Instead, I’d rather generate the phrase in VBA based on the row of the ActiveCell. It was while writing this I had the happy thought to use VBA’s Evaluate function.
The Evaluate function basically returns the value of whatever you are evaluating as if it was entered in the active cell. At least that’s how I think of it for this instance. So, in the usage below, the structured reference’s @ symbol, e.g., [@Year] means “the value of the Year field in the active row of the table.”
It has two forms, one where you spell out Evaluate and one where you use square brackets, like I’ve done below.
Dim BestPicString As String
'only proceed if the ActiveCell is in the table body
On Error Resume Next
If Intersect(ActiveCell, ActiveCell.ListObject.DataBodyRange) Is Nothing Then
Exit Sub
End If
On Error GoTo 0
BestPicString = _
["Best Picture for " & tblBestPics[@Year] & CHAR(10) & "was " & tblBestPics[@Film]]
wsBestPics.Range("cellBestPic").Value = BestPicString
End Sub
I did this because when I use the brackets I don’t have to double the quotes. I’m not absolutely sure of this, but here are the two versions of Evaluate that work for me:
("=""Best Picture for "" & tblBestPics[@Year] & CHAR(10) & ""was "" & tblBestPics[@Film]")
and
["Best Picture for " & tblBestPics[@Year] & CHAR(10) & "was " & tblBestPics[@Film]]
One more thing to note is that both versions error when I don’t include the table name – tblBestPics – although that’s not necessary in an actual Excel formula where you could just refer to [@Year].
Below is the code that I would write if I wasn’t using the Evaluate function. It’s not bad, but requires more variables and logic:
Dim lo As Excel.ListObject
Dim ActiveTableRow As Long
Dim BestPic As String
Dim BestPicYear As String
Dim BestPicString As String
On Error Resume Next
If Intersect(ActiveCell, ActiveCell.ListObject.DataBodyRange) Is Nothing Then
Exit Sub
End If
On Error GoTo 0
Set lo = ActiveCell.ListObject
'header row is DatabodyRange.Rows(0)
ActiveTableRow = ActiveCell.Row - lo.DataBodyRange.Rows(0).Row
BestPicYear = lo.ListColumns("Year").DataBodyRange.Rows(ActiveTableRow)
BestPic = lo.ListColumns("Film").DataBodyRange.Rows(ActiveTableRow)
BestPicString = "Best Picture for " & BestPicYear & vbCrLf & "was " & BestPic
wsBestPics.Range("cellBestPic").Value = BestPicString
End Sub
In the INTERSECT function, you have used ActiveCell twice. Could we use Target variable instead of ActiveCell in the first instance?
I could not follow the use of ActiveCell twice. Please explain.
Hi Sandeep,
Thanks for the question.
The point of the section is to determine if the ActiveCell is in a table’s data range. Target can be more than one cell, so wouldn’t work for this purpose. I intersect it with ActiveCell.ListObject.DataBodyRange because I don’t want to run the code if ActiveCell is in the table’s header. If you’re not familiar with Range’s Listobject property, check it out.
I wrapped it in On Error statements, which is never a great practice because it might mask unexpected errors. I really should use a test that doesn’t just ignore errors. Some kind of error handling/ignoring is required here because although
ActiveCell.ListObject Is Nothingwill be True if ActiveCell isn’t in a table,ActiveCell.ListObject.DataBodyRange Is Nothingwill error.Thanks a lot Doug.
You’re welcome!
Dear Doug,
Many Thanks!!
i’m finding your articles VERY interesting
Let my just suggest to update your answer with a reference to the evaluate approach here:
https://stackoverflow.com/questions/12653713/need-to-return-the-row-value-in-a-table-in-excel-using-vba-could-be-any-table-a
Hi Roberto, somehow I missed your comment (the first in months). Thanks for the suggestion and glad you’re liking the posts!