Excel – Referencing cell only by column name in Excel 2010 VBA

microsoft-excel-2010named-rangesvba

In Excel 2010, I need to go through a data table row by row and manipulate the data in VBA.

My original function reads like this:

Private Function GetValue(ByVal myRange As Excel.Range, ByVal strCol As String) As String
    Dim myCell As Object
    Set myCell = myRange.Cells(, strCol) 
    GetValue = myCell.Value 
End Function

I call it like this:

GetValue(myRow, "AE")

myRow is an Excel.Range representing a row.
"AE" is the column index.

I want to convert to use column names instead of column indexes because users may choose to add or remove columns in the future. I identified a range of cells as a table in Excel, named the table, and chose unique column names.

This means I would now call the function by using:

GetValue(myRow, "column_name")

but I can't find examples where I can specify only the column name without also specifying the row.

Is this even feasible?

Best Answer

The code from other answer didn't compile for me, but it allowed me to do some further research.

Inspired by How do i loop an excel 2010 table by using his name & column reference? and How to loop though a table and access row items by their column header?, I ended up using:

Private Function GetValue(ByVal myRange As Excel.Range, ByVal strCol As String) As String
    GetValue = myRange.Columns(myRange.ListObject.ListColumns(strCol).Range.Column).Value
End Function
Related Question