UDF Array Formula

[azrasound]

I am writing a UDF that accepts several ranges as parameters. I enter the UDF as an array formula across 14 cells (e.g., C10:P10) and when I first enter it, it seems it gets called 4 times? If I change a cell value that would trigger the UDF to execute, it sometimes gets called once, and sometimes more than once.

At any rate, the thing I am struggling with most right now is just figuring out why it is not behaving as expected. E.g., the first parameter that is passed in is the fixed range $C$1:$P$1, whose value is the simple concatenation of the values in rows 2 and 3. E.g., C2 = "The", C3="Word", so C1 = "TheWord".

When I debug the formula, though, as noted, it is called more than once. On top of that, the first time it gets called, the range $C$1:$P$1 has values, and then on the next iterative call, it has no values...they all show as Empty when I view the range parameter in the Watch window.

Needless to say, I have no idea what is happening or causing this behavior. I hope this made sense, and if not, perhaps I can create a simple example from scratch to demonstrate the issue.

 

[shg]

Post the code, and explain what it's suppoosed to do.

 

[RoryA]

Probably worth reading this: Excel User-Defined Functions - Decision Models

I suspect the third heading may apply.

 

[azrasound]

Well, I tried creating the issue from a new spreadsheet, but no go. The existing spreadsheet is too complex to describe/recreate.

Thanks for the link, RoryA. I had seen it before, and used some stuff there already to try and test things out (e.g., the IsCalced function). So I am not sure what is causing the behavior, but something is causing one of the parameters being passed in to be empty, and the IsCalced function isn't picking it up...I assume because it looks like the cells in the range don't have formulas.

The range being passed in is literally the concatenation of two text cells. I am not changing the text values anywhere, but if I change another cell that triggers the UDF, sometimes the range with the concatenated values gets passed in with no values, and its Formula property shows as being empty (when it is, in fact, something like =A1&B1).

This all seems vary vague I am sure. Unfortunately, I don't have much to go on. Maybe I need to do extensive checking of all the values being passed in and just exit the function if I think something is "broken"?

 

[azrasound]

Even more quirky, during the "bad recalculation", when that one range is passing in empty values, another range passed into the UDF, which is literally 4 cells with no formula, just manually entered text, when I look at that parameter in the Watch window, it shows the same text value for each cell (when they are all, in fact, different). Something must be corrupt, or I am missing something in how Excel behaves/calculates.

 

[snb_]

You may find an example here:

http://www.snb-vba.eu/bestanden/__array_function.xlsb

 

[RoryA]

I suggest you do as shg said and post the code, then explain exactly how you are calling it and what is in the cells being passed. If you can put a workbook somewhere (OneDrive / Dropbox / other), so much the better.