What is Excel 2007 workbook Name size limit? Why?

Question

Workbook names in Excel 2007 are supposed to be limited in size only by memory, but this appears not to be the case. Eventually, an array saved to a name will get big enough that when you try to save you get (paraphrased): "one or more of the formulas in this workbook is larger than the 8192 character limit, please save as binary file".

OK, so then save it to a binary file format... but even here, an array can get big enough to make saving the file impossible.

What gives? How are names being stored within Excel that this occurs? Is this something particular to the installation? Is there a way around it?

Try it out yourself with the code below. It will run perfectly and the name will be properly populated, but saving will give you somenasty errors. 3351 elements is too many but 3350 saves just fine:

Public Sub TestNameLimits()
    Dim v As Variant
    ReDim v(1)

    Dim index As Integer

    For index = 1 To 3351
        ReDim Preserve v(index)
        v(index) = "AAAA"
    Next

    Call Application.Names.Add("NameLimit", v)

End Sub

Answer 1

The Names collection is a feature of Excel that has been around for a very long time. The formula length limitation in Excel 2003 is 1,024 (2^10) but was expanded for Excel 2007 to 8,192 (2^13).

These two articles describe the main size limitations for Excel 2003 and Excel 2007:

Excel 2003 specifications and limits

Excel 2007 specifications and limits

To solve this, I would have a look at the Excel.Worksheet.CustomProperties collection. I believe that the Worksheet.CustomProperties item size is limited only by memory. You will have to test this on your system, and probably in various versions of Excel as well, but I think you should be easily able to save well over a 10,000,000 characters.

However, when using the Worksheet.CustomProperties collection, you will be responsible for converting your array to and from a string yourself, unlike the Names collection, which can convert your array to a string automatically.