VBA Excel 宏中 Split() 的字符串长度限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12026552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
String length limit for Split() in VBA Excel macro
提问by pixL
I'm currently rebuilding an excel macro which parses a text file and fills cells in an excel sheet according to a certain ruleset. My new version is supposed to handle files which extends over a much larger span of time and on a day-to-day basis rather than a summed up result for a week. However, it seems that ever since I got these larger files the split command won't return anything.
我目前正在重建一个 excel 宏，它解析文本文件并根据某个规则集填充 excel 工作表中的单元格。我的新版本应该处理跨越更长时间和日常的文件，而不是一周的总结结果。然而，似乎自从我得到这些更大的文件后，split 命令就不会返回任何东西。
rawDataArray = Split(rawData, Chr$(10))
While stepping through I can clearly see that the rawData string contains data which I loaded from the source text file, I also opened the file in a hex editor to verify that chr(10) exists as they should. After the operation has been executed the rawDataArray is still empty though.
在逐步执行时，我可以清楚地看到 rawData 字符串包含我从源文本文件加载的数据，我还在十六进制编辑器中打开了该文件以验证 chr(10) 是否存在。操作执行后，rawDataArray 仍然是空的。
The previous version only handled text files which were a couple of thousand characters at the most and it worked flawlessly. The current files are approximately 500.000 characters long, and this is the only (well, first ;)) line that won't work, which brought me into the idea that maybe there's an upper limit to how long strings the split command can handle. Could this be true? And if so, how long is it?
以前的版本只处理最多几千个字符的文本文件，并且运行完美。当前文件大约有 500.000 个字符长，这是唯一（好吧，第一个 ;））行不起作用，这让我想到 split 命令可以处理的字符串长度可能有上限。这是真的吗？如果是这样，多长时间？
Could it also be because we switched from Office 2007 to Office 2010 at my office? I don't know if the vbscript versions act the same and I guess this could be a factor too.
也可能是因为我们在我的办公室从 Office 2007 切换到了 Office 2010？我不知道 vbscript 版本的行为是否相同，我想这也可能是一个因素。
Thanks a lot in advance!
非常感谢！
EDIT: I'm not sure if it's customary to answer your own posts with the solution but, here goes.
编辑：我不确定用解决方案回答你自己的帖子是否习惯，但是，这里是。
It seems the split command can't handle such big strings, I tried to reduce the file to ~7k chars and that worked. I haven't found the exact number yet but this seems to be the reason at least. Also, thank you Avner for the correction regarding vbscript vs. VBA, I'm self-taught when it comes to programming/scripting so now I learned something new!
似乎 split 命令无法处理这么大的字符串，我试图将文件减少到 ~7k 个字符并且有效。我还没有找到确切的数字，但至少这似乎是原因。另外，感谢 Avner 对 vbscript 与 VBA 的更正，我在编程/脚本方面是自学的，所以现在我学到了一些新东西！
Thanks Avner for the correction regarding
感谢 Avner 的更正
回答by Avner Shahar-Kashtan
An Excel macro is written in VBA, not VBScript. VBA's limitations shouldn't be a problem for you. If anything, your computer's RAM might be a limit. This is from Office 2010's VBA documentation, and this is true for Office 2007 as well:
Excel 宏是用 VBA 编写的，而不是 VBScript。VBA 的限制对您来说应该不是问题。如果有的话，您的计算机的 RAM 可能是一个限制。这是来自 Office 2010 的VBA 文档，Office 2007 也是如此：
There are two kinds of strings: variable-length and fixed-length strings.
- A variable-length string can contain up to approximately 2 billion (2^31) characters.
- A fixed-length string can contain 1 to approximately 64K (2^16) characters.
有两种字符串：可变长度和固定长度的字符串。
- 可变长度字符串最多可以包含大约 20 亿 (2^31) 个字符。
- 固定长度的字符串可以包含 1 到大约 64K (2^16) 个字符。
I'm guessing the problem is that the Splitmethod doesn't handle huge strings very well. Perhaps it would be best to use Midin a loop to split it into chunks, and call Splitseparately, though that has other problems.
我猜问题是该Split方法不能很好地处理大字符串。也许最好Mid在循环中使用将其拆分为多个块，然后Split单独调用，尽管这还有其他问题。
回答by xll
Split can handle string of any size. I just have checked the split function with a simple sub (in Excel 2010, but I think 2007 should work the same)
Split 可以处理任何大小的字符串。我刚刚用一个简单的子检查了 split 函数（在 Excel 2010 中，但我认为 2007 应该工作相同）
Public Sub TestSplit()
Dim Count As Long
Count = 1000000
Dim x
ReDim x(Count)
Dim i As Long
For i = 1 To Count
x(i) = "My String " & i
Next
Dim RawString As String
RawString = Join(x, Chr(10))
Debug.Print "RawString length = " & Len(RawString)
Dim SplitArray
SplitArray = Split(RawString, Chr(10))
Debug.Assert UBound(SplitArray) = Count
End Sub
Output:
RawString length = 16888896
There must be something with reading your source file. Are you sure there are chr(10) characters in your rawData variable? I suggest you to check it with instr function:
阅读您的源文件必须有一些东西。您确定 rawData 变量中有 chr(10) 个字符吗？我建议你用 instr 函数检查它：
InStr(RawString, Chr(10))
It should be greater than 0. But even in this case rawDataArray should contain one element with the whole rawData string.
它应该大于 0。但即使在这种情况下，rawDataArray 也应该包含一个元素和整个 rawData 字符串。
