问题描述
函数ProcessData()
Dim dataList()
dataList = Range(A1)。CurrentRegion
'TODO处理dataList
结束函数
当我在Visual Basic(运行/ F5)中测试它,它的效果很好;我的dataList构建没有问题。但是,如果我将工作表中的单元格设置为:
= ProcessData()
该功能在CurrentRegion步骤中静默失败。为什么会这样呢?如何纠正它?
如果从Excel单元格调用函数(即作为用户定义函数/ UDF),您只能通过参数访问通过函数传递的范围。任何对其他范围的访问(和 .CurrentRegion
是一个范围)将导致循环引用可能取消执行。
此外,在UDF中,您不能修改工作表上的任何内容 - 但只返回函数的结果!
有关更多详细信息,请查看此。
I'm trying to build a 2D array of data using "CurrentRegion".
Function ProcessData()
Dim dataList()
dataList = Range("A1").CurrentRegion
' TODO Process the dataList
End Function
When I test this within Visual Basic (Run/F5), it works great; my dataList is built with no problems. However, if I set a cell in my worksheet to:
= ProcessData()
the function silently fails at the "CurrentRegion" step. Why does this happen? How do I remedy it?
If you call a Function from an Excel cell (i.e. as an User-Defined-Function/UDF), you can only access the ranges that are handed to the function via parameters. Any access to other ranges (and .CurrentRegion
is a range) will result in a "Circular Reference" potential cancellation of the execution.
Also, in a UDF you cannot modify anything on the worksheet - but only return the result of function!
For further details, check out this link.
这篇关于Excel VBA函数在Visual Basic中工作,但在Worksheet中失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!