Function not updating

[Mark Hanford]

I have a simple Excel 2000 spreadsheet where each "record" consists of two
rows

R1 4.69 0.39 1.89 0.41 0.79 4.81
R2 56% 13% 6% 40% 20% 16%
R3 4.69 0.37 1.89 0.42 0.79 4.64
R4 56% 13% 6% 41% 20% 16%
R5 4.69 0.37 1.89 0.41 0.63 3.49
R6 56% 13% 6% 40% 16% 12%

And I want a row at the end that compares the last record with the one
before. Statically, this might be
=R6C1-R4C1 =R6C2-R4C2 ...

But I find that when I insert rows between the data and the summaries, they
"cleverly" keep the row id's the same.
I have created a function to calculate this:

Function CheckChanges() As Variant
CheckChanges = Cells(ActiveCell.Row - 3, ActiveCell.Column).Value -
Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
End Function

However this doesn't update until I edit the field and press the enter key.
I have tried some solutions I've found on the net, like putting a "volatile"
parameter and just passing today(), but that doesn't work either.
Is a function the way to go with this? Is there another way, although even
if there's another way in this case, I'd like to know why it doesn't work

Yours stumped,

Mark

 

[Niek Otten]

Hi Mark,

You'll have to include the arguments in the function definition and the
function call.
Now Excel doesn't know there is a dependency, so it doesn't recalculate.
You can include "Application.Volatile" in your function header, but then it
will always recalculate, even if it is not necessary. And although the
present version of Excel seems to calculate in the correct order, I'm not
confident it will always do that, also in future versions.
So the best way really is to define arguments.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Tushar Mehta]

First, you don't need a UDF for this. Use a relative formula. Using
the RC scheme, you should have =R[-3]C - R[-1]C. This formula is good
for any cell and will give the difference between the value of the cell
three rows above less the value in the cell one row above.

The reason your UDF doesn't work is that XL doesn't know if and when it
needs to call it during a recalculation. With very (very, very) few
exceptions, a UDF should operate on only arguments passed to it.

You could have used:

Function CheckChanges(Cell1, Cell2) As Variant
CheckChanges = Cell1.Value - Cell2.Value
End Function

and used it as =CheckChanges(R[-3]C, R[-1]C), which of course, leads us
back to the first paragraph in this message!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Mark Hanford]

Sorry, what I meant was that I started with the inital example, and then
added the parameters in another example...

Function CheckChanges(Optional Volatile) As Variant
Dim ThisValue
Dim PrevValue

ThisValue = Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
PrevValue = Cells(ActiveCell.Row - 3, ActiveCell.Column).Value

CheckChanges = PrevValue - ThisValue
End Function

And then called either with =CheckChanges(B51) or =CheckChanges(Today())
They make no difference, and the second causes an error on F9 updates.

 

[Mark Hanford]

Thanks, I'd totally forgotten that this notation even existed, and never
even knew that you could do this relative stuff.

The problem is, it suffers from exactly the same problem. My nice =R[-3]C -
R[-1]C in row 52 becomes =R[-4]C - R[-2]C after I insert a row above the
summary row

I always want the calculation to be on the previous and third from previous
rows, regardless of how many rows are inserted.

Mark

First, you don't need a UDF for this. Use a relative formula. Using
the RC scheme, you should have =R[-3]C - R[-1]C. This formula is good
for any cell and will give the difference between the value of the cell
three rows above less the value in the cell one row above.

The reason your UDF doesn't work is that XL doesn't know if and when it
needs to call it during a recalculation. With very (very, very) few
exceptions, a UDF should operate on only arguments passed to it.

You could have used:

Function CheckChanges(Cell1, Cell2) As Variant
CheckChanges = Cell1.Value - Cell2.Value
End Function

and used it as =CheckChanges(R[-3]C, R[-1]C), which of course, leads us
back to the first paragraph in this message!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I have a simple Excel 2000 spreadsheet where each "record" consists of two
rows

