Gary€ś, thanks. Just what I needed. After a good snap and more patience I
discovered meanwhile that also CurrentArray works on this place.
Sub RecalculationForced()
Dim sF As String, Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Cell.HasFormula = True Then
sF = Cell.Formula
If Not Cell.HasArray Then
Cell.Formula = sF
Else
Cell.CurrentArray = sF
End If
End If
Next Cell
End Sub
Charles, I know, of course, your pages and highly appreciate them as well as
your refinement. I will consider the region of its application. What I try is
undoubtedly brutal, but for one-man tasks it seems quite sufficient,
considering the simplicity.
Sincerely
--
Petr Bezucha
"Charles Williams" wrote:
Thanks Rick
"Rick Rothstein" wrote in message
...
Nice website Charles... you have some **very** useful information summarized
on your website (and I don't just mean the UDF stuff).
--
Rick (MVP - Excel)
"Charles Williams" wrote in message
...
Also look at
http://www.decisionmodels.com/calcsecretsj.htm
for more information on writing robust and efficient VBA UDFs
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
"Rick Rothstein" wrote in message
...
Instead of replacing formulas and whatnot, have you tried simply putting
an...
Application.Volatile
statement at the beginning of your UDFs?
--
Rick (MVP - Excel)
"PBezucha" wrote in message
...
UDFs frequently do not recalculate all from various reasons, especially
after
some manipulation with codes. F9-key combinations are in this case
usually
of no avail, too. A reliable and quick remedy is to replicate all the
formulas (here in an active worksheet) by a macro. In its simplest form,
the
macro fails, however, if it encounters array formula. You can avoid, of
course, that incident:
Sub RecalculationForced()
Dim sF As String, rCell As Range
For Each rCell In ActiveSheet.UsedRange
If rCell.HasFormula = True Then
If Not rCell.HasArray Then
sF = rCell.Formula
rCell.Formula = sF
End If
End If
Next rCell
End Sub
This works well (xl 2002), but afterward you must replicate those omitted
formulas by hand.
Do you guess there can be a programmable way, how to detect the range of
an
array pertinent to the searched cell, and how to arrange its replication?
Sincerely
--
Petr Bezucha