Excel VBA:1 步将范围转换为字符串数组 [英] Excel VBA: Range to String Array in 1 step

问题描述

我知道您可以轻松地将一系列单元格放入 Variant 数组中，但我想使用字符串数组(因为它是一维的并且比 Variant 数组占用的内存更少).

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).

有什么办法可以自动将范围转换为字符串数组?

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!

更新2:答案交给了西蒙，他付出了巨大的努力(其他人也是如此)，并最终指出一次射击确实不可能从射程到弦阵.谢谢大家.

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.

推荐答案

怎么样...

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

这篇关于Excel VBA:1 步将范围转换为字符串数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！