R1 4.69 0.39 1.89 0.41 0.79 4.81
R2 56% 13% 6% 40% 20% 16%
R3 4.69 0.37 1.89 0.42 0.79 4.64
R4 56% 13% 6% 41% 20% 16%
R5 4.69 0.37 1.89 0.41 0.63 3.49
R6 56% 13% 6% 40% 16% 12%

And I want a row at the end that compares the last record with the one
before. Statically, this might be
=R6C1-R4C1 =R6C2-R4C2 ...

But I find that when I insert rows between the data and the summaries, they
"cleverly" keep the row id's the same.
I have created a function to calculate this:

Function CheckChanges() As Variant
CheckChanges = Cells(ActiveCell.Row - 3, ActiveCell.Column).Value -
Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
End Function

However this doesn't update until I edit the field and press the enter key.
I have tried some solutions I've found on the net, like putting a "volatile"
parameter and just passing today(), but that doesn't work either.
Is a function the way to go with this? Is there another way, although even
if there's another way in this case, I'd like to know why it doesn't work

Yours stumped,

Mark

 

[Tushar Mehta]

Ah, ha! Use

=INDIRECT(ADDRESS(ROW()-3,COLUMN()))-INDIRECT(ADDRESS(ROW()-1,COLUMN
()))

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thanks, I'd totally forgotten that this notation even existed, and never
even knew that you could do this relative stuff.

The problem is, it suffers from exactly the same problem. My nice =R[-3]C -
R[-1]C in row 52 becomes =R[-4]C - R[-2]C after I insert a row above the
summary row

I always want the calculation to be on the previous and third from previous
rows, regardless of how many rows are inserted.

Mark

First, you don't need a UDF for this. Use a relative formula. Using
the RC scheme, you should have =R[-3]C - R[-1]C. This formula is good
for any cell and will give the difference between the value of the cell
three rows above less the value in the cell one row above.

The reason your UDF doesn't work is that XL doesn't know if and when it
needs to call it during a recalculation. With very (very, very) few
exceptions, a UDF should operate on only arguments passed to it.

You could have used:

Function CheckChanges(Cell1, Cell2) As Variant
CheckChanges = Cell1.Value - Cell2.Value
End Function

and used it as =CheckChanges(R[-3]C, R[-1]C), which of course, leads us
back to the first paragraph in this message!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I have a simple Excel 2000 spreadsheet where each "record" consists of two
rows

R1 4.69 0.39 1.89 0.41 0.79 4.81
R2 56% 13% 6% 40% 20% 16%
R3 4.69 0.37 1.89 0.42 0.79 4.64
R4 56% 13% 6% 41% 20% 16%
R5 4.69 0.37 1.89 0.41 0.63 3.49
R6 56% 13% 6% 40% 16% 12%

And I want a row at the end that compares the last record with the one
before. Statically, this might be
=R6C1-R4C1 =R6C2-R4C2 ...

But I find that when I insert rows between the data and the summaries, they
"cleverly" keep the row id's the same.
I have created a function to calculate this:

Function CheckChanges() As Variant
CheckChanges = Cells(ActiveCell.Row - 3, ActiveCell.Column).Value -
Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
End Function

However this doesn't update until I edit the field and press the enter key.
I have tried some solutions I've found on the net, like putting a "volatile"
parameter and just passing today(), but that doesn't work either.
Is a function the way to go with this? Is there another way, although even
if there's another way in this case, I'd like to know why it doesn't work

Yours stumped,

Mark

 

[Tushar Mehta]

That is not how Volatile works. Check XL VBA help for its correct use.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Mark Hanford]

Ah, I see now. I didn't actually know about the "volatile" method, I just
picked that word up as that's what I wanted the parameter to do

It still doesn't work though. If I put application.volatile in the top of
my function, and then put =CheckChanges() in the cells, I get no updates
until I edit a cell and press [Enter], and then all cells update to same
value.

In my function I use ActiveCell.Row and .Cell, I was assuming that this
refers to the cell the function is in, not just the cell currently
highlighted; is this correct?

Mark

 

[Mark Hanford]

Yay! That's the one...

I do like to do thinks with fancy formulas rather than UDF's.

