Como posso fazer um script esperar antes de continuar?

Question

Eu encontrei este fantástico script vba que pega um endereço e retorna a latitude e longitude usando a API de mapas do google. No entanto, o google tem recursos de limitação de taxa, e eu quero fazer isso para um grande número de endereços (aproximadamente 3 mil). O script, por outro lado, não tem limite de taxa, tornando-os bastante incompatíveis. É possível editar este script para que ele não passe para a próxima célula até que a solicitação anterior tenha sido concluída?

    Function MyGeocode(address As String) As String
  Dim strAddress As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String
  strAddress = URLEncode(address)
  'Assemble the query string
  strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
  strQuery = strQuery & "address=" & strAddress
  strQuery = strQuery & "&sensor=false"
  'define XML and HTTP components
  Dim googleResult As New MSXML2.DOMDocument60
  Dim googleService As New MSXML2.XMLHTTP60
  Dim oNodes As MSXML2.IXMLDOMNodeList
  Dim oNode As MSXML2.IXMLDOMNode
  'create HTTP request to query URL - make sure to have
  'that last "False" there for synchronous operation
  googleService.Open "GET", strQuery, False
  googleService.send
  googleResult.LoadXML (googleService.responseText)
  Set oNodes = googleResult.getElementsByTagName("geometry")
  If oNodes.Length = 1 Then
    For Each oNode In oNodes
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
      MyGeocode = strLatitude & "," & strLongitude
    Next oNode
  Else
    MyGeocode = "Not Found (try again, you may have done too many too fast)"
  End If
End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
  Dim StringLen As Long: StringLen = Len(StringVal)
  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String
    If SpaceAsPlus Then Space = "+" Else Space = "%20"
    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
        result(i) = Char
      Case 32
        result(i) = Space
      Case 0 To 15
        result(i) = "%0" & Hex(CharCode)
      Case Else
        result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

Qualquer ajuda seria muito apreciada, vocês são os melhores!

    

Answer 1

O código que você postou simplesmente informa ao Excel como consultar o Google Maps, ele não o faz realmente - você precisa chamar a função MyGeocode e informar ao Excel quais endereços você deseja consultar.

Primeiro pressione Alt + F11 para acessar o Visual Basic Editor (VBE). Clique com o botão direito no nome da pasta de trabalho no painel esquerdo e selecione Inserir > Módulo . Cole o VBA neste novo módulo.

Para executar este código, você precisa adicionar uma referência à biblioteca Microsoft XML, v6.0 . No VBE, clique em Ferramentas > Referências , role para baixo e marque a caixa ao lado da biblioteca. Clique em OK.

Agora, voltemos ao próprio Excel.

Digamos que você tenha um subconjunto de seus 3 milhões de endereços nas colunas A: D.

NacolunaE,concatenetodososseuscamposdeendereçoparaobteroendereçocompletoemumacélula:

=A2&" "&B2&" "&C2&" "&D2

Em seguida, na coluna F, podemos chamar as coordenadas do Google da seguinte forma:

=MyGeocode(E2)

Não há necessidade de dizer ao Excel para fazer uma pausa entre as chamadas - você controla isso pelo número de vezes que você informa ao Excel para executar =MyGeocode() - ou seja, quantas linhas no Excel você copia a fórmula.

Por segurança, faça isso em no máximo 2.500 linhas por dia para estar em conformidade com os T & Cs do Google (obrigado Seth), a menos que você esteja disposto a gastar algum dinheiro para aumentar o número de chamadas que você pode fazer para 100.000 por dia.

    

Answer 2

Você pode fazer o VBA parar um programa por, digamos, 5 segundos, como segue:

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

O que pode funcionar para você

    

Answer 3

Se você tiver três milhões de endereços, convém considerar um acesso comercial que permita usar o serviço sem limitação de taxa. Caso contrário, use algo como:

Msgbox "Click to go on", , "Example"

A partir dos Termos de Uso :

d. API Limitations

Google sets and enforces limits on your use of the APIs (e.g. limiting the number of API requests that you may make or the number of users you may serve), in our sole discretion. You agree to, and will not attempt to circumvent, such limitations documented with each API. If you would like to use any API beyond these limits, you must obtain Google's express consent (and Google may decline such request or condition acceptance on your agreement to additional terms and/or charges for that use). To seek such approval, contact the relevant Google API team for information (e.g. by using the Google developers console).