Cell updates not occuring

[mark_the_yeti]

I have many cells using a formula I created in VBA (below).

When the workbook opens it asks me to allow macro, and I do. Then it asks
if I want to update, and I do. The cells remain un-updated, however, until I
double-click on them as if to edit, then hit enter. Only then does any one
cell update.

I have made sure the File-Options-Calculate-Autocalculate option has been
selected.
F9 has no effect.

Note that the worksheet "Mark P" is hidden in this workbook, and the values
in Mark P are coming from a worksheet in a different workbook. I have
confirmed that this part is working properly, though.

You help is seincerely appreciated.

Function MarkP(task, start, fin)
hours = 0
For Row = start To fin
Set curCell = Worksheets("Mark P").Cells(Row, 4)
Set valCell = Worksheets("Mark P").Cells(Row, 5)
If curCell.Value = task Then
hours = hours + valCell.Value
End If
Next Row
MarkP = hours

End Function

 

[Mike H]

Hi,

Try

Application.volatile

at the start of you function

Mike

 

[Jim Thomlinson]

You can add Application.Volatile to the start of the function to make it
volatile. That will force it to recalc any time a calc runs in XL. Or ir you
woant you could just use

Ctrl + Alt + Shift + F9

Which is a full recalc regardless whether the cells need to be recalced or
not...

 

[mark_the_yeti]

Please also note that I'm working in Excel 2003. When I asked a colleague to
try this, he was unable to even get the "enter and exit" solution to work in
Excel 2007....

Also, the formula in the worksheet cell is "=markp($F4,4,454)" in one case
anyway.

 

[mark_the_yeti]

The Application.Volatile solution worked very well.

alt+shift+ctrl+F9 worked as well, but bogged down my PC for a full minute or
two.

The 2007 vs. 2003 issue I described was occuring becuse there's an
additional; option to enable linked content in 2007 that was not being
actived.

My spreadsheet now works exactly as I'd hoped. Thank you!

 

[witek]

I have many cells using a formula I created in VBA (below).

When the workbook opens it asks me to allow macro, and I do. Then it asks
if I want to update, and I do. The cells remain un-updated, however, until I
double-click on them as if to edit, then hit enter. Only then does any one
cell update.

I have made sure the File-Options-Calculate-Autocalculate option has been
selected.
F9 has no effect.

Note that the worksheet "Mark P" is hidden in this workbook, and the values
in Mark P are coming from a worksheet in a different workbook. I have
confirmed that this part is working properly, though.

You help is seincerely appreciated.

Function MarkP(task, start, fin)
hours = 0
For Row = start To fin
Set curCell = Worksheets("Mark P").Cells(Row, 4)
Set valCell = Worksheets("Mark P").Cells(Row, 5)
If curCell.Value = task Then
hours = hours + valCell.Value
End If
Next Row
MarkP = hours

End Function

Excel calls udf function when parameters to that function change values.

As long as task, start and fin do not change excel does not see any
reason to call that function and update its value.

Excel does not consider changes in Worksheets("Mark P").Cells(Row, 4)
range as a reason to call the function because this range is not a
parameter to that function

Instead of having task, start and fin pass task and a range as
parameters and use start and fin in excel formula to build range which
should be passed to your function.

 

[JoeU2004]

The Application.Volatile solution worked very well.

Are you aware that that causes those functions to be executed every time any
cell in the workbook is modified?

Alternatively, I would be inclined to do the following.

First, create a Workbook_Open event macro. Choose one unused cell in the
workbook, say Z1, and do the following:

Private Sub Workbook_Open()
Range("Z1").Clear
End Sub

(Of course, Z1 does not have to be totally unused. It can be any cell that
Workbook_Open modifies for whatever purpose.)

Then, for each function that you want executed with the workbook is opened,
create a dependency on Z1 either by passing it as an unused parameter or by
including it in the computation, e.g. MarkP(...)+Z1 or MarkP(...)&Z1
depending on the type of the value returned by the function.

Of course, you could accomplish the same thing by having the Workbook_Open
macro directly calculate the cells that contain references to the functions
that you want executed when the workbook is opened; e.g.
Range("B1").Calculate.

The problem with that is: if you move those cells or otherwise cause them
to be moved, Range("B1") might no longer be correct. You can ameliorate
that problem by naming all of the cells and ranges of cells to be executed
when the workbook is opened, and Range("name") in the Workbook_Open macro.


----- original message -----

 

[JoeU2004]

Errata....

Private Sub Workbook_Open()
Range("Z1").Clear
End Sub

Of course, it would be better to name the cell and refer to the cell name in
the macro, because the cell location might change due to worksheet
modifications.


----- original message -----

 

[mark_the_yeti]

Joe,

I am indeed aware of the recalculation issue. Fortunately, the sheet is
mostly for viewing purposes only, summing totals from other worksheets and
workbooks. The recalculation takes mere seconds, however. Strangely, the
alt+shift+ctrl+F9 recalc takes a full minute or more...

