VBA to ensure that workbook/worksheet(s) are calculated fully?

[hellfire45]

Hi guys. I have a monster size automation I'm building and I need to ensure at certain times that the entire workbook is calculated and/or various worksheets are calculated before the VBA proceeds.

Is there some vba code I can add to tell the workbook to check to see if the entire workbook is calculated and if not, recalculate it until it is in a fully calculated state?

Likewise, a similar this for individual worksheets? I found the code below in a different workbook but I'm not sure what this does.

If Not Application.CalculationState = xlDone Then                DoEvents
    End If

Thanks so much!

 

[hellfire45]

bump

 

[bhos123]

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If Not Application.CalculationState = xlDone Then
DoEvents
End If
Next


add this at the start of your code.

 

[hellfire45]

The automation is giant and needs to calculate at different places. For example, one of the processes has me creating customer worksheets by looping through a header name that updates all formulae. Would you recommend I place that piece of code every time I need to ensure full calculation throughout the dozen or so modules?

 

[bhos123]

if the cells are linked with formulas, doesn't the workbook update automatically? I guess it should.

 

[hellfire45]

if the cells are linked with formulas, doesn't the workbook update automatically? I guess it should.

It doesn't. The workbook is massive. So each full workbook calculation takes about 15 seconds. I have the calculations set to manual otherwise it'd take 10 hours to run it. So in the example of the customer worksheets. I basically would Change the customer name in the header which is tied to all formulas. Then I calculate it, then I spit out a copy. But I need to make sure that the workbook is fully calculated for all of this. I think sometimes the code goes faster than the computer can process and stuff gets missed. I've seen instances of workbooks not calculating fully during huge VBA runs. It's scary not really knowing, especially when jobs hang in the balance. I'd see below for example. It's about half the module.

COM.Activate'Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.CalculateBeforeSave = False
Calculate


'Resets the hidden columns and rows as well as filtering on the commit worksheet
hideloopstart = Application.Match("MTM", COM.Rows("2:2"), 0)
hideloopend = Application.Match("END", COM.Rows("1:1"), 0)
If COM.FilterMode = True Then COM.ShowAllData
COM.Rows("1:10000").EntireRow.Hidden = False
COM.Columns("M:BM").EntireColumn.Hidden = False


'runs through a loop and hides unneeded columns
For xcol = hideloopstart To hideloopend
            columnval = COM.Cells(Application.Match("MTM", COM.Columns(1), 0), xcol).value
            If columnval = "" Then
                        COM.Columns(xcol).EntireColumn.Hidden = True
            End If
Next xcol




'clears contents from the aggregate commit worksheet and creates new headers with correct columns and syntax
agg.Cells.ClearContents
agg.Rows("1:10000").EntireRow.Hidden = False
Set rng = COM.Range("$A$1:$BM$2").SpecialCells(xlCellTypeVisible)
rng.Copy agg.Range("A1")
agg.Range("E1").value = "Aggregate"


'begins the loop that cylces through all the vendors with manufacturer stores
reportcolumn = Application.Match("Customer Scorecard", ref.Rows("1:1"), 0)
lastreport = ref.Columns(reportcolumn).Find("LASTRPT", , , , xlRows, xlPrevious).Row - 1
lastr = COM.Columns(1).Find("*", , xlValues, , xlRows, xlPrevious).Row
For xlist = Application.Match("Customer Name", ref.Columns(1), 0) + 1 To lastreport
            COM.Activate
            'percent = Format(xlist / lastreport, "Percent")
            'Application.StatusBar = "Phase 5: Generating Customer Report " & percent & " of Reports Complete..."
            Application.StatusBar = "Phase 5: Generating Customer Report " & xlist - 2 & " of " & lastreport - 2 & " Reports Complete..."
            
            'resets all filtering in all of the meaningful worksheets.  This incldues the main report sheet which might undergo some truncating via hiding rows lateron.
            'resets the manufacturer store to the next in the rotation of vendors
            customername = ref.Cells(xlist, reportcolumn).value
            z = Application.Match("*", COM.Rows("1:1"), 0)
            COM.Cells(1, z).value = customername
            COM.Calculate

 

[hellfire45]

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If Not Application.CalculationState = xlDone Then
DoEvents
End If
Next


add this at the start of your code.

So just for clarity, this piece of code will make sure that the entire workbook is calculated when it runs? Does DOEvents tell the VBA to recalculate if it isn't fully calculated?

 

[bhos123]

I believe Yes,

similar post: http://www.mrexcel.com/forum/excel-questions/484021-start-process-wait-until-finished-resume.html