0

Please advise on the following:

I have a code that should refresh formulas in my workbook and then copy the results as values to the other sheet. The problem is that the code flies right through everything and copies data even before the refresh is completed (refresh takes approx. 10-15 seconds).

I have used the below (and its varieties) but it didn't do anything to slow it down: 

Application.Wait (Now + TimeValue("0:00:15"))

Do I have to do a loop that would check the cell content and execute "copy" part once it identifies the cell is updated?

Sub Excel_VBA_Timer_Event1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim V As Workbook: Dim x, y, z As Worksheet
Set V = ThisWorkbook: Set x = V.Sheets(1): Set y = V.Sheets(2): Set z = V.Sheets(3)
z.Columns("A:O").EntireColumn.Delete

Aplication.Run "RefreshAllWorkbooks"

'WAIT HERE for approx 15 seonds
'At the moment the code flies through till the end before Bloomberg formulas are refreshed


y.Columns("C:M").Copy
z.Range("A1").PasteSpecial (xlPasteValues): z.Range("A1").PasteSpecial (xlPasteFormats)

z.Activate: z.Cells(1, 1).Select
Application.CutCopyMode = False

MsgBox "Done"

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Alien_Explorer
  • 859
  • 1
  • 9
  • 22
  • VBA is single threaded. By all means `Application.Run` is expected to return the value returned by the called macro. What is "RefreshAllWorkbooks"? Is it a function that you can edit? If it is, I would try to capture the return value and examine it in a loop. Like a do until return value is set from called function. – HackSlash Aug 16 '19 at 15:30
  • Presumably "refresh all workbooks" is refreshing querytables "in the background". If possible, make the refresh *not* run in the background (i.e. synchronously) or make a new not-background-refresh `RefreshAllWorkbooksSynchronously` version of the macro so that it becomes a synchronous/blocking operation; then the next instruction will only execute after the refresh has completed. – Mathieu Guindon Aug 16 '19 at 15:55
  • Alternatively, use `WithEvents` object variables for a more fine-tuned handling of the refreshing operations, but then that's a much more involved solution with a massively different paradigm (event-driven vs plain procedural) – Mathieu Guindon Aug 16 '19 at 15:57
  • [This](https://stackoverflow.com/a/12855591/1188513) seems a viable solution, and [this](https://stackoverflow.com/a/26780134/1188513) could be worth a shot. – Mathieu Guindon Aug 16 '19 at 15:59

0 Answers0