Function not showing up in list

Question

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

• Bert Onstott: I was getting a compile error, which was caused by using XML v6. Changing it to v3 allowed it to compile. But is still doesn't let it run.
• bugdrown: Make sure you're providing a valid string argument when you call the function. What happens when you type ?Co_Ordinates("your valid string") in the immediate window?
• Tim Williams: Methods with parameters don't show up in the Alt+F8 list of macros.
• Ron Rosenfeld: Do you perhaps have module with the same name? And did you place it in a "regular" module? (not a worksheet or workbook module).
• FaneDuru: @bugdrown Calling the function in that way it will return an error because Application.Caller does not return a cell (having a Font ColorIndex). It is so designed to be called only from a cell (or many cells using it as array...). It cannot be (easily) run from VBE...

Answer 1

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...

2. 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...

3. 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

• FaneDuru: @Bert Onstott Didn't you find some time to read the above answer and test suggestions? If tested, didn't they work as you need?