custom function not recalcing on sheet with outline view

[Guest]

Very strange: Have a user-written function used hundreds of times on one
sheet. Often, when the workbook is first opened, all of those function
references show as blank. Hitting F9 (recalc) has no effect; in fact the
only way I can get the functions to calculate is by expanding or compressing
one of the outline view ("grouping") areas on the sheet. At that point, all
the cells with the function in it recalc immediately and have the proper
values.

The functions in question are not within the outline area being changed. It
appears that if I remove all the grouping from the sheet, I can't get this
behavior. But I want the grouping there to help hide some parts of the sheet.

The function is a simple one, doing something similar to a VLOOKUP. It
follows the rules about all cells or ranges being referenced coming in
through the parameter list, so the calculation engine knows about precedents.

Does anyone know what might be causing this bizzarre behavior? I've not
found anything in the MS Knowledgebase or other web searches. Thanks in
advance.

 

[Guest]

add to function

application.screenupdating = false
'''ungroup everything
'''Your function code here
Application.calculate
''regroup everthing
application.screenuopdating = true

 

[Guest]

Thanks for the suggestion, but I don't think this will be a solution. Two
things:
First, there are hundreds of references to the function on the page, and I
don't think
I want to incur the overhead of grouping/ungrouping the whole page for each
function call. Further, I'd want the various groups on the page to remain
set the way they started, which could be a mixture of different levels of
expansion and compression. So the code to preserve those and reset them
would be elaborate.

My mention of the fact that grouping/ungrouping seems to trigger the recalc
was intended as a symptom that might help someone say "this is what is
happening." The underlying problem, though, seems to be that this sheet is
not recalc'd automatically when it needs to be, despite the fact that the
options setting is correct and the function has proper references to its
dependents through its parameter list. Actually, more accurately, it appears
as though it is not calc'd at all, even once, when the workbook is first
opened, until something explicit is done on this sheet to trigger the calc.
It is also the case that if I copy/paste to itself a single cell that happens
to be referenced in any one of the cells that call this function, then the
whole page recalculates instantly.

This is part of a fairly elaborate, large spreadsheet (20 sheets, thousands
of rows), but this is the first and only time I've ever seen any behavior
like this.