AutoFilling Function not updating

[PJohnson]

Can someone please tell me what I am missing here.

I use the followig function to concatenate two columns of dates.

When I use the fill handle to autofill the function down the column, the
values calculated in the first cell of the column are carried down the
column -- without recalculating or updating for each individual iteration.

I cannot seem to force the function to recalcuate or examine each iteration
of the function independently?

Any help appreciated.
===================
Public Function OsteoDate(MyText) as String

Dim FromDate
FromDate = ActiveCell.Offset(0, 1).Text

Dim ToDate
ToDate = ActiveCell.Offset(0, 2).Text

If MyText = "" Then 'Check to see if first date column is null
OsteoDate = "on " & FromDate
Else 'concatenate both dates together with "from" and "to"
OsteoDate = "from " & FromDate & " to " & ToDate
End If

End Function

 

[JE McGimpsey]

Since you're using ActiveCell, every call to OsteoDate during the same
calculation cycle will return the same result depending on which cell is
Active. In addition, since you haven't declared Application.Volatile,
the function will only recalculate when MyText changes, not when the
dates change.

If you want the function to be dependent on the cell that contains the
function, you can use

Public Function OsteoDate(MyText) As String
Dim ToDate As String
Dim FromDate As String
Application.Volatile
With Application.Caller
FromDate = .Offset(0, 1).Text
ToDate = .Offset(0, 2).Text
End With
If MyText = vbNullString Then
OsteoDate = "on " & FromDate
Else
OsteoDate = "from " & FromDate & " to " & ToDate
End If
End Function

Using Application.Volatile, however, means that each formula will
recalculate whenever a calculation occurs on the worksheet. A better
solution, IMO, would be to include the From and To dates in the argument
list, and make:

Public Function OsteoDate(byVal FromDate As String, _
ByVal ToDate As String, _
Optional ByVal MyText As String = vbNullString) As String
If MyText = vbNullString Then
OsteoDate = "on " & FromDate
Else
OsteoDate = "from " & FromDate & " to " & ToDate
End If
End Function

Or, even better, just base the function on the number of date arguments:

Public Function OsteoDate(ByVal FromDate As String, _
Optional ByVal ToDate As String = vbNullString) As String
If ToDate = vbNullString Then
OsteoDate = "on " & FromDate
Else
OsteoDate = "from " & FromDate & " to " & ToDate
End If
End Function

So you can call it as

A1: =OsteoDate(B1,C1)

and the function will update