How did Don's suggestion work out?
Some things to think about:
- Why do you need a full calculation? Excel tracks dependencies and in a
regular calculation on recalculates what needs to be calculated. The end
result should be the same, only faster.
If your UDF needs to be recalculated at every calculation, but isn't, then
you can specify it as a volatile function:
Function MyUDF(param)
Application.Volatile
' Do Stuff
End Function
However, a function only needs to be volatile if it has dependencies other
than those specifed in it's parameters, or the dependencies can't be
evaluated by Excel (depends on a reference contained in text, the system
clock, the moon phase, etc.)
I haven't done much with controlling calculation, but here you can find info
about the process and how to control it:
http://www.decisionmodels.com/calcsecretsg.htm
http://www.decisionmodels.com/calcsecretsh.htm
It's my understnading that setting Workshet.EnableCalculation to False and
True again makes the next calculation of that Worksheet a Full Calculation,
so here is procedure that I think will force a full calc of any specified
workbook:
Sub FullyCalculate(WhichWorkbook As Workbook)
Dim ws As Worksheet
With WhichWorkbook
For Each ws In .Worksheets
ws.EnableCalculation = False
ws.EnableCalculation = True ' Next Calculation will be a Full
Calculation
Next ws
For Each ws In .Worksheets
ws.Calculate
Next ws
End With
Set ws = Nothing
End Sub
To call, you could use:
FullyCalculate ActiveWorkbook
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of hanumant shinde
Sent: Saturday, February 11, 2012 12:18 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Replacement for Application.CalculateFull
Can anybody help pls. its really very imp for me
_____
From: dguillett1 <dguille...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Friday, 10 February 2012 11:27 PM
Subject: Re: $$Excel-Macros$$ Replacement for Application.CalculateFull
try
Application.Calculation = xlManual
sheets.select
for i=1 to sheets.count
activesheet.calculate
next i
Don Guillett
SalesAid Software
dguille...@gmail.com
From: hanumant shinde <mailto:arsfan2...@yahoo.co.in>
Sent: Friday, February 10, 2012 11:08 AM
To: Excel Group <mailto:excel-macros@googlegroups.com>
Subject: $$Excel-Macros$$ Replacement for Application.CalculateFull
Hi,
Can you please give any code or anything which will replace
Application.CalculateFull.
when i use above code this calculates all the Open workbooks whereas i want
to calculate only the Activeworkbook.
i have tried below code but its not reliable as it does not calculate
EVERYTIME.
Public Sub CalcActiveWBOnly()
Dim wrksht As Worksheet
Application.Calculation = xlManual
For Each wrksht In ThisWorkbook.Sheets
wrksht.Calculate
Debug.Print wrksht.Name
Next wrksht
End Sub
There is UDF lets say "test" which should get calculated after above code is
executed but it doesnt happen everytime though the above code gets executed
everytime.
its very very urgent and very very IMP for me. please help me.
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
