Excel VBA：第一步将范围转换为字符串数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6773232/
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
Excel VBA: Range to String Array in 1 step
提问by aevanko
I know you can easily take a range of cells and slap them into a Variant Array but I want to work with a string array (because it's single-dimensional and takes less memory than a Variant array).
我知道您可以轻松地将一系列单元格放入 Variant Array 中，但我想使用字符串数组（因为它是一维的并且比 Variant 数组占用的内存少）。
Is there any way to automatically convert a range into a string array?
有没有办法自动将范围转换为字符串数组？
Right now I am using a function that will take the range and save the values in a variant array, then convert the variant array to a string array. It works nice , but I'm looking for a way to go directly from the range to string array. Any help would be greatly appreciated.
现在我正在使用一个函数，该函数将获取范围并将值保存在变体数组中，然后将变体数组转换为字符串数组。它很好用，但我正在寻找一种直接从范围到字符串数组的方法。任何帮助将不胜感激。
Function RangeToArray(ByVal my_range As Range) As String()
Dim vArray As Variant
Dim sArray() As String
Dim i As Long
vArray = my_range.Value
ReDim sArray(1 To UBound(vArray))
For i = 1 To UBound(vArray)
sArray(i) = vArray(i, 1)
Next
RangeToArray = sArray()
End Function
UPDATE: It's looking like there is no way to skip the step of throwing the data into a variable array first before converting it to a single-dimensional string array. A shame if it's true (even if it doesn't take much effort, I like to ultra-optimize so I was hoping there was a way to skip that step). I'll close the question in a few days if no solution presents itself. Thanks for the helpful comments, guys!
更新：看起来在将数据转换为一维字符串数组之前，无法跳过先将数据放入变量数组的步骤。如果这是真的，那就太遗憾了（即使不需要太多努力，我喜欢超优化，所以我希望有一种方法可以跳过这一步）。如果没有解决方案，我将在几天内结束这个问题。感谢您的有益评论，伙计们！
UPDATE2: Answer goes to Simon who put in great effort (so did everyone else) and utlimately pointed out it's indeed impossible to go from range to string array in one shot. Thanks, everyone.
更新 2：答案交给西蒙，他付出了巨大的努力（其他人也是如此）并最终指出，一次射击确实不可能从范围到字符串阵列。谢谢大家。
采纳答案by Simon Cowen
How about...
怎么样...
Public Function RangeToStringArray(theRange As Excel.Range) As String()
' Get values into a variant array
Dim variantValues As Variant
variantValues = theRange.Value
' Set up a string array for them
Dim stringValues() As String
ReDim stringValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2))
' Put them in there!
Dim columnCounter As Long, rowCounter As Long
For rowCounter = UBound(variantValues, 1) To 1 Step -1
For columnCounter = UBound(variantValues, 2) To 1 Step -1
stringValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter))
Next columnCounter
Next rowCounter
' Return the string array
RangeToStringArray = stringValues
End Function
回答by Tragamor
You actually can go directly from a range to an array using the functions Split, Join and a delimiter not in the text.
您实际上可以使用函数 Split、Join 和文本中没有的分隔符直接从范围转到数组。
Assuming you have already assigned a 1D range of values as SrcRange
假设您已经将一维值范围指定为 SrcRange
Dim Array() As String: Array = Split(Join(Application.Transpose(SrcRange), "#"), "#")
回答by J.A. Daling
Function RangeToStringArray(myRange as range) as String()
ReDim strArray(myRange.Cells.Count - 1) As String
Dim idx As Long
Dim c As Range
For Each c In myRange
strArray(idx) = c.Text
idx = idx + 1
Next c
RangeToStringArray = strArray
End Function
回答by Shawn Pauliszyn
If you don't mind altering the contents of the clipboard then:
如果您不介意更改剪贴板的内容，则：
COPY the range to the clipboard with the Copy method:
MyTargetRange.CopyCopy the contents from the clipboard to a string variable (search this site or elsewhere for functions to transfer strings to/from the clipboard).
SPLIT the string into a variant array:
MyStringArray = Split(MyClipboardText, vbCrLf)OPTIONAL: The array will have one additional blank element because there is always an additional Return (vbCrLf) at the end of the text you just copied to the clipboard. To remove simply resize the array:
Redim Preserve MyStringArray(Ubound(MyStringArray) - 1)
使用 Copy 方法将范围复制到剪贴板：
MyTargetRange.Copy将剪贴板中的内容复制到字符串变量（在此站点或其他地方搜索将字符串传入/传出剪贴板的函数）。
将字符串拆分为一个变体数组：
MyStringArray = Split(MyClipboardText, vbCrLf)可选：该数组将有一个额外的空白元素，因为在您刚刚复制到剪贴板的文本末尾总是有一个额外的 Return (vbCrLf)。要删除只需调整数组大小：
Redim Preserve MyStringArray(Ubound(MyStringArray) - 1)
Very simple and quick!!!
非常简单快捷！！！
Drawbacks are that the clipboard may change when you least expect it (during a recalculation) and that it only produces arrays of strings (not Doubles or other numerical value types).
缺点是剪贴板可能会在您最不期望的时候发生变化（在重新计算期间），并且它只生成字符串数组（不是双精度型或其他数值类型）。
This would be EXTREMELY HELPFUL if you are working with lots of repetitive functions (thousands) that use the same data (thousands of data points). The first time your function is called, do all the intermediate calculations on the ranges of data that you need but save the results in static variables. Also save a string copy of your input ranges via the clipboard. With each subsequent call to your function, convert the input ranges to text, again via the clipboard, and compare with the saved copy. If they are the same you may be able to bypass allot of your preliminary calculations.
如果您正在处理使用相同数据（数千个数据点）的大量重复函数（数千个），这将非常有用。第一次调用您的函数时，对您需要的数据范围进行所有中间计算，但将结果保存在静态变量中。还可以通过剪贴板保存输入范围的字符串副本。每次后续调用您的函数时，再次通过剪贴板将输入范围转换为文本，并与保存的副本进行比较。如果它们相同，您可以绕过分配的初步计算。
回答by tim goodwin
Named ranges used in VBA are already arrays. So first make the range into a named range, then refer to it and delete the named range. For example:
VBA 中使用的命名范围已经是数组。所以先把范围变成命名范围，然后引用并删除命名范围。例如：
ThisWorkbook.Names.Add Name:="test_array", RefersTo:=Sheet1.Range("A4:B10")
a = Sheet1.Range("test_array")
ThisWorkbook.Names("test_array").Delete
