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