Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
543 views
in Technique[技术] by (71.8m points)

Excel VBA function works in Visual Basic, but fails in Worksheet

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...