Create a gist now

Instantly share code, notes, and snippets.

@nalgeon /
Last active Apr 21, 2017

What would you like to do?
Пример вызова Подсказок из Excel

Как подключить пример

  1. Откройте Excel и перейдите в редактор Visual Basic (в ленте Разработчик > Visual Basic).
  2. Откройте список ссылок: в меню Tools > References...
  3. Отметьте пункты «Microsoft Scripting Runtime» и «Microsoft VBScript Regular Expressions 5.5».
  4. Нажмите OK.
  5. Откройте модуль Лист1.
  6. Скопируйте код из примера в модуль.
  7. Замените значение константы API_KEY с CHANGE_ME на ваш API-ключ.

Как работает пример

Введите адрес в ячейку A1, нажмите «энтер». В ячейке B1 появится адрес из Подсказок.

Private Const API_KEY = "CHANGE_ME"
Private pCachedRegexes As Dictionary
Public Function GetRegex( _
ByVal Pattern As String, _
Optional ByVal IgnoreCase As Boolean = True, _
Optional ByVal MultiLine As Boolean = True, _
Optional ByVal MatchGlobal As Boolean = True) As RegExp
' Ensure the dictionary has been initialized
If pCachedRegexes Is Nothing Then Set pCachedRegexes = New Dictionary
' Build the unique key for the regex: a combination
' of the boolean properties and the pattern itself
Dim rxKey As String
rxKey = IIf(IgnoreCase, "1", "0") & _
IIf(MultiLine, "1", "0") & _
IIf(MatchGlobal, "1", "0") & _
' If the RegExp object doesn't already exist, create it
If Not pCachedRegexes.Exists(rxKey) Then
Dim oRegExp As New RegExp
With oRegExp
.Pattern = Pattern
.IgnoreCase = IgnoreCase
.MultiLine = MultiLine
.Global = MatchGlobal
End With
Set pCachedRegexes(rxKey) = oRegExp
End If
' Fetch and return the pre-compiled RegExp object
Set GetRegex = pCachedRegexes(rxKey)
End Function
Function Utf8ToAscii(ByVal txt) As String
On Error Resume Next: Err.Clear
With CreateObject("ADODB.Stream")
.Type = 1
.Write txt
.Position = 0
.Type = 2
.Charset = "UTF-8"
Utf8ToAscii = .ReadText
End With
End Function
Function Suggest(ByVal name, ByVal query) As String
Dim http
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
timeout = 2000 'milliseconds
http.setTimeouts timeout, timeout, timeout, timeout
request = "{ ""query"": """ & query & """ }"
http.Open "POST", "" & name
http.setRequestHeader "Content-Type", "application/json"
http.setRequestHeader "Authorization", "Token " & API_KEY
http.send request
'Debug.Print http.responseText
Suggest = http.responseText
End Function
Function Guess(entity, query) As String
valuePattern = "^.""suggestions"":..""value"":""([^""]+)"".+$"
Dim valueRegex As RegExp
Set valueRegex = GetRegex(valuePattern)
resp = Suggest(entity, query)
'Debug.Print resp
If valueRegex.Test(resp) Then
result = valueRegex.Replace(resp, "$1")
result = query
End If
'Debug.Print result
Guess = result
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
If Target.Address = "$A$1" Then
Debug.Print "Source: " & Target.Value
suggested = Guess("address", Target.Value)
Debug.Print "Suggested: " & suggested
Range("B1").Value = suggested
End If
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment