UDF Array Formula

azrasound

New Member
Joined
Oct 26, 2014
Messages
6
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Post the code, and explain what it's suppoosed to do.
 
Upvote 0
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"?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top