none
Bypass Clipoard

    Question

  • I have some simple code that works fine in 2003, but not 2007 as follows

    ThisWorkbook.Sheets("Sheet2").Range("D4:D100003").ClearContents

    'The folowing line changes the col_index_num on 100,000 vlookup formulas.  Takes a minute or so to calculate

    ThisWorkbook.Sheets("Sheet2").Range("B2") = 3

    'The following line executes prior to the above line calculation completing

    ThisWorkbook.Sheets("Sheet2").Range("D4:D100003").Value = ThisWorkbook.Sheets("Sheet2").Range("B4:B100003").Value

    'If I replace the above line with the following, it works fine

    ThisWorkbook.Sheets("Sheet2").Range("b4:B100003").Copy
    ThisWorkbook.Sheets("Sheet2").Range("D4:D100003").PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False

    Why does vba not wait for the calculation to complete when  I attempt to bypass the clipboard?

    Tuesday, December 11, 2012 10:41 PM

Answers

  • Thank you Jim.  It seems my issue was related to calculation being interrupted.  There is a property in vba Application.CalculationInterruptKey which can have a value of xlAnyKey, xlEscKey, or xlNoKey.  Default is xlAnyKey.  I changed to xlEscKey and it processed correctly. 

    The Copy PasteSpecial, irregardless of interruption, will wait for calculations to complete.  .Value = .Value does not.  I'm sure other functions do not wait as well, so I will set Application.CalculationInterruptKey = xlEscKey for all my programs, then back to xlAnyKey at the end.  I choose xlEscKey, so I can still interrupt calculations in the event Excel is not set back to xlAnyKey.

    This also seems much more sensitive in 2007 versus 2003

    Wednesday, December 12, 2012 4:16 PM

All replies

  • I haven't seen this problem but I take your word for it happening. I wonder if inserting this code before the Value = Value helps? While Application.CalculationState = xlCalculating Wend
    Wednesday, December 12, 2012 1:35 PM
  • Thank you Jim.  It seems my issue was related to calculation being interrupted.  There is a property in vba Application.CalculationInterruptKey which can have a value of xlAnyKey, xlEscKey, or xlNoKey.  Default is xlAnyKey.  I changed to xlEscKey and it processed correctly. 

    The Copy PasteSpecial, irregardless of interruption, will wait for calculations to complete.  .Value = .Value does not.  I'm sure other functions do not wait as well, so I will set Application.CalculationInterruptKey = xlEscKey for all my programs, then back to xlAnyKey at the end.  I choose xlEscKey, so I can still interrupt calculations in the event Excel is not set back to xlAnyKey.

    This also seems much more sensitive in 2007 versus 2003

    Wednesday, December 12, 2012 4:16 PM