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:

=Sheet1!$A$1:$B$4

However, in general, they are named formulas, which can involve functions, as in the ‘dynamic named range’ trick:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

or they can be literal values, such as an array:

={1,2,3;4,5,6;7,8,9}

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:

{=somename}

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.

### Like this:

Like Loading...

*Related*

Roy – try this:

Create a named array called named_array, then step through this little routine:

Sub test_na()

Dim VBAarray As Variant

VBAarray = [named_array]

End Sub

I don’t know that I’d want to defend this as being “best practice”, but it does work đź™‚

Doug – I use this regularly, not so much for array constants as for other named variables. This is shorthand notation for the EVALUATE function.

That’s interesting – not something I’ve used before.

According to the Help page (and a quick experiment), the square bracket form has to take a literal name: [myname], whereas the .Evaluate form takes a string, which can be a variable.

It also seems to be a way of running worksheet functions:

Evaluate(“SIN(45)”)

/Roy

Interesting thread about the use of Evaluate and [] here:

http://www.ozgrid.com/forum/showthread.php?t=52372

And come to think of it, I use it myself to evaluate formulas stored on the worksheet as text:

http://newtonexcelbach.wordpress.com/2008/04/22/evaluate-function/

http://newtonexcelbach.wordpress.com/2008/07/05/evaluate-function-update/

http://newtonexcelbach.wordpress.com/2009/03/10/evaluate-integrals-to-a-specified-tolerance/

Doug, that’s very interesting. By coincidence, I’ve started thinking about how one might use Excel to support Domain Specific Languages – possibly be using side-effecting functions in formulas. However, control over formula evaluation would need to be taken over (so that instructions are executed in a definite order). Holding formulas as text and using Evaluate might be the solution. I’ll post properly on this when I’ve got a bit further.

/Roy

One slight oddness is that if you Evaluate a formula containing user-defined functions (i.e. VBA) you can’t get into the functions in Debug mode: Step Into jumps right over the Evaluate statement.

Okay, guys, your starter for ten:

Here’s a simple UDF:

Public Function FUNCTEST() As String

FUNCTEST = “TEST evaluated”

MsgBox FUNCTEST ‘a little side-effect

End Function

In the Immediate window I enter:

?evaluate(“FUNCTEST()”)

or the square bracket form without quotes.

I get *two* MsgBoxes. Discuss.

/R

And weirder still:

Suppose the function displays its Caller:

Public Function FUNCTEST() As String

FUNCTEST = “TEST evaluated”

MsgBox “Caller ” & Application.Caller.Address

End Function

where we assume that Caller is a Range. Let’s say that cell E9 is the current selection on the worksheet. The first MsgBox says “Caller $A$1”, and the second says “Caller $E$9”.

What is going on here???

It’s not the Immediate window: the same thing happens when the function is evaluated from a Sub.