JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,535
- Office Version
- 365
- Platform
- Windows
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:
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:
Code:
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