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

Hi,

Try

Application.volatile

at the start of you function

Mike

"mark_the_yeti" wrote:

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

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...
--
HTH...

Jim Thomlinson


"mark_the_yeti" wrote:

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

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" wrote:

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

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!

"mark_the_yeti" wrote:

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" wrote:

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

mark_the_yeti wrote:
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.

"mark_the_yeti" wrote:
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 -----

"mark_the_yeti" wrote in message
...
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!

"mark_the_yeti" wrote:

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" wrote:

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

Errata....

I wrote:
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 -----

"JoeU2004" wrote in message
...
"mark_the_yeti" wrote:
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 -----

"mark_the_yeti" wrote in message
...
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!

"mark_the_yeti" wrote:

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" wrote:

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

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.

"JoeU2004" wrote:

"mark_the_yeti" wrote:
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 -----

"mark_the_yeti" wrote in message
...
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!

"mark_the_yeti" wrote:

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" wrote:

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

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

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

"mark_the_yeti" wrote:

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

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.

"witek" wrote:
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 -----

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

Improvement....

I wrote:
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 -----

"JoeU2004" wrote in message
...
"witek" wrote:
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 -----

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