Hmmm passing it through a collection does seem to work, thanks! So that requires feeding in of the range, not very familiar with collections, so a few follow up questions!

I'm thinking the best option would then to have the array as a public variable and the populating via the above loop through collection, would this be best?

That formula requires the range be be specified, is their any easy method of selecting the correct relative range based from the cell the function is in a ubound of columns and rows of the array?

That formula runs every time the sheet calculates, pretty sure that wouldn't work for multiple instances of the same function in a sheet, for example you wouldn't be able to recalc all the cells through shift+F9 correct?