#Value error while accessing user defined function in VBA

Question

Code works fine when I access function from VBA however when I call the same function in excel cell (postalcode("23.0776120,72.6538530"), I get #Value error. My code is:

Function PostalCode(latlng As String) As String

Dim xmlDoc As MSXML2.DOMDocument60
Dim xEmpDetails As MSXML2.IXMLDOMNode
Dim xParent As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode
Dim Col, Row As Integer

Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.async = False
xmlDoc.validateOnParse = False
' use XML string to create a DOM, on error show error message
If Not xmlDoc.Load("https://maps.googleapis.com/maps/api/geocode/xml?latlng=" & latlng) Then
    Err.Raise xmlDoc.parseError.ErrorCode, , xmlDoc.parseError.reason
End If

Set xEmpDetails = xmlDoc.DocumentElement
Set xParent = xEmpDetails.FirstChild

Row = 1
Col = 1

Dim xmlNodeList As IXMLDOMNodeList

Set xmlNodeList = xmlDoc.SelectNodes("//formatted_address")

 Worksheets("Sheet1").Cells(1, 6).Value = xmlNodeList.Item(0).Text
 Dim xyz As String
 PostalCode = xmlNodeList.Item(0).Text
' PostalCode = "Not Found (try again, you may have done too many too fast)"
MsgBox PostalCode

End Function

Answer 1

It's a documented limitation of User-Defined Functions that you cannot generally operate on or manipulate range/worksheet objects within a UDF called from the worksheet. While you can do most value/property queries, you can't change the environment:

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

• Insert, delete, or format cells on the spreadsheet.
• Change another cell's value.
• Move, rename, delete, or add sheets to a workbook.
• Change any of the environment options, such as calculation mode or screen views.
• Add names to a workbook. Set properties or execute most methods.

I suspect that stepping through this code using F8 key in the VBE will identify the error, probably Worksheets("Sheet1").Cells(1,6).Value.

The basic rationale behind this limitation is to prevent infinite loops/circular references.

There are ways to circumvent this limitation.