CODEWITHSUNDEEP
vbaexcel
pgarzav32
pgarzav32

Reputation: 21

Recalculate ten cells every ten seconds

I am trying to use Google Maps API for around 2,000 locations. I was told the API only allows ten calculations every ten seconds hence we need the Excel sheet to do that. My code: 

'recalculate cells
Sub Recalculatecells()
Dim irow As Integer
Dim i As Integer
Dim j As Integer


'    Worksheets("Sheet2").Active
        Range("A1").Select
        Selection.End(xlDown).Select
        iRow = ActiveCell.Row

        Range("N1").Value = irow

        For i = 1 To iRow
            Range("N" & Str(i)).Formula = "=GetCoordinates(A" & i & ")"
        j = j + 1
        If j = 10 Then

            Application.Wait (Now + TimeValue("00:00:10"))
            j = 0
        Else
            j = j + 1
        End If

    Next i

End Sub

This is currently giving an error on the line: 

Range("N" & Str(i)).Formula= "=GetCoordinates(A" & i & ")"

(Note: =getcoordinates is the function to run the Maps API.)

I don't know why the error is there, once this error is removed would the code work?

Upvotes: 2

Views: 243

Answers (1)

user4039065
user4039065

Reputation:

The Application.OnTime method can call the routine that it is in.

Sub Recalculatecells()
    Application.StatusBar = "Calculating ..."
    Worksheets("Sheet2").Range("N1:N10").Calculate
    Application.StatusBar = vbNullString
    'put something here to optionally exit the timed loop
    Application.OnTime Now + TimeSerial(0, 0, 10), "Recalculatecells"
End Sub

I find this allows more freedom for working on the workbook instead of staying within the same sub procedure and mucking with a For ... Next increment counter.

Upvotes: 1

Related Questions