I came across something recently that pointed out that you can save arrays of values (or indeed single values) as named constants – that is, using names. We usually think of names as referring to ranges, like this:
However, in general, they are named formulas, which can involve functions, as in the ‘dynamic named range’ trick:
or they can be literal values, such as an array:
Note the semicolons, making this a 3 x 3 array.
We could construct the array in our code, or get it from a worksheet range:
Dim arr As Variant arr = rng.Value Names.Add somename, arr
It’s not easy to find out the row/column sizes from the array constant, so we might want to save these as named values in their own right:
Names.Add somename + "Rows", UBound(arr, 1) Names.Add somename + "Cols", UBound(arr, 2)
The array constant can then be used to populate a worksheet range using an array formula:
However, getting the array constant back into a VBA array isn’t so easy. Unless there’s some trick that eludes me, the only way is to go via a worksheet range. Here’s a function that does this:
Function NamedArray(arrname As String) As Variant Dim nrows As Long Dim ncols As Long Dim rng As Range Dim arr As Variant nrows = Mid(Names(arrname + "Rows").Value, 2) 'lose the initial '=' ncols = Mid(Names(arrname + "Cols").Value, 2) Set rng = Range("DD1") 'somewhere remote Set rng = rng.Resize(nrows, ncols) rng.FormulaArray = "=" + arrname arr = rng.Value rng.Clear NamedArray = arr End Function
This uses the range’s FormulaArray property to suck in the values.
Having said that that’s the only way, an an alternative would be to parse the array constant and build the array in code, but that would be painful, and not easy to do generally for all types of value.
Is this actually worth it? I’m not sure. My preference is that data should be stored out on a worksheet (which could be hidden if desired), from which we can simply grab the Range.Value array. Also, there’s a limit on the size of the array constant (I assume lexically): it didn’t work (in xl2000) with approx. 8K rows.