What is the Excel 2007 workbook name size limit? What for?

Question

The names of workbooks in Excel 2007 should be limited in size only to memory, but this does not seem to be the case. In the end, the array saved for the name will become so large that when you try to save you get (rephrased): "one or more formulas in this book exceed the limit of 8192 characters, save as a binary file."

OK, and then save it as a binary file ... but even here, the array can become large enough to make the file impossible.

What gives? How are names stored in Excel, what happens? Is this something special to install? Is there any way around this?

Try it yourself using the code below. It will work perfectly, and the name will be filled in correctly, but saving will give you doubtful errors. 3351 elements are too many, but 3350 saves only a 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 name collection is an Excel feature that has been around for a very long time. The formula length limit in Excel 2003 is 1024 (2 ^ 10), but has been extended for Excel 2007 to 8 192 (2 ^ 13).

These two articles describe the basic size limits for Excel 2003 and Excel 2007:

Excel 2003 Specifications and Limitations

Excel 2007 Specifications and Limitations

To solve this problem, I would look at the Excel.Worksheet.CustomProperties collection. I believe that the size of the Worksheet.CustomProperties element is limited only by memory. You will need to test this on your system and possibly in different versions of Excel, but I think you should easily save more than 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 a collection of names that can automatically convert your array to a string.