Excel User Defined Functions cannot change other cells

You'd think this issue would be well documented.

I've been struggling for some time to get a user defined function written in Visual Basic for Applications to return an array of values to a range in my spreadsheet from which it was called. Turns out this is not possible.

It doesn't show any errors. If you trap an error using an "On Error" routine, it gives you a 1004 error "Application or object defined."

I only discovered by accident that UDFs can only return a value to the cell that called them.

Friggin hell. I have a lot of working around to do.