How can I return multiple values from a VBA function to several cells in a table?
Scenario:
I have a function that takes a start date and end date, and returns the number of working days (excluding bank holidays and weekends).
I would like to extend it to also return the actual number of days, number of weekend days, number of bank holidays, as well as work days, to make it easier to see where the numbers come from.
So I've tweaked the return line to:
WorkDays = Array(TotalDays, TotalSats + TotalSuns, TotalBanks, TotalDays - TotalSats - TotalSuns - TotalBanks)
Obviously, those are my own variables!
If I use a regular worksheet then I can simply select the 4 cells in the row, stick in the function and CTRL-SHFT-ENTER amd autofill down. And all works fine.
However, if I try to do exactly the same in a table, I get an error when CSEing:
Multi-cell array formulas are not allowed in tables
And needless to say, I need to have my data in a table.
I'm using Excel 2007.
Does anyone know a workaround for this? Or indeed, why tables can't cope with these types of function?
Obviously I could split the function into 4 separate functions, but I'd prefer not to, partly to keep everything manageable, partly because it does a load of manual checking against bank holiday date serials so isn't the fastest as it is, and partly to extend my understanding of tables and VBA.
Thank you
Bookmarks