VBA - changing VOLATILITY of a UDF called PrevSheet(), which references Previous Tab

[thegurumonkey]

Hi,


I'm hoping to use the UDF PrevSheet() to replace INDIRECT() for referencing data in previous sheets, TO AVOID IT BEING VOLATILE
BUT the code for the macro says this is volatile too.


Can I simple replace the line:


Application.Volatile True
with
Application.Volatile False

By which i mean, would that work, rather than would that be wise (though I'm interested in both answers)


For reference, the entire code was:


Function PrevSheet(rCell As Range)
Application.Volatile
Dim i As Integer
i = rCell.Cells(1).Parent.Index
PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function




Also, a separate but related quick question:


I need a NextSheet() too, so can I basically change both occurences of the name itself and change one '-' to a '+':
i.e.
PrevSheet = Sheets(i - 1).Range(rCell.Address)
to
NextSheet = Sheets(i + 1).Range(rCell.Address)


Sorry if it's more complicated than that. I'm just guessing and have zero experience with VBA.

HUGE HUGE thanks in advance! VERY useful!