UDF Array Formula

azrasound

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

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Post the code, and explain what it's suppoosed to do.
 

azrasound

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

New Member
Joined
Oct 26, 2014
Messages
3

ADVERTISEMENT

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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,111
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

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
Top