Thanks,

Mark

Ah, ha! Use

=INDIRECT(ADDRESS(ROW()-3,COLUMN()))-INDIRECT(ADDRESS(ROW()-1,COLUMN
()))

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thanks, I'd totally forgotten that this notation even existed, and never
even knew that you could do this relative stuff.

The problem is, it suffers from exactly the same problem. My nice =R[-3]C -
R[-1]C in row 52 becomes =R[-4]C - R[-2]C after I insert a row above the
summary row

I always want the calculation to be on the previous and third from previous
rows, regardless of how many rows are inserted.

Mark

First, you don't need a UDF for this. Use a relative formula. Using
the RC scheme, you should have =R[-3]C - R[-1]C. This formula is good
for any cell and will give the difference between the value of the cell
three rows above less the value in the cell one row above.

The reason your UDF doesn't work is that XL doesn't know if and when it
needs to call it during a recalculation. With very (very, very) few
exceptions, a UDF should operate on only arguments passed to it.

You could have used:

Function CheckChanges(Cell1, Cell2) As Variant
CheckChanges = Cell1.Value - Cell2.Value
End Function

and used it as =CheckChanges(R[-3]C, R[-1]C), which of course, leads us
back to the first paragraph in this message!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I have a simple Excel 2000 spreadsheet where each "record" consists

of
two

rows

R1 4.69 0.39 1.89 0.41 0.79 4.81
R2 56% 13% 6% 40% 20% 16%
R3 4.69 0.37 1.89 0.42 0.79 4.64
R4 56% 13% 6% 41% 20% 16%
R5 4.69 0.37 1.89 0.41 0.63 3.49
R6 56% 13% 6% 40% 16% 12%

And I want a row at the end that compares the last record with the one
before. Statically, this might be
=R6C1-R4C1 =R6C2-R4C2 ...

But I find that when I insert rows between the data and the

summaries,
they

"cleverly" keep the row id's the same.
I have created a function to calculate this:

Function CheckChanges() As Variant
CheckChanges = Cells(ActiveCell.Row - 3, ActiveCell.Column).Value -
Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
End Function

However this doesn't update until I edit the field and press the

enter
key.

I have tried some solutions I've found on the net, like putting a "volatile"
parameter and just passing today(), but that doesn't work either.
Is a function the way to go with this? Is there another way,

although
even

if there's another way in this case, I'd like to know why it doesn't work

Yours stumped,

Mark

 

[Tushar Mehta]

Glad that worked out.

Yes, it's a balance between formulas and UDFs. I think that at some
point the benefits of formulas erode because of maintainence concerns.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Yay! That's the one...

I do like to do thinks with fancy formulas rather than UDF's.

Thanks,

Mark

Ah, ha! Use

=INDIRECT(ADDRESS(ROW()-3,COLUMN()))-INDIRECT(ADDRESS(ROW()-1,COLUMN
()))

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thanks, I'd totally forgotten that this notation even existed, and never
even knew that you could do this relative stuff.

The problem is, it suffers from exactly the same problem. My nice =R[-3]C -
R[-1]C in row 52 becomes =R[-4]C - R[-2]C after I insert a row above the
summary row

I always want the calculation to be on the previous and third from previous
rows, regardless of how many rows are inserted.

Mark

First, you don't need a UDF for this. Use a relative formula. Using
the RC scheme, you should have =R[-3]C - R[-1]C. This formula is good
for any cell and will give the difference between the value of the cell
three rows above less the value in the cell one row above.

The reason your UDF doesn't work is that XL doesn't know if and when it
needs to call it during a recalculation. With very (very, very) few
exceptions, a UDF should operate on only arguments passed to it.

You could have used:

Function CheckChanges(Cell1, Cell2) As Variant
CheckChanges = Cell1.Value - Cell2.Value
End Function

and used it as =CheckChanges(R[-3]C, R[-1]C), which of course, leads us
back to the first paragraph in this message!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I have a simple Excel 2000 spreadsheet where each "record" consists of
two
rows

