Returning Two Dimensional Array from a Function

[mahmud1280]

Hey there! I am writing a function that gets an array from an Access Recordset and produces the result in excel. The pasting is not directly possible by using a excel function although that works for a Subroutine. So I am trying to return the output as an array from the function. Can anyone help me writing a function that returns two-dimensional array as an output? Thanks

[MrShorty]

Writing a UDF that returns an array is fairly simple. The main ideas:
1) The function needs to be dimensioned as a Variant (it will become a variant containing an array at the end).
2) You need to dimension an array to hold your result
3) At the end of the function, assign the function variant to be this result array.

The necessary code will look something like:

Please Login or Register  to view this content.

Like other built in array functions (LINEST(), FREQUENCY(), TRANSPOSE(), etc), this kind of UDF will be used by selecting the desired outputrange, entering the function, and confirming with ctrl-shift-enter. Like other array functions, it can be nested inside of an INDEX() function to access individual elements of the output array.

[mahmud1280]

Thank you MrShorty!! It worked for me I was also using the same method but I made some mistakes in forming the two dimensional array. In this method, I need to manually select the output range and put the formula. I would like it to populate the needed range without manually inserting the formula. Is it possible?

Thanks again!

[mahmud1280]

Thank you MrShorty!! It worked for me I was also using the same method but I made some mistakes in forming the two dimensional array. In this method, I need to manually select the output range and put the formula. I would like it to populate the needed range without manually inserting the formula. Is it possible?

Thanks again!

[MrShorty]

I need to manually select the output range and put the formula. I would like it to populate the needed range without manually inserting the formula. Is it possible?

Maybe I am just optimistic, but I tend to think almost anything is possible with enough time, effort, and ingenuity.

Before making recommendations, I would want to understand your requirements. Why is "manually inserting the formula" undesirable? What "run-time" things need to happen when entering the formula.

It seems to me that most solutions to "without manually inserting the formula" will involve VBA Sub procedures. Either you will need a VBA Sub procedure that enters the formula for you (.formulaarray property), or the VBA Sub procedure will need to receive the results of the Function procedure and write the results into the cell.

[mahmud1280]

It is desirable because I don't know before hand for how many rows I need to drag the formula. If I drag more than the resulting array I get #N/A Error. So, What I actually want is to put the formula in a single cell and I am hoping that populates the returning array. Thanks

[MrShorty]

So, What I actually want is to put the formula in a single cell and I am hoping that populates the returning array. Thanks

I can't think of a ready way to make any array function (native Excel or VBA UDF) operate in this way.

You can have a VBA Sub that will call the Function procedure, process the resulting array for size, and then write the results into the desired range.

If it is readily obvious from the input data, you can have a VBA Sub procedure that will analyze the input arguments and determine before calling the function how large the array should be. Then use the .FormulaArray property to write the function into the range needed.

You can select a larger array than you expect to need and hide the offending N/A's using conditional formatting.

You can code your UDF to fill in the "extra" cells with a suitable "this is outside of the input parameters" value that will be less offensive than the N/A. Empty string or 0 or other.

You can use the INDEX() and IFERROR() functions to hide the error values. =iferror(index(myudf(...),row,column),""). One thing I don't like about this approach is that you get a new function call for each element of the array, which can mean a lot of duplicated effort.

Which of these seems better than simply having the N/A's?

[mahmud1280]

I want a function to run this, I don't want to run a sub from the worksheet.

Before running the function I can not determine the size of the array. Is it possible to trigger a sub procedure once a function is closed?

I have seen some people using Application.ontime, but the code is really long and daunting

[MrShorty]

Is it possible to trigger a sub procedure once a function is closed?

The closest thing I can think of to this is a calculate event procedure. It seems that many examples of event procedures are for the change event, but the calculate event can also be quite useful: (http://www.cpearson.com/excel/Events.aspx
https://msdn.microsoft.com/EN-US/lib.../ff838823.aspx )

I would seem quite possible to code a calculate event procedure that will take your inputs, run the function, then write the results into the spreadsheet. It's not the kind of coding that I do, so I'm probably not much help with the details.