Excel VBA：当将Variant数组返回到所选范围时，需要解决255转移字符限制问题 [英] Excel VBA: Need Workaround for 255 Transpose Character Limit When Returning Variant Array to Selected Range
问题描述
我正在努力解决一个涉及Excel 255字符限制的常见问题。尝试将一个变量数组从一个函数返回到工作表上的选定范围时，我遇到错误。 当函数返回的数组中的每个单元格都不超过255个字符时，它们将按照它们的顺序发布到表格中：一个元素出现在所选范围内的每个单元格中。但是，如果我的返回变量数组中的任何元素超过255个字符，我得到一个值！错误。这些错误是坏的，因为我需要我的长元素，并希望将数据保持在一起！
I am struggling with a common problem involving an apparent Excel 255-character-limit. I encounter an error when attempting to return a variant-array from a Function to the selected range on the worksheet. When each of the cells in the Function's returning array are under 255 characters, they post to the sheet just as they should: one element appears in each cell within the selected range. However, if any element in my returning variant array is longer than 255 characters I get a Value! error. These errors are bad because I need my long elements and want to keep the data together!
这个问题的版本一再出现再次在许多论坛中，我可以找到一个清晰简单的多用途解决方案，用于将阵列单元格超过255个字符时返回到所选范围（不一定包含公式）。我的最大元素在1000，但是如果解决方案可以容纳多达2000个字符的元素，那将会更好。
Versions of this problem appear over and over again in many forums, yet I am able to find a clear simple, all-purpose solution for returning variant arrays to the selected range (not necessarily containing formulas) when the array cells exceed 255 characters. My largest elements are around 1000, but it would be better if the solution could accommodate elements up to 2000 characters.
最好我希望通过一个函数或其他代码行来实现这可以添加到我的函数（而不是一个子例程）。我想要避免子程序的原因：我不想硬编码任何范围。我希望这是灵活的，输出位置是根据我当前的选择动态地。
Preferably, I want this to be implemented with a function, or lines of additional code which can be added to my function (not a subroutine). My reason for wanting to avoid subroutines: I do not want to have to hard-code any ranges. I want this to be flexible and for the output location to be dynamically based on my current selection.
请，如果你可以帮助找到一种方法来生产函数，它将Variant Array作为输入，并保持所需的数组：单元格1：1的关系，我非常感激。
所以此功能与短单元格工作：
So this function with short cells works:
Function WriteUnder255Cells()
Dim myArray(3) As Variant 'this the variant array I will attempt to write
' Here I fill each element with less than 255 characters
' it should output them if you call the function properly.
myArray(0) = "dog"
myArray(1) = "cat"
myArray(2) = "bird"
myArray(3) = "fly"
WriteUnder255Cells = Application.Transpose(myArray())
End Function
但是这个功能，单元格超过255将不会输出。
But this fuction, with cells exceeding 255 will not output.
Function WriteOver255Cells()
Dim myArray(3) As Variant 'this the variant array I will attempt to write
' Here I fill each element with more than 255 characters
' exceeding the 255-character limit causes the VALUE! errors when you output them
myArray(0) = "ThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelaxydog"
myArray(1) = "ThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydog"
myArray(2) = "ThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydog"
myArray(3) = "ThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydogThequickbrownfoxjumpedoverthelazydog"
WriteOver255Cells = Application.Transpose(myArray())
End Function
这是您生成输出和结果的方式：
This is how you produce the output and results:
首先，您需要创建两个模块（将一个函数插入到每个模块中，将代码粘贴到相应的模块中）。要运行WriteUnder255Cells（），请在工作表中选择4行x 1列的区域（返回模块的位置），然后在公式栏中键入= WriteUnder255Cells（）（不要输入引号）。注意，这些被称为数组公式，所以，而不是打（输入）来创建输出，你需要点击（控制+ shift + enter）。对WriteOver255Cells（）重复相同的过程以产生错误。
First you need to create the two modules(to insert one function into each module, paste the code from one into the respective module). To run "WriteUnder255Cells()", select an area of 4 rows x 1 column on the sheet (this where you return the module) and type "=WriteUnder255Cells()" into the formula bar (do not enter the quotes). Note these are called like array formulas, so instead of hitting (enter) to create the output, you need to hit (control + shift + enter). Repeat the same process for WriteOver255Cells() to produce the errors.
以下是一些解决问题的文档/论坛讨论。解决方案似乎过于具体或笨重，因为它们引起子程序（我想避免）：
Here are some documents/forums discussions which address it. The solutions seem to be either overly specific or clunky because they evoke subroutines (which I want to avoid):
https://support.microsoft.com/en-us/kb/213841
http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/
https://forums.techguy.org/threads/solved-vba-access-to-excel-255-char-limit-issue.996495/
推荐答案
这对我有用：
Function Over255()
Dim myArray(3) As String '<<<<< not variant
myArray(0) = String(300, "a")
myArray(1) = String(300, "b")
myArray(2) = String(300, "c")
myArray(3) = String(300, "d")
'Over255 = Application.Transpose(myArray())
Over255 = TR(myArray)
End Function
'like Application.Transpose...
Function TR(arrIn) As String()
Dim arrOut() As String, r As Long, ln As Long, i As Long
ln = (UBound(arrIn) - LBound(arrIn)) + 1
ReDim arrOut(1 To ln, 1 To 1)
i = 1
For r = LBound(arrIn) To UBound(arrIn)
arrOut(i, 1) = arrIn(r)
i = i + 1
Next r
TR = arrOut
End Function
似乎像你需要返回一个字符串数组和 Application.Transpose 不这样做
Seems like you need to return a string array and Application.Transpose doesn't do that
这篇关于Excel VBA：当将Variant数组返回到所选范围时，需要解决255转移字符限制问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
