Storing Arrays as Named Constants

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

    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.


8 Responses to “Storing Arrays as Named Constants”

  1. 1 dougaj4 July 26, 2009 at 1:02 pm

    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 🙂

  2. 2 Alex J July 26, 2009 at 3:30 pm

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

  3. 3 Roy MacLean July 27, 2009 at 8:44 am

    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:


  4. 5 Roy MacLean July 27, 2009 at 1:17 pm

    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.


  5. 6 Roy MacLean July 27, 2009 at 2:24 pm

    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.

  6. 7 Roy MacLean July 27, 2009 at 4:06 pm

    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:


    or the square bracket form without quotes.

    I get *two* MsgBoxes. Discuss.


  7. 8 Roy MacLean July 27, 2009 at 4:29 pm

    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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

July 2009
« Jun   Aug »

%d bloggers like this: