什么是Excel 2007工作簿名称大小限制？为什么？ [英] What is Excel 2007 workbook Name size limit? Why?

问题描述

Excel 2007中的工作簿名称的大小应该仅限于内存，但情况并非如此。最终，保存到名称中的数组将变得足够大，当您尝试保存时，获得（释义）：本工作簿中的一个或多个公式大于8192个字符数，请保存为二进制文件。 / p>

确定，所以然后将其保存为二进制文件格式...但即使在这里，一个数组可以变得足够大，以保存文件不可能。

什么给了？如何在Excel中存储这些名称？这是安装特别的吗？有没有办法？

使用下面的代码自己尝试一下。它将运行完美，名称将被正确填充，但保存将给你女性错误。 3351元素太多，但3350保存正常：

  Public Sub TestNameLimits（）
 Dim v As Variant 
 ReDim v（1）
 
 Dim index As Integer 
 
对于索引= 1到3351 
 ReDim保存v（索引）
v（索引） =AAAA
下一个
 
调用Application.Names.Add（NameLimit，v）
 
 End Sub 
  

解决方案

名称集合是Excel的一个功能，已经存在了很长时间。 Excel 2003中的公式长度限制为1,024（2 ^ 10），但是将Excel 2007扩展为8,192（2 ^ 13）。

这两篇文章描述了主要大小Excel 2003和Excel 2007的限制：

Excel 2003规范和限制

Excel 2007规范和限制

为了解决这个问题，我将看看 Excel.Worksheet.CustomProperties 集合。我相信Worksheet.CustomProperties项目大小仅受内存限制。您将不得不在您的系统上进行测试，也可能在各种版本的Excel中进行测试，但我认为您应该能够轻松地保存超过10,000,000个字符。

但是，使用Worksheet.CustomProperties集合时，您将负责将数组自动转换为字符串，而不必像数组一样将数组自动转换为字符串。

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

解决方案

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.

这篇关于什么是Excel 2007工作簿名称大小限制？为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！