Array UDF Recalculation

[PBezucha]

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

 

[Gary''s Student]

Your code is good. Just expand it to include array formulas as well:

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
Else
sF = rCell.FormulaArray
rCell.FormulaArray = sF
End If
End If
Next rCell
End Sub

 

[Rick Rothstein]

Instead of replacing formulas and whatnot, have you tried simply putting an...

Application.Volatile

statement at the beginning of your UDFs?

 

[Charles Williams]

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

Instead of replacing formulas and whatnot, have you tried simply putting
an...

Application.Volatile

statement at the beginning of your UDFs?

 

[Charles Williams]

You would also need to detect and handle multi-cell array formulae as well:
This sub may be useful

Sub ExpandRange(oStartRange As Range, oEndRange As Range)

' Input:
' oStartRange,
' a range object that may or may not contain array formulae
' Output:
' oEndRange, a range object that has been expanded -
' to include all the cells in any array formula that is partly in the
range
'
Dim oCell As Range
Dim oArrCell As Range

On Error Resume Next
'
Set oEndRange = oStartRange
For Each oCell In oStartRange
If oCell.HasArray = True Then
For Each oArrCell In oCell.CurrentArray
If Intersect(oEndRange, oArrCell) Is Nothing Then
Set oEndRange = Union(oEndRange, oArrCell)
End If
Next oArrCell
End If
Next oCell
Set oCell = Nothing
Set oArrCell = Nothing
End Sub

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Rick Rothstein]

Nice website Charles... you have some **very** useful information summarized on your website (and I don't just mean the UDF stuff).

 

[Gary''s Student]

You are correct. Very nice catch!

 

[Charles Williams]

Thanks Rick

Nice website Charles... you have some **very** useful information summarized
on your website (and I don't just mean the UDF stuff).

 

[PBezucha]

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