Automatic calculation of user-defined worksheet function

[Bart Deschoolmeester]

Hello,
i've defined the following user-defined function.
It references a cell on the previous sheet relative to the
current cell's (the caller) position.

Function PreviousWorksheet(RelativeRow, RelativeColumn As
Integer) As Variant
If Application.Caller.Worksheet.Name = "First sheet"
Then PreviousWorksheet = 0 Else PreviousWorksheet =
Application.Caller.Worksheet.Previous.Cells
(Application.Caller.Row + RelativeRow,
Application.Caller.Column + RelativeColumn).Value
End Function

When changing the cell the function is referencing to the
value is not updated. Only when I force the calculation
(pressing enter, copying the cell,.. but not F9) the
function is reevaluated.
Other, simpler functions give no problem so maybe it has
to do with how the function is defined (the caller
function).

Any solutions?
Or maybe there's an easier solution to reference the
previous sheet in EXCEL97. (A normal reference to the
sheet won't do as i want the function to work even if I
copy the sheet, with a normal reference all copied sheets
reference the original sheet and not the previous)

Thanks
Bart

 

[Charles Williams]

Excel only recalculates non-volatile functions when one of their input
parameters changes.
Your function references cells that are not in the argument list so excel
does not recalculate it as you want.

You would need to add Application.Volatile to the function, which will make
it recalculate at every recalculation, regardless of whether it really needs
to.

Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

 

[keepitcool]

Bart,

The RelNav function will be more flexible. However it will NOT be fast.
For speed I suggest using 1 cell that uses the RelSheetName, then in the
remaining cells for that sheet use formulas like:

=INDIRECT(ADDRESS(ROW();COLUMN();;;$A$1))
or
=INDIRECT(A1&"!"&CELL("address"))

Cheerz!


Function RelNav( _
Optional RelativeSheet As Long, _
Optional RelativeRow As Long, _
Optional RelativeColumn As Long) As Variant

'Optional volatility: Currently turned ON
Application.Volatile True

Dim s&, r&, c&
With Application.Caller
s = xlMod(.Worksheet.Index + RelativeSheet, _
.Worksheet.Parent.Worksheets.Count)
r = xlMod(.Row + RelativeRow, 2 ^ 16)
c = xlMod(.Column + RelativeColumn, 2 ^ 8)
End With
RelNav = Worksheets(s).Cells(r, c)
End Function

Function RelSheetName(Relativesheet As Long) As String
Application.Volatile True
Dim s&
With Application.Caller
s = xlMod(.Worksheet.Index + Relativesheet, _
.Worksheet.Parent.Worksheets.Count)
End With
RelSheetName = Worksheets(s).Name
End Function

Private Function xlMod&(n&, d&)
xlMod = n - d * Int((n - 1) / d)
End Function




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >