Excel VBA pausing for Bloomberg

[mbush2]

Hi,

I am new to VBA and trying to create a macro where it will begin by inserting today's date into a cell, then pause for roughly 35 seconds while the massive correlation matrix pulls all the correlations of the portfolio in, then takes an output value that is generated in the workbook from the filled correlation matrix (correlation coefficient) and copy's and pastes it in a different cell. Then it repeats this process again using "Today's date - 1" (yesterday's date), waiting 35 seconds again and taking the new output value and copy and pasting it below the previous one.

I know I am not close but here is what I have for code thus far (which does not work properly). Thank you so much for any help.

Please Login or Register  to view this content.

[TKCZBW]

If the code all resides in VBA, then it should 'wait' for code to complete before executing the next line. You shouldn't need a wait.

If you want to insert one for whatever reason, you can use the following. Be sure to place the declarations before any subroutines in your code:

Please Login or Register  to view this content.

[mbush2]

The calculations reside outside of VBA in the actual workbook. They are a data pull in from bloomberg. It is a 75 x 75 matrix and each cell within it has a =BDP function pulling in correlation between to Stock Prices (that is why I takes 35 seconds for it to get pulled in). The pause I have built in right now Application.OnTime works well with waiting to run the second macro that does the copy and pasting but not sure how to get it all to fit together with a loop.

[TKCZBW]

THis is untested, but try dropping the 'DoEvents' command before the 'Next i'. This may help.

[Fotis1991]

Welcome to the forum.

We'd like to help you but first..

Pls take some minutes to read forum rules and specially-in this case- rule#3 and add code tags around your code.
Then anyone will be able to help you.