Referencing cell only by column name in Excel 2010 VBA

Question

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?

score 5 · Accepted Answer · edited May 23 '17 at 12:41

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

score 1 · Answer 2 · answered Feb 10 '15 at 00:42

Consider:

Public Function GetValue2(ByVal myRow As Long, ByVal strColumnName As String) As String
    Dim myCell As Object

    Set myCell = Cells(myRow, Range(strColumnName).Column)
    GetValue2 = myCell.Value
End Function

In this example, I "named" column B "qwerty"

enter image description here

Please note there will be potential volatility problems with this function.

Referencing cell only by column name in Excel 2010 VBA

2 Answers2