Another Issue With A Function

[SkippyPB]

Ron Rothstein suggested and I used this function in my Excel 2003
Workbook:

Public Function QBRushYds(W, Q)
QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10
End Function


In Worksheet Totals Cell B56 thru B59 I have the following four
calculations:

=TRUNC(QBRushYds(1,1))
=TRUNC(QBRushYds(1,2))
=TRUNC(QBRushYds(1,3))
=TRUNC(QBRushYds(1,4))


In Worksheet Week1 Cells C3 thru C6 I have the following values:

725
814
283
9

The function correctly calculates the values:

72
81
28
0

If I change any of the values in C3 thru C6, the values in B56 thru
B59 do not change.

In Tools, Options, Calculation I have Calculation Automatic checked. I
have other UDFs that recalc when values change. Why doesn't this one?

Thanks.


////
(o o)
-oOO--(_)--OOo-

Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor.
The other stayed behind in the cotton fields and never amounted to much.

The second one, naturally, became known as the lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[Dave Peterson]

Since you're not passing the range that's changing to the function, excel
doesn't know anything changed--so it doesn't recalculate the cell with the
formula.

You could add a line:

Public Function QBRushYds(W, Q)
application.volatile
QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10
End Function

But this only forces the function to recalculate when excel recalcs. I'd try to
force a recalc before I trusted the results.

 

[Mike H]

Hi,

Application.volatile should do it

Public Function QBRushYds(W, Q)
Application.Volatile
QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10
End Function

Mike

 

[SkippyPB]

Hi,

Application.volatile should do it

Public Function QBRushYds(W, Q)
Application.Volatile
QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10
End Function

Mike

Thanks to Mike and Dave. That change works just fine.

////
(o o)
-oOO--(_)--OOo-

Two boll weevils grew up in South Carolina.
One went to Hollywood and became a famous actor.
The other stayed behind in the cotton fields and never amounted to much.

The second one, naturally, became known as the lesser of two weevils.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve