Refreshing a User Defined Function

[ChrisA]

To all:

On Sheet 1 of my workbook, I have a date in cell A1. In the next sheet, I
want the same date as the previous sheet plus seven days. When I copy the
latest sheet, I want it to reference the sheet located prior to it rather
than referencing the original sheet (Sheet 1). After copying 52 worksheets,
I want each of them to reference the sheet located just prior to each of the
sheets.

I found this User Defined Function posted here on this discussion site and
it works great except for one thing. If I change the original date on Sheet
1, the UDF does not automatically update. Is there a way that I could make
this happen???

Any help would be greatly appreciated.

ChrisA

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1.
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

 

[Dave Peterson]

You could select that cell with the formula and hit F2 followed by enter. This
tells excel that the formula changed and it's time to reevaluate it.

Alternatively, you could add

application.volatile

to the top of the UDF:

Option Explicit
Function PrevSheet(rg As Range)

'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1.

Application.volatile

dim n as long

n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

The problem with these UDFs is that excel only recalculates them when it thinks
something changed. If you don't give it info (passed in the function call),
then excel won't recalc.

By making the function volatile, then excel will recalc whenever it
recalculates. So you may want to force a recalculation before you trust the
results.

ps.

If you have this in A2 of Sheet2
=prevsheet(A1)
and delete row 1 of sheet2 or insert new rows/columns, then your formula may not
be what you want. Since excel will adjust that range.

You might want to considering passing the address directly to the function:

=prevsheet("A1")

using this:

Option Explicit
Function PrevSheet(addr As String)
'Enter =PrevSheet("B1") on sheet2 and you'll get B1 from sheet1.
Application.Volatile
Dim n As Long
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(addr).Value
End If
End Function