CODEWITHSUNDEEP
excelvbageocode
Bert Onstott
Bert Onstott

Reputation: 61

Function not showing up in list

Following these instructions: https://www.exceldemy.com/learn-excel/geocoding/ I created a new module and used Tools/References to check Microsoft XML, 6.0. Then I pasted this code into the new module:

Function Co_Ordinates(address As String) As String
Application.Caller.Font.ColorIndex = xlNone
Dim xDoc As New MSXML2.DOMDocument
xDoc.async = False
xDoc.Load ("https://nominatim.openstreetmap.org/search?format=xml&q=" + WorksheetFunction.EncodeURL(address))
If xDoc.parseError.ErrorCode <> 0 Then
    Application.Caller.Font.ColorIndex = vbErr
    Co_Ordinates = xDoc.parseError.reason
Else
    xDoc.SetProperty "SelectionLanguage", "XPath"
    Dim loc As MSXML2.IXMLDOMElement
    Set loc = xDoc.SelectSingleNode("/searchresults/place")
    If loc Is Nothing Then
        Application.Caller.Font.ColorIndex = vbErr
        NominatimGeocode = xDoc.XML
    Else
        Application.Caller.Font.ColorIndex = vbOK
        Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
    End If
End If
End Function

But when I select the top line in the function and click the run arrow, I get a pop-up list of macros that does not include Co_Ordinates. The image below shows what I see. Also, if I use that function in a cell (=Co_Ordinates(K12)), I get a Name? error. What am I doing wrong? Screen Capture

Upvotes: 0

Views: 58

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

  1. You cannot run from VBE (Visual Basic for Application Editor) a Function/Sub code with parameters. The respective procedure needs the respective parameter/s and it cannot run without it/them. It shows a list of possible Subs able to call it.

For instance, having the next simple testing function:

Function TestFunction(strMess As String) As String
  MsgBox strMess
End Function

and the next testing Sub calling it:

Sub CallTestFunction()
   TestFunction "Test message..."
End Sub

If you try running TestFunction from VBE, click inside and press F5, or the Run Sub arrow the window you mention is popping up. If you select there CallTestFunction the function will run using the parameter sent by the respective function.

You can not do it similarly in the exiting UDF function because it needs to be called from the sheet. Its first code line needs to extract the range where from it has been called Application.Caller.Font.ColorIndex = xlNone.

Unfortunately, you cannot simulate such a parameter...

  1. The function can be run in a way to work as it should only if the content of the cell used as parameter is a correct address, able to be processed by the site where to be processed, or the code itself returns an error!

In the code you show it is a constant not defined, able to produce the Value error on a code line appearing twice:

Application.Caller.Font.ColorIndex = vbErr

vbErr is not recognized as a color index constant...

Try using instead:

Application.Caller.Font.ColorIndex = vbYellow

and the error can be overpassed...

  1. If you want to see the respective address (if it is correctly spelled...) on the map, try the next code. Select the cell keeping the address and run it:
Sub GotoMap()
  Const URL As String = "https://nominatim.openstreetmap.org/ui/search.html?q="
  ThisWorkbook.FollowHyperlink URL & WorksheetFunction.EncodeURL(ActiveCell.Value)
End Sub

Upvotes: 1

Related Questions