访问VBA中用户定义函数时的#Value错误 [英] #Value error while accessing user defined function in VBA

问题描述

当我从VBA访问函数时，代码工作正常，但是当我在excel单元格中调用相同的函数（ postalcode（23.0776120,72.6538530）时，我得到#Value错误。我的代码是：

 功能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 
 
设置xmlDoc =新的MSXML2.DOMDocument60 
 xmlDoc.async = False 
 xmlDoc.validateOnParse = False 
'使用XML字符串创建一个DOM，错误显示错误消息
如果不是xmlDoc .Load（https://maps.googleapis.com/maps/api/geocode/xml?latlng=& latlng）然后
 Err.Raise xmlDoc.parseError.ErrorCode，xmlDoc.parseError.reason 
 End If 
 
设置xEmpDetails = xmlDoc.DocumentElement 
设置xParent = xEmpDetails.FirstChild 
 
 Row = 1 
 Col = 1 
 
 Dim xmlNodeList As IXMLDOMNodeList 
 
设置xmlNodeList = xmlDoc.SelectNodes（// formatted_address）
 
工作表（Sheet1）。单元格（1，6）.Value = xmlNodeList.Item（0）.Text 
 Dim xyz As String 
 PostalCode = xmlNodeList.Item（0）.Text 
'PostalCode =找不到（再试一次，你可能做得太多太快）
 MsgBox PostalCode 
 
结束功能
  

解决方案

这是用户定义函数的一个文件限制，您不能通常在从工作表调用的UDF中操作或操作范围/工作表对象。虽然您可以执行大多数值/属性查询，但您无法更改环境：

由公式调用的用户定义函数工作表单元格不能
更改Microsoft Excel的环境。这意味着这样一个
函数不能执行以下任何操作：

• 在电子表格中插入，删除或格式化单元格。


• 更改其他单元格的值。


• 移动，重命名，删除或将工作表添加到工作簿。


• 更改任何环境选项，如计算模式或屏幕视图。


• 将名称添加到工作簿。设置属性或执行大多数方法。

我怀疑通过这个代码使用F8键VBE会识别错误，可能工作表（Sheet1）。单元格（1,6）.Value 。

这个限制背后的基本原理是防止无限循环/循环引用。

有办法规避这个限制。

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

解决方案

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.

这篇关于访问VBA中用户定义函数时的#Value错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！