Using dynamic arrays to populate named ranges for ListBox Controls is unstable - user error?.

Copper Contributor

Hi, this is my first post, and I've been pointed here by a MS365 support ambassador whose help I have exhausted.  Edited to include @JoeMcDaid Excel Program Manager as this seems to be your bag, Joe.  Thanks in advance.

I am building a sales tool in Excel that asks representatives a series of questions about a long list of products that are broken down into 3 filter questions.  It's essentially a cascade.

Each product has a category, an application and up to 5 discrete functions

Q1 chose category, Q2 choose an application (filtered by category), Q3 choose function(s) filtered by application.

I am using a series of UNIQUE() and FILTER() formulae which work perfectly when used to populate data validation lists using Named Ranges on the face of the worksheet.  No problem there.

However, when I use these same Named Ranges to populate ListFillRange in my ListBox controls on either a worksheet or a userform, the lists become unstable and return only partial lists to the control.  Sometimes they work OK other times they bomb fairly routinely.

This has resulted in 2 sessions out of several dozen where I get an Out of Memory message.  Am I overloading the PC memory with several dynamic array formulae in the controls - remember they operate fine every time on a native worksheet using # spill named ranges.  The problem only occurs with listbox controls on userforms or on the face of worksheets.

Any ideas on things to check, avoid, restrictions etc?

 

RoryForbes_0-1619625072103.png

This is the 3 dynamic array formulae working perfectly on the face of the worksheet.

If I use the same dynamic array formulae to populate listbox controls then they fail to display all the values. See next image.

RoryForbes_1-1619627193721.png

Here the ListBox for selection of Application has the same ListFillRange as the Data Validation on the earlier worksheet.  Sometimes it works, here only 3 of the values in the array are displayed - when there should be 5 per the first image.

RoryForbes_2-1619627798925.png

This image shows a further restriction in the displayed list values when same RowSource this time is used for the ListBox in the userform.  But the Userform below in the VBE shows with the populated list from the dynamic array named range.

RoryForbes_3-1619627910751.png

I fear I have spent too long looking at this now and there may be an answer somewhere obvious I am looking straight at.  Has anyone seen these symptoms before and come up with a resolution.

If no quick answer I am happy to post the file but will need to redact some company confidential information.

Many thanks in advance

 

Rory

 

Windows10 Pro 64 bit 8Gb RAM

Microsoft Excel for Microsoft 365 MSO (16.0.13901.20436) 64 bit

 

5 Replies
That does look rather odd. This is the first report I've had of this so, unfortunately, I don't have any quick answers. Would you mind crafting a simple workbook that reproduces the issue that you could share?
Thanks Joe,
I will work on that later today (Friday) UK-time and send over with a short description of the data model and the business logic that the workbook contains. Watch out for that being attached in my reply. Appreciate your time on this.
Thanks
Rory

@RoryForbes 
Update for @JoeMcDaid 

Hi Joe

Please see a simple workbook file attached showcasing the 2 areas where this inconsistency occurs.

  1. Start on the sheet "Proto" and use the Data Validation dropdown in cell Proto!I9 to choose one of 6 categories of product.  This is populated from a simple, static named range called "Category" (see sheet "Data model components").
  2. The Data Validation in cell Proto!I9 uses '=$I$11#' as the Source for the list validation.
    RoryForbes_0-1619881722153.png
  3. Choose 'Bakery' from cell Proto!I9.
  4. Note that my first dynamic array formula sits in cell Proto!B30 and this spills all the products from the table 'tblMasterList' that have the category of 'Bakery'.  This filters (by Category - cell B28) and sorts (by Application - column 4 in the array) all table rows In this dataset there are 6 rows returned. 

 

=IFERROR(SORT(FILTER(tblMasterList,tblMasterList[Category]=$I$9,""),4),"")​

This is the first dynamic array formula in the logic.

 

  • Note that my second dynamic array formula sits in cell Proto!K11 and this spills all the unique values found in column 4 of the first dynamic array formula into a new array.

 

