Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
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 -- Gary''s Student - gsnu200832 "PBezucha" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
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 "Gary''s Student" wrote in message ... 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 -- Gary''s Student - gsnu200832 "PBezucha" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
You are correct. Very nice catch!
-- Gary''s Student - gsnu200832 "Charles Williams" wrote: 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 "Gary''s Student" wrote in message ... 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 -- Gary''s Student - gsnu200832 "PBezucha" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF Recalculation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recalculation | Excel Worksheet Functions | |||
Automatic recalculation of array functions | Excel Programming | |||
Recalculation | Excel Discussion (Misc queries) | |||
Too much recalculation | Excel Programming | |||
Recalculation | Excel Programming |