PDA

View Full Version : Evaluating a string of an array



Adamski
11-12-2009, 03:18 AM
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
11-12-2009, 03:29 AM
Why not just store the sheet names in a worksheet range, and use that range within your formulas.

Adamski
11-12-2009, 03:50 AM
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
11-12-2009, 04:28 AM
A proper array should work. A function defined something like



Function GetArray() As Variant
Dim ary

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

End Function


and use like

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