Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic calculation of user-defined worksheet function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic calculation of user-defined worksheet function
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 "Bart Deschoolmeester" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic calculation of user-defined worksheet function
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 "Bart Deschoolmeester" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Function Help | Excel Worksheet Functions | |||
User defined function returns imprecise value when used in worksheet | Excel Discussion (Misc queries) | |||
User Defined Function use any worksheet | Excel Discussion (Misc queries) | |||
User Defined function - Help | Excel Worksheet Functions | |||
Inefficient Calculation of User Defined Functions in Excel 2000 | Excel Programming |