3

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?

2
  • If you don't want to specify the row, how will Excel/VBA know what row you're trying to manipulate?
    – misha256
    Feb 9, 2015 at 22:49
  • Because I'm passing the row as a parameter to the function.
    – chabzjo
    Feb 10, 2015 at 19:48

2 Answers 2

5

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
1

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.

3
  • Can you explain what you mean by volatility problems?
    – chabzjo
    Feb 10, 2015 at 19:47
  • I mean that if you change the contents of column B, the function is not smart enough to know it is necessary to recalculate itself. Feb 10, 2015 at 19:54
  • Actually, having the function knowing it is necessary to recalculate is not part of my requirements. But thanks for thinking ahead. :)
    – chabzjo
    Feb 11, 2015 at 21:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .