Pass multidimensional array into Excel UDF in VBA?

In general, you can't do this VBA is fine with nested arrays as values, but Excel just isn't. This makes sense, because Excel ultimately has to treat any values it gets from your UDFs as things that can go into the worksheet grid.

In general, you can't do this. VBA is fine with nested arrays as values, but Excel just isn't. This makes sense, because Excel ultimately has to treat any values it gets from your UDFs as things that can go into the worksheet grid.It would be nice if intermediate results didn't have to obey this restriction.

In your example above, you're not trying to put a jagged array into any cells; you just want to create it and pass it to 'TEST1'. Unfortunately, Excel just doesn't work that way.It evaluates each expression in a formula to a legal Excel value, and #VALUE! Is the catch-all for "not a legal value".

(There are other limitations to returned arrays besides jaggedness. For example, arrays over a certain size also result in a #VALUE! Error.

) Note that nested arrays that are all the same length can be passed back from UDFs: Public Function thisKindOfNestingWorks() thisKindOfNestingWorks = Array(Array(1, 2), Array(3, 4)) End Function This can be useful when you're building up some list-of-lists that you actually want coerced to a 2-D array.So, calling your ARY function above like this should work just fine: =ARY(ARY(A1, B1), ARY(C1, D1)) However, your TEST1 function would fail, since calling =TEST1(ARY(ARY(A1, B1), ARY(C1, D1))) would result in a 2-D array being passed to TEST1, not a 1-D array of 1-D arrays. You might also find this question and my answer to it to be helpful: Return a User Defined Data Type in an Excel Cell MUCH LATER EDIT: Incidentally, your 'ARY' function could be a lot shorter. This has nothing to do with your original question, but it's worth mentioning: Public Function arr(ParamArray args()) arr = args End Function There is an example of using it in this answer: Excel Select Case?

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.

Related Questions