My function currently has 6 parameters, (one of which is a longish
list of file names and the other parameters specify what information
to bring back from these files).
There's about 200 incidences of it on the current worksheets which
takes about 30 minutes to recalculate (this could be marginally
improved on, but isn't unreasonable given what the function is doing).
I'm happy to add another parameter, but I want it to leave the cell
alone (without reevaluating the function) if the 7th parameter is set
to FALSE and only redo it if the new parameter is TRUE.
sort of ...
if parameter7=FALSE then
exit function without altering the value from last recalculation
else
go through the lengthy process using the first six parameters
end if
sadly, I can't get it to leave the cells alone.
Shieldinng a range from the effects of <Ctrl><Alt><F9> would work for
me if this is possible - suspect not though.
As an interim (or possibly final!) measure, I knocked this up...
Sub ReDdoBlock()
'needs a block highlighting, the top left cell formula gets copied
'to each other cell in the block and then turned into a value
For Each thingy In Selection.Cells
z = z + 1
If z = 1 Then
theformula = thingy.FormulaR1C1
Else
thingy.FormulaR1C1 = theformula
answer = thingy.Value
thingy.FormulaR1C1 = answer
End If
Next thingy
End Sub
which might be useful elsewhere too.