R1 4.69 0.39 1.89 0.41 0.79 4.81
R2 56% 13% 6% 40% 20% 16%
R3 4.69 0.37 1.89 0.42 0.79 4.64
R4 56% 13% 6% 41% 20% 16%
R5 4.69 0.37 1.89 0.41 0.63 3.49
R6 56% 13% 6% 40% 16% 12%

And I want a row at the end that compares the last record with the one
before. Statically, this might be
=R6C1-R4C1 =R6C2-R4C2 ...

But I find that when I insert rows between the data and the summaries,
they
"cleverly" keep the row id's the same.
I have created a function to calculate this:

Function CheckChanges() As Variant
CheckChanges = Cells(ActiveCell.Row - 3, ActiveCell.Column).Value -
Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
End Function

However this doesn't update until I edit the field and press the enter
key.
I have tried some solutions I've found on the net, like putting a
"volatile"
parameter and just passing today(), but that doesn't work either.
Is a function the way to go with this? Is there another way, although
even
if there's another way in this case, I'd like to know why it doesn't
work

Yours stumped,

Mark

 

[Mark Hanford]

They are a bit of a nightmare to ammend, especially if there's formatting on
the cells, as a simple "drag-to-the-right" at auto-fill them kills the
formats.

Luckily, find-and-replace seems to work on functions too.

M

Glad that worked out.

Yes, it's a balance between formulas and UDFs. I think that at some
point the benefits of formulas erode because of maintainence concerns.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Yay! That's the one...

I do like to do thinks with fancy formulas rather than UDF's.

Thanks,

Mark

Ah, ha! Use

=INDIRECT(ADDRESS(ROW()-3,COLUMN()))-INDIRECT(ADDRESS(ROW()-1,COLUMN
()))

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thanks, I'd totally forgotten that this notation even existed, and never
even knew that you could do this relative stuff.

The problem is, it suffers from exactly the same problem. My nice =R[-3]C -
R[-1]C in row 52 becomes =R[-4]C - R[-2]C after I insert a row above the
summary row

I always want the calculation to be on the previous and third from previous
rows, regardless of how many rows are inserted.

Mark

First, you don't need a UDF for this. Use a relative formula. Using
the RC scheme, you should have =R[-3]C - R[-1]C. This formula is good
for any cell and will give the difference between the value of the cell
three rows above less the value in the cell one row above.

The reason your UDF doesn't work is that XL doesn't know if and

when
it

needs to call it during a recalculation. With very (very, very) few
exceptions, a UDF should operate on only arguments passed to it.

You could have used:

Function CheckChanges(Cell1, Cell2) As Variant
CheckChanges = Cell1.Value - Cell2.Value
End Function

and used it as =CheckChanges(R[-3]C, R[-1]C), which of course,

leads
us

back to the first paragraph in this message!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I have a simple Excel 2000 spreadsheet where each "record"

consists
of

two
rows

R1 4.69 0.39 1.89 0.41 0.79 4.81
R2 56% 13% 6% 40% 20% 16%
R3 4.69 0.37 1.89 0.42 0.79 4.64
R4 56% 13% 6% 41% 20% 16%
R5 4.69 0.37 1.89 0.41 0.63 3.49
R6 56% 13% 6% 40% 16% 12%

And I want a row at the end that compares the last record with

the
one

before. Statically, this might be
=R6C1-R4C1 =R6C2-R4C2 ...

But I find that when I insert rows between the data and the summaries,
they
"cleverly" keep the row id's the same.
I have created a function to calculate this:

Function CheckChanges() As Variant
CheckChanges = Cells(ActiveCell.Row - 3, ActiveCell.Column).Value -
Cells(ActiveCell.Row - 1, ActiveCell.Column).Value
End Function

However this doesn't update until I edit the field and press the enter
key.
I have tried some solutions I've found on the net, like putting a
"volatile"
parameter and just passing today(), but that doesn't work either.
Is a function the way to go with this? Is there another way, although
even
if there's another way in this case, I'd like to know why it doesn't
work

Yours stumped,

Mark