Controlling Calculation from VBA 
Controlling Calculation from VBA.For information on what the calculation methods do see Calculation Methods. VBA allows you to control Calculation methods, properties and events: Calculation Methods:F9  RecalculateApplication.Calculate CTRL/ALT/F9 – Full CalculationIn Excel 2000 and later versions: Application.CalculateFullIn Excel 97 there are two possible methods using either SendKeys or EnableCalculation: You can use SendKeys to send the CTRL/ALT/F9 key sequence. This can be tricky because Sendkeys just sends the keystrokes into the keyboard buffer, and they are not normally processed until VBA has ended and you cannot guarantee that they will be processed by the correct window/application etc. With Windows 95/98/ME: SendKeys "%^{F9}", True
With Windows NT/2000/XP: Application.SendKeys "%^{F9}" If the VBA procedure containing the Sendkeys statement is called directly or indirectly from a command button the True argument does not work, so you have to use DoEvents to get Win Xp and Excel97 to process them. Another method uses the worksheet.enablecalculation property. When this property is changed all the formulae on the worksheet are flagged as uncalculated, so toggling the property to false and then back to true for all sheets in all open workbooks will cause the next calculation to be a full calculation. Dim oSht as worksheet for each oSht in Worksheets Application.calculate You can also use this method for a single worksheet to do a full sheet calculate. Full Calculation with Dependency RebuildIn Excel 2002 and later versions: Application.CalculateFullRebuild In prior versions of Excel you can achieve the same effect by switching
to manual, replacing all equal signs in formulae with equal signs
and then either switching back to automatic or doing a manual full
calculation (Ctrl/Alt/F9) Shift F9 – Sheet RecalculateWorksheets(“SheetName”).Calculate Range Calculate: see Calculation Methods for details and limitationsRange(“$a$3:$Z$9”).Calculate Evaluate Method:You can use the Evaluate method of the Application, Worksheet or Chartobject to return the result of calculating a string containing Names, Ranges and/or a Formulae to VBA without altering anything on a worksheet. Evaluate also enables you to get the results of a singlecell or multicell array formula passed to Evaluate as a string. There are two different syntaxes for Application.Evaluate, for example as Evaluate("SUM(A1:A20") or as [SUM(A1:A20)] The difference between using Application.Evaluate
and Worksheet.Evaluate is not spelled out in Excel's
help. Evaluate Method limitations:
You can bypass many of these limitations (at the cost of performance) by inserting the formula string into a worksheet cell and then reading the resulting cell value back into a VBA variable. Evaluate error handling:If Evaluate cannot evaluate the string it returns an error rather than raising an error, so to trap the error you need to assign the result to a variant and then check the variant using ISERROR. Public Function EVAL(theInput As Variant) As Variant The EVAL function can be called either as a UDF or from VBA. Properties controlling Calculation Options:Application.CalculationCan be set to xlCalculationAutomatic, xlCalculationManual or xlCalculationSemiAutomatic,
(in Excel95 these were xlAutomatic etc). Setting these properties sometimes fails with a 1004 error when the code is called from the event code of a Control Toolbox control. You can bypass this problem by setting the TakeFocusonClick property of the control button to false, or for other controls by preceding it by Activesheet.Activate. Excel's Initial Calculation SettingFastExcel V4 allows you to control Excel’s initial calculation sequence. Excel sets the initial calculation mode from the first nontemplate, nonaddin workbook opened, or created and calculated. This means that the calculation mode setting in subsequently opened workbooks will be ignored. If you need to override the way Excel initially sets the calculation mode you can set it yourself by creating a module in ThisWorkbook (doubleclick ThisWorkbook in the Project Explorer window in the VBE), and adding this code. This example sets calculation to Manual. Private Sub Workbook_Open() Unfortunately if calculation is set to Automatic when a workbook containing this code is opened, Excel will start the recalculation process before the Open event is executed. The only way I know of to avoid this is to open a dummy workbook with a Workbook open event which sets calculation to manual and then opens the real workbook. Application.CalculateBeforeSaveTrue or False. If calculation is Manual and CalculateBeforeSave is true when you Save the workbook it will be recalculated. Application.Iteration, MaxIterations and MaxChangeIf Application.Iteration is true and the workbook contains circular references, then calculation will iterate until either the number of iterations reaches MaxIterations or the largest change in cell value in the latest iteration is less than Maxchange. Workbook.PrecisionAsDisplayedIf True the workbook will be calculated using the number of decimal places in the formatting. Workbook.Date1904If true date calculations in the workbook will be based on 1904. Workbook.AcceptLabelsInFormulasIf true Excel will use "Natural Language" labels in formulae. For anything other than simple models this should be turned off. Worksheet.EnableCalculation; Preventing Specific Worksheets being CalculatedFastExcel Version 4 gives you an improved implementation of this facility, called Mixed Mode sheets, which allows you to control which sheets will be recalculated by which type of calculation event, and saves your settings with the workbook. Setting the Worksheet property EnableCalculation to False will prevent Excel from including the worksheet in a recalculation. This will stop the sheet being recalculated by Automatic Recalculation, F9, Ctrl/Alt/F9 and by Sheet.Calculate, Application.Calculate, Application.CalculateFull and Application.CalculateFullRebuild. The EnableCalculation property has no effect on Range.Calculate. If you use Range.Calculate on a formula on a worksheet that has EnableCalculation set to false then at the next recalculation Excel will recalculate any formulae on other sheets that are dependent on that formula. Setting enablecalculation to false and then back to true will flag all the formulae on the worksheet as uncalculated. If the calculation mode is Automatic a recalculation will be triggered. All calculation methods, including Sheet.Calculate but excluding Range.Calculate, will then calculate all the formulae on the sheet. You can use this method as a way of simulating Calculatefull at worksheet rather than workbook level. Note that Sheet.Calculate will not reset the uncalculated formulae flags, so two Sheet.Calculates in succession after toggling the EnableCalculation property will both do a Full Sheet Calculate. The property is reset to true when a workbook is opened. Calculation Events:Application or Workbook SheetCalculateThis event occurs after any worksheet is recalculated or changed chart data is replotted. Worksheet or Chart CalculateThis event occurs after the Worksheet is recalculated or the chart's changed data is replotted. Excel 2002/2003 OnlyExcel 2002/2003 considerably enhances your ability to control calculation from VBA: Adding specified cells to the calculation listYou can use Range.Dirty to add the
specified cells to the list of cells requiring calculation at the
next recalculation. Checking Calculation StatusThe Application.CalculationState property allows you to check if calculation has completed ( xlDone ), is pending ( xlPending) , or is in process ( xlCalculating ). The Pending state seems to correspond to the message Calculate in the statusbar: for workbooks with more than 65536 dependencies the CalculationState is always xlPending or xlCalculating. Interrupting CalculationYou can control the users ability to interrupt calculation by specifying what will interrupt the calculation. Application.CalculationInterruptKey= XlAnyKey  XLEscKey  XlNokey You can also control error handling of the interrupt (in Excel 97 onwards) using Application.EnableCancelKey= xlDisabled  xlErrorHandler  xlInterrupt Application.CheckAbortAccording to Help Application.Checkabort is supposed to stop recalculation except for a specified range. It does not do this. Apparently it throws a runtime error if there are pending abort messages in the app message queue (mouse clicks, esc key down etc). The parameter specifies whether to eat the message or leave it in the queue. The objective is to allow a a longrunning VBA calculation to be interrupted in the same way as an Excel calculation. Application.CheckAbort ([KeepAbort]) Excel 2007 OnlyControlling MultiThreaded CalculationYou can control Excel 2007's new multithreaded calculation from VBA using Application.MultiThreadedCalculation. Application.MultiThreadedCalculation.Enabled can be True or False to turn on/off multithreaded calculation. Application.MultiThreadedCalculation.ThreadMode
can be xlThreadModeAutomatic or xlThreadModeManual. Range Calculate and RangeCalculateRowMajorOrderExcel 2007 has two Range calculation methods. There is no standard user interface for running these calculation methods: you must call them by using VBA or some other programming language. These methods are useful when you want to calculate only a small block of cells while leaving all other formulas unchanged. Range.CalculateRange.Calculate calculates the range one row at a time, left to right and top to bottom, and then resolves all dependencies within the range. This is the same method that Excel 2002 and Excel 2003 use, except that it has been enhanced to handle iterative calculations in manual mode. Range.CalculateRowMajorOrderRange.CalculateRowMajorOrder calculates the range one row at a time, left to right and top to bottom, but it completely ignores all dependencies. This is the same method as Microsoft Excel 97 and Excel 2000 use. Because CalculateRowMajorOrder does not try to resolve dependencies, it is usually significantly faster than Range.Calculate. If you can ensure that any dependencies within a block of formulas always refer backward to cells to the left or above, the Range.CalculateRowMajorOrder can be the fastest calculation method in Excel on a single processor system. Workbook.ForceFullCalculationYou can set a new workbook property, Workbook.ForceFullCalculation , programmatically by using the Excel object model. When this property is set to True, dependencies are not loaded at open, the dependency dirty chain is not updated, and every calculation of the workbook is a full calculation rather than a recalculation. If you have a workbook that has so many complex dependencies that loading the dependencies at workbook open takes a long time or recalculation takes longer than full calculation, you can use this property to force Excel to skip loading the dependencies and always use full calculation. Also if making a change to the workbook takes a long time in manual mode because of the time taken to dirty all the dependencies of the cell being changed, then setting Workbook.ForceFullCalculation to True will eliminate the delay.



