2

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?

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

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.

  • Can you explain what you mean by volatility problems? – chabzjo Feb 10 '15 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. – Gary's Student Feb 10 '15 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 '15 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.