0

I would like to refer to the values of the column using a VBA macro. The problem that I'm facing is If someone introduces a new column in between the columns the code becomes absolute. Below is my code snippet for reference.

Public Function Area()
row = ActiveCell.row
Dim AB As Double
Dim BB As Double

DA = Range("AB" & row).Value
BB = Range("BB" & row).Value
Area = AB * BB 

End Function

If someone introduces a new column before AB this code becomes absolute. I would like to know if there is a way to refer these columns by column names or assigning a unique identifier to each column and referring the same.

If someone has any idea about this, kindly comment. Thank you!!

BigBen
  • 46,229
  • 7
  • 24
  • 40
Raghu Nainar
  • 3
  • 1
  • 2
    Does this answer your question? [VBA selecting column by name after Selection.Find](https://stackoverflow.com/questions/50092861/vba-selecting-column-by-name-after-selection-find) – Sancarn Aug 10 '20 at 13:09

1 Answers1

0

You might consider using named ranges. You set the names to the whole columns. (Please note that the function will not be triggered by changes in the sheet. If you would like to have it as a user defined function, you need to give the values as parameter to the function.)

Public Function Area() As Double
    Dim row As Long
    Dim AB As Double
    Dim BB As Double
    
    row = ActiveCell.row
    AB = Range("aa").Cells(row).Value
    BB = Range("bb").Cells(row).Value
    Area = AB * BB
End Function
Viktor West
  • 544
  • 6
  • 9