Evaluating a string of an array

[Adamski]

Hello,

I want to use an array of sheet names in formulas. I had a custom vba function to loop through all my sheets and add the appropriate ones to an array and return it. That worked but was too slow since it was looping the sheets everytime the function was used.

So I wanted to have a global array of the sheet names, populated once on Workbook Open. Unfortunatly it appears you can't have global arrays.

So I thaught I'd have a global string of the sheet names eg:
"{Sheet1;Sheet2;Sheet4;Sheet5}"
or
"{'Sheet1';'Sheet2';'Sheet4';'Sheet5'}"
or similar.

How do I then use the string in a formula?

The INDIRECT function dosen't work
Is there a way to convert a string representing an array to an actual array.

eg.
=SUMPRODUCT({1;2;3;4;5;6;7;8;9})
works

=SUMPRODUCT("{1;2;3;4;5;6;7;8;9}")
fails with #VALUE!

=SUMPRODUCT(INDIRECT("{1;2;3;4;5;6;7;8;9}"))
fails with #REF!

I want to use a custom vba function to return the string
eg. something like (if the above actually worked!)
=SUMPRODUCT(INDIRECT(GetArray()))

Or perhaps there is a better solution?

Thanks

[Bob Phillips]

Why not just store the sheet names in a worksheet range, and use that range within your formulas.

[Adamski]

Yes, it's an option I considered - and still am.
I was just trying to avoid unnesessary sheets as I thaught there would be a way to to it with INDIRECT.
But I can hide the extra sheet so that's ok if there is no simple way to do arrays with INDIRECT.

Thanks

[Bob Phillips]

A proper array should work. A function defined something like

[vba]

Function GetArray() As Variant
Dim ary

ary = Array("Sheet1", "Sheet2")
GetArray = ary

End Function
[/vba]

and use like

=SUMPRODUCT(N(INDIRECT(getarray()&"!A1")))