Function not updating

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

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

"Mark Hanford" wrote in
message ...
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

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

In article , stuff-
says...
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

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



"Niek Otten" wrote in message
...
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

"Mark Hanford" wrote in
message ...
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

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

"Tushar Mehta" wrote in message
m...
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

In article , stuff-
says...
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

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

In article , stuff-
says...
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

"Tushar Mehta" wrote in message
m...
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

In article , stuff-
says...
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

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

In article , stuff-
says...
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



"Niek Otten" wrote in message
...
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

"Mark Hanford" wrote in
message ...
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

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


"Tushar Mehta" wrote in message
om...
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

In article , stuff-
says...
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



"Niek Otten" wrote in message
...
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

"Mark Hanford" wrote
in
message ...
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

Yay! That's the one...

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

Thanks,

Mark

"Tushar Mehta" wrote in message
om...
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

In article , stuff-
says...
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

"Tushar Mehta" wrote in
message
m...
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

In article , stuff-
says...
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

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

In article , stuff-
says...
Yay! That's the one...

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

Thanks,

Mark

"Tushar Mehta" wrote in message
om...
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

In article , stuff-
says...
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

"Tushar Mehta" wrote in
message
m...
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

In article , stuff-
says...
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