Is Application.Volatile the correct solution?

[JenniferMurphy]

With help here from Rick Rothstein and Jerry Sullivan, I wrote a UDF (col()) to provide a column function similar to the built-in row() function. I later discovered that its results were not getting updated when a column was inserted before where it was called, causing the column letter to be off.

After little research, I added an Application.Volatile statement to the UDF and now it always gets updated. Is that the correct solution?

Here's the UDF:

Function col(Optional ByVal BaseCell As Range) As String
Application.Volatile    'Force recalculation on any update
 
Dim NumCols As Long
Dim CallerCell As Range
Set CallerCell = Application.Caller
 
With Application.Caller
  If BaseCell Is Nothing Then 'If no basecell, return the actual column letter
    col = Split(CallerCell.Address, "$")(1)
  Else                        'Else, return the column letter relative to the basecell
    NumCols = .Column - BaseCell.Column 'Get the displacement from the basecell
    If NumCols > 0 Then                 'If away from the basecell, return the displacement
      col = Split(.Parent.Cells(1, NumCols).Address, "$")(1)
    Else                                'Else, return "**"
      col = "**"
    End If
  End If
End With

End Function

 

[Yongle]

Application.Volatile does the job well

How about this variant ...

Function Col(Optional ByVal BaseCell As Range) As String
    Application.Volatile  
    If BaseCell Is Nothing Then Set BaseCell = Application.Caller
    Col = Split(BaseCell.Address, "$")(1)
End Function

 

[JenniferMurphy]

Application.Volatile does the job well

Thanks

How about this variant ...

Function Col(Optional ByVal BaseCell As Range) As String
    Application.Volatile  
    If BaseCell Is Nothing Then Set BaseCell = Application.Caller
    Col = Split(BaseCell.Address, "$")(1)
End Function

Wow!!! I love simple, tight code. I'll do some testing to ensure I understand it.

Thank you!

 

[offthelip]

in my version of EXCEL (2007) there is a perfectly good inbuilt function which does the same : Column()
Has this been dropped in more recent versions??
(I have just spotted the different you want the letter!!)

 

[Yongle]

@offthelip
=IF("You try the function", "You may learn something", "You will miss out")