I will explore your proposed solution when I've got some spare time.

 

[mark_the_yeti]

Note that I needed to save this file as a "Macro-enables Excel 2007" file
type...
Unless that's coincidence. Confirmation anyone?

 

[Mike H]

The fact that you had macros in leads to no surprise that if you wanted them
to work the file needed to be macro enabled and has nothing to do with the
addition of application.volatile

Mike

 

[witek]

JoeU2004 wrote:
"... or by including it in the computation, e.g. MarkP(...)+Z1 or

MarkP(...)&Z1 depending on the type of the value returned by the function.

including non parameter in computation does not call a function


Function aaaa()

aaaa = Range("a1").Value + 1

End Function


changing A1 value does not change value of a cell where function is used.

in general udf function result should depend only on parameters passed
to a function.

 

[JoeU2004]

including non parameter in computation does not call a function
[....]
aaaa = Range("a1").Value + 1
[....]
changing A1 value does not change value of a cell where function is used.

I think you misunderstood.

MarkP() is a UDF, and MarkP(...)+Z1 and MarkP(...)&Z1 are fragments of an
Excel formula.

The OP had defined MarkP as Function MarkP(task, start, fin). Presumably
that is called in an Excel formula like MarkP(A1,B1,C1).

I was suggesting that he could add a 4th parameter that need not be used in
the UDF, to wit: Function MarkP(task, start, fin, dummy), which could be
called in an Excel formula like MarkP(A1,B1,C1,Z1).

Alternatively, instead of modifying all functions that the OP might want
executed when the workbook is opened, he could simply include a reference to
Z1 in the Excel expressions that use those functions, using an appropriate
form, "...+Z1" or "...&Z1", depending on whether the expression is numeric
or text.

Recall that in my example of Workbook_Open, I simply cleared Z1. So
"...+Z1" would add zero, and "...&Z1" would concatenate a null string, both
effectively no-ops.

Putting this all together, you can construct the following experiment to see
what I mean.

Create the following UDF:

Function markp(a, b, c)
MsgBox "markp from " & Application.Caller.Address
End Function

Create the following workbook event macro:

Private Sub Workbook_Open()
Range("z1").Clear
End Sub

In Excel, create the following formulas:

A1: =markp(B1,C1,D1)+Z1
A2: =markp(B1,C1,D1)&Z1

Save and reopen the workbook. You should get the following message boxes:

markp from $A$1
markp from $A$2

Works just fine in my revision of Excel 2003.


----- original message -----

 

[JoeU2004]

Improvement....

Create the following workbook event macro:

It would be better to write:

Private Sub Workbook_Open()
MsgBox "workbook_open"
Range("z1").Clear
End Sub

Save and reopen the workbook. You should get the following message boxes:

workbook_open
markp from $A$1
markp from $A$2

If you don't see see the "markp" message boxes, my guess is you also will
not see the "workbook_open" message box. That would indicate that you did
not set up the Workbook_Open event macro properly.


----- original message -----

including non parameter in computation does not call a function
[....]
aaaa = Range("a1").Value + 1
[....]
changing A1 value does not change value of a cell where function is
used.

I think you misunderstood.

MarkP() is a UDF, and MarkP(...)+Z1 and MarkP(...)&Z1 are fragments of an
Excel formula.

The OP had defined MarkP as Function MarkP(task, start, fin). Presumably
that is called in an Excel formula like MarkP(A1,B1,C1).

I was suggesting that he could add a 4th parameter that need not be used
in the UDF, to wit: Function MarkP(task, start, fin, dummy), which could
be called in an Excel formula like MarkP(A1,B1,C1,Z1).

Alternatively, instead of modifying all functions that the OP might want
executed when the workbook is opened, he could simply include a reference
to Z1 in the Excel expressions that use those functions, using an
appropriate form, "...+Z1" or "...&Z1", depending on whether the
expression is numeric or text.

Recall that in my example of Workbook_Open, I simply cleared Z1. So
"...+Z1" would add zero, and "...&Z1" would concatenate a null string,
both effectively no-ops.

Putting this all together, you can construct the following experiment to
see what I mean.

Create the following UDF:

Function markp(a, b, c)
MsgBox "markp from " & Application.Caller.Address
End Function

Create the following workbook event macro:

Private Sub Workbook_Open()
Range("z1").Clear
End Sub

In Excel, create the following formulas:

A1: =markp(B1,C1,D1)+Z1
A2: =markp(B1,C1,D1)&Z1

Save and reopen the workbook. You should get the following message boxes:

markp from $A$1
markp from $A$2

Works just fine in my revision of Excel 2003.


----- original message -----

JoeU2004 wrote:
"... or by including it in the computation, e.g. MarkP(...)+Z1 or


including non parameter in computation does not call a function


Function aaaa()

aaaa = Range("a1").Value + 1

End Function


changing A1 value does not change value of a cell where function is
used.

in general udf function result should depend only on parameters passed to
a function.