#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

Community · Accepted Answer · 2017-05-23 12:22:30Z

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.

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jul 11, 2016 at 12:31

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Over a year ago

Thanks a ton!! After commenting "Worksheets("Sheet1").Cells(1,6).Value"..I got the result..

Collectives™ on Stack Overflow

#Value error while accessing user defined function in VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related