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

Question

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?

Answer 1

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.