=IFERROR(SORT(UNIQUE(INDEX(Proto!$B$30#,,4),FALSE,FALSE)),"")​

 

  • The Data Validation in cell Proto!K9 uses a named range as a source '=DynamicApplication'.  This named range Refers to '=Proto!$K$11#' as the source of data for the List.
  • Note the third dynamic array formula in cell Proto!B62.  This filters the tblMasterList once more but this time by the value of the selected Application.  This returns 3 rows from tblMasterList.
  • Choose 'Tortillas' from cell Proto!K11.
  •  
  • Note there are now 5 more dynamic array formulae in cells Proto!AF2, AG2, AH2, AI2 and AJ2.  These allow me to store the maximum of 5 function values that each combination of product/category/application may hold.  These formulae, example of cell AF2 below, allow me to sort the unique functions as returned by the dataset returned from the formula in cell Proto!B62.

 

=IFERROR(SORT(UNIQUE(INDEX($B$62#,,6),FALSE,FALSE),,-1),"")​

 

 

  • I then squeeze the values stored by the 5 array formula through some 6 steps of text manipulation from cell Proto!Z1 finishing at cell Proto!AC10 which gives me a unique list of the filtered functions available for the selected product/category/application combinations that I have just cascaded through.
  • The ninth (9th) dynamic array formula now gives me the source for my 3rd Data Validation List in cell Proto!M9

 

=IFERROR(UNIQUE(FILTER($AA$9:$AA$24,$AA$9:$AA$24<>"")),"")​

 

  • At cell Proto!M11 I then copy across the dynamic formula from cell Proto!AC10 as follows

 

=$AC$10#​

This is the 10th dynamic array formula on the worksheet.

 

  • The 11th dynamic array formula on the sheet is at cell Proto!M20 where I then present all the unique products that are returned by the dynamic array formula at cell Proto!B62 using this next formula.

 

=IFERROR(SORT(UNIQUE(INDEX($B$62#,,2),FALSE,FALSE)),"")​

 

  • That is all the dynamic array formulae.  You can now see that the 3 Data Validation lists in cells Proto!I9, K9 and M9 all properly provide the filtered and cascaded list of values through my questions 1,2 and 3.
  • So far so good.  This all works perfectly using the data validation lists on the face of the worksheet, if a little cumbersome to get to the cascaded filtered lists, but it works.
  • Now when I try to add in some UI/UX improvements by introducing ListBox controls on a new worksheet 'Clean' and on 'UserForm1' then the behaviour of the 3 filtered lists starts to get unreliable.
  • On sheet 'Clean' I have 4 listbox controls.  They all have named ranges as their ListFillRange properties.  The 3 'Dynamic...' named ranges use simple spill cells as the Refers to value.
    ChooseCategory = 'Category' Refers to simple static named range
    ListBox1 = 'DynamicApplication' Refers to: '=Proto!$K$11#'
    ListBox2 = 'DynamicFunctionality' Refers to: '=Proto!$AC$10#'
    ListBox3 = 'DynamicProduct' Refers to: '=Proto!$M$20#'
  • On 'UserForm1' I have similarly 4 listbox controls.  They all have named ranges as their RowSource property value as follows:
    ListBox1 = 'Category'
    ListBox2 = 'Dynamic Application'
    ListBox3 = 'DynamicFunctionality'
    ListBox4 = '=Proto!M20#' - note as a test to see if it made a difference compared with 'DynamicProduct'.
  • So overall I have well over 11 dynamic array formulae which function fine using DataValidation lists but start behaving badly at the introduction of ListBox controls.  I am either overusing them or there is a problem in the way that Excel assigns memory to these new formula when used with controls - maybe.

Over to you Joe, to take a look.  The workbook is as simple as I can make it to show all the work that is going on through each step through the cascade.

 

Many thanks

 

Rory

 

@RoryForbes 

Hi @JoeMcDaid 

Pulling my hair out now with this forum platform.  I've submitted my reply after a lot of effort which is a lengthy explainer about the workbook sample and it's now disappeared (my reply with workbook) from the thread.

I'm attaching the workbook again but I don't have time right now to re-write the explainer - I wish I'd done it in Word rather than type direct into the forum rich text editor; but ah well, lesson learned.

Here is the workbook.  It's a simple cascade from q1 to q3 but I have to put the data through lots of gyrations - I have counted 11 dynamic array formulae on the face of the Proto worksheet before we start thinking about these being repeated in Named Ranges to be recycled into ListBox controls.

Take a look and ask me any questions.

Much appreciated.

Rory

Hi Joe
Time marches on and I've partially resolved this issue by moving away from Userforms and presenting the entire UI within the Excel worksheet and using Data Validation to display the results of the dynamic array formulae - which works reliably every time whereas using the controls for ComboBox., Forms and ActiveX, do not work reliably.
I wonder if you've had any further opportunity to review this issue and identify a workaround or schedule a code change to deal with the unreliability.
I may need to move the UI into a userform for Mac compatibility with other elements but I am concerned that the dynamic array formulae and Form/ActiveX controls for ListFillRange and RowSource are not going to work.
Appreciate any aupdate so I can schedule appropriate mitigation work in the right direction.
Many thanks
Rory