VBA Application OnTime

Question

hi, i have the following macros:

Sub refreshSheet()

Dim i As Integer

Workbooks("VBA Test").Activate

i = 3

For i = 3 To 4

    Sheets("Output").Cells(i, 1).Copy

    Sheets("Earning").Range("A1").PasteSpecial Paste:=xlPasteValues

    Sheets("Earning").Select

    Application.Run "RefreshEntireWorksheet"

    MsgBox "1,i=" & i

    Application.OnTime (Now + TimeValue("00:01:00")), "CopyCells"

Next i

End Sub

---

Sub CopyCells()

Dim Temp As Single

    MsgBox "2,i=" & i

    Workbooks("VBA Test").Activate

    Temp = Sheets("Earning").Cells(19, 22).Value

    Sheets("Output").Cells(i, 2).Value = Temp

... ...

End Sub

---

For this macro, i intended to run Sub refreshsheet, first i=3, and it comes to application.ontime. It will stop for 1 minute and then run sub CopyCells. After CopyCells finishes, it shall return to Sub refreshsheet, next i, then i=4 and run the whole process again. When CopyCells finishes at the 2nd time, given i=4, the whole process stops.

but, it does not run as what i want. I inserted the Msgbox and figure out when it runs to application. ontime, it stops for 1 minute, but it does not go to copycells, instead, it runs next i, which makes i=4, and runs from beginning again. it skips copycells. when it comes to application.ontime again when i=4, it stops for 1 minute. since i=4, it stops at end sub. and THEN, it starts to run CopyCells.

I guess the call sub goes wrong, but how?

any ideas? Appreciate.

Answer 1

Sorry for delay - this one has me a bit stumped. I'll keep looking at it - in the meantime hopefully someone else may chime in.

You can pass i to CopyCells this way (note mix of single and double quotes):

Application.OnTime (Now + TimeValue("00:01:00")), "'CopyCells """ & i & "'"

Sub CopyCells(i)

Answer 2

wow, the i passes. cool. Appreciate.

i m just curious, call sub can not be in a loop, that's pretty weired.

Thank you very much.

Answer 3

hi Murray, Appreciate for your help. 

well, actually, I m using Bloomberg, a financial information terminal. It has an add in to Excel, which can pull some data from its terminal and do some calculation in excel.

what i want to do is to copy a security ticker name to sheets"earning" A1, and then, sheets "earning" will retrieve data from bloomberg using its formula based on the security name in A1 and do some calculation thereafter in excel. and then macro will copy some calculation results in sheets earning to sheets output. But the point is it will take some time for excel to retrieve data from bloomberg, so i have to ask the macro to wait like 1 minute. I used .wait at beginning, but it seems that during the 1 minute, the macro keeps "running", which exhausts excel resources and actually it's not retrieving data. so after 1 minute, it all returns NA or something. but after 1 minute, macro will copy next security to do the next calculation for the next security (that's why i is increasing, which actually copies next security ticker to sheets earning). I checked with bloomberg and it gives the hints as follows. so it seems that during .ontime's 1minute, the macro is not "running", and the retrieving works fine. That is my following codes (using .ontime, removed the loop, and put i=3 in the copycells sub again, the following codes work fine): 

Sub refreshSheet()

Dim i As Integer

Workbooks("VBA Test").Activate

    i = 3

    Sheets("Output").Cells(i, 1).Copy

    Sheets("Earning").Range("A1").PasteSpecial Paste:=xlPasteValues

    Sheets("Earning").Select

    Application.Run "RefreshEntireWorksheet"

    MsgBox "1,i=" & i

    Application.OnTime (Now + TimeValue("00:00:45")), "CopyCells"

End Sub

---

Sub CopyCells()

Dim Temp As Single

    i = 3

    MsgBox "2,i=" & i

    Workbooks("VBA Test").Activate

    Temp = Sheets("Earning").Cells(19, 22).Value

    Sheets("Output").Cells(i, 2).Value = Temp

....

end sub

---

the hints from Bloomberg as follows for your reference:

Important note*:*

Due to the nature of Microsoft Excel, BDx & BLP static links willnot update while your VBA macro is running.  Due to this, you will not be able to issue a Refresh command within a macro and immediately process the results within the same macro.  The macro must exit before the cells will populate.

As a workaround to this behaviour, it is suggested that you implement a timer callback function which is triggered after submitting the refresh call.  This would wait some desired period of time before executing your subsequent calculation code.

The following code snippet demonstrates a call to refresh all workbooks, followed by a 10 second delay before calling the processSheet sub-routine.  This would be inserted into a VBA module:

Sub refreshSheet()     Application.Run "RefreshEntireWorksheet"     Application.OnTime (Now + TimeValue("00:00:10")), "processSheet" End Sub
Sub processSheet()     ' perform processing here    End Sub 	

Some requests may take longer than 10 seconds to return.  Within the processSheet() sub-routine, you could check to see whether cells you are interested in are still displaying "#N/A Requesting Data..." and, if so, start a new timer and exit the sub-routine.

So I guess the problem now is two questions:

1. How can i add the loop to it. i has to increase by step 1;
2. How to Pass i to the CopyCells sub; (I tried to add (i) to the copycells after .Ontime. no luck)

Appreciate for your patience.

Answer 4

Given you only want to wait a minute and proceed, there is no need to use OnTimer. You can just use a Wait statement and pass the value of i to the CopyCells routine.

Like so:

Sub refreshSheet()
    Dim i As Integer
    Workbooks("VBA Test").Activate
    'i = 3 ------> No need to initialise i - the For loop does it for you
    For i = 3 To 4
        Sheets("Output").Cells(i, 1).Copy
        Sheets("Earning").Range("A1").PasteSpecial Paste:=xlPasteValues
        Sheets("Earning").Select
        Application.Run "RefreshEntireWorksheet"
        MsgBox "1,i=" & i
        Application.Wait (Now + TimeValue("0:01:00"))
        CopyCells (i) '---->Pass i to the CopyCells sub
    Next i
End Sub

Sub CopyCells(i As Integer)
    'Dim Temp As Single
    MsgBox "2,i=" & i
    Workbooks("VBA Test").Activate
    'Temp = Sheets("Earning").Cells(19, 22).Value ---->No need to use a temp variable, see line below
    'Sheets("Output").Cells(i, 2).Value = Temp
    Sheets("Output").Cells(i, 2).Value = Sheets("Earning").Cells(19, 22).Value
End Sub