Calculation Property of Application Object

     

    Calculation Methodologies:

  • Under the FORMULAS tab we can find CALCULATION group
  • Under the aforementioned group we can find CALCULATION OPTIONS

    •  

  • Automatic: Application calculates underlying cells data automatically based on formula
  • Manual: Application doesn't calculate automatically. User has to press CTRL + S

    •  

    From VBA Perspective:
  • First convert into Manual
  • To update the underling cells use any of below mentioned piece of code

    • ActiveWorkbook.Save

    Application.Calculate

    Application.Calculation = xlCalculationAutomatic

     

    Sub CalculateData()
    ActiveSheet.Range("D2:D6").ClearContents
    Application.Calculation = xlCalculationManual
    ActiveSheet.Range("D2").Value = "=B2*C2"
    ActiveSheet.Range("D2:D6").FillDown
    Application.Wait (Now + TimeValue("00:00:05"))
    'ActiveWorkbook.Save
    Application.Calculate
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Completed"
    End Sub

    Download The Workbook

     

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli