2

I am running a macro that opens a file referencing the one I am working in, pastes the relevant items as values into a separate sheet and makes a workbook out of that sheet.

The reason why I am doing this is because there are several thousand countifs, averageifs, and processor-intensive ilk.

The program runs from start to finish, just fine. The issue is that only a few of the items are calculated before the copy/paste operation and so I get a lot of #VALUE errors on the copy of the sheet with the formulas--even though the formulas are calculating correctly on further inspection.

I suspect the correct course of action is to delay the run until the sheet finishes calculating. Any and all help would be appreciated.

EDIT: I've tried all manner of application.calculations and nothing seems to be working. The links and items calculate normally if I open manually and let the processor do its thing. The only items that calculate are the ones that contain "COUNTA" somewhere in it. Is it possible that the application calculation methods don't work with Countifs and the like?

Community
  • 1
  • 1
Archaimot
  • 93
  • 12
  • As a quick test, have you tried turning off the spreadsheet autocalculation at the beginning of the macro, and turning it on at the end (or turn it off before you close your source data)? `Application.Calculation = xlCalculateManual`. Also, try turning off the screen updating - this might help. `Application.ScreenUpdating = False` – BruceWayne Apr 27 '15 at 20:50
  • Can you post you're code so we can see exactly what is going on. @user3578951 suggestion is probably the right answer, but could need some fine tuning based on exactly how you are doing this. – Degustaf Apr 27 '15 at 20:53
  • @user3578951 - As I interpret this question...OP has indicated the code copies before the sheet has finished calculating, how would turning calculation off solve the problem? – Davesexcel Apr 27 '15 at 21:18

2 Answers2

0

Shouldn't be that hard to do - the Worksheet object has a Calculate property that fires after it calculates. You can add a custom property to the worksheet that exposes a flag that you set after it is done calculating. In the worksheet code that has the time consuming calculation...

Option Explicit
Private can_copy As Boolean

Public Property Get CopyOK()
    CopyOK = can_copy
End Property

Private Sub Worksheet_Calculate()
    can_copy = True
End Sub

Private Sub Worksheet_Activate()
    can_copy = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    can_copy = False
End Sub

'For volitile functions.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    can_copy = False
End Sub

...and in the calling code:

Dim book As Workbook

Set book = Application.Workbooks.Open("C:\foobar.xlsm")
Do While Not book.Worksheets("Sheet1").CopyOK
    DoEvents
Loop

'Do your thing...

Note that I likely missed some events that would trigger a recalculation, but this should cover the scenario of just opening it.

Comintern
  • 21,855
  • 5
  • 33
  • 80
  • This did not work for me. I do not know why. It asked to update the links (I've tried a "yes" and a "no"), and now it's giving me value errors on the whole sheet. – Archaimot Apr 28 '15 at 14:56
0

So, I found a means for this to work: 

Do Until Application.CalculationState = xlDone
       Application.Calculate
        While Application.CalculationState <> xlDone
            MsgBox Application.CalculationState
            DoEvents
        Wend
    Loop

It was a solution I sort of applied from Siddharth Rout : Wait until Application.Calculate has finished

Thank you everyone for your help!

Community
  • 1
  • 1
Archaimot
  • 93
  • 12