Excel中VBA：范围为String数组中的1步 [英] Excel VBA: Range to String Array in 1 step

问题描述

我知道你可以很容易地采取一系列的细胞，并将它们一巴掌成变量数组，但我想用一个字符串数组来工作（因为它是单维的，并且需要较少的内存比一个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?

现在我使用的，将采取的范围，并保存在一个变量数组中的值，然后将变长数组转换为字符串数组的函数。它的工作原理不错，但我在寻找一种方式直接从范围到字符串数组中去。任何帮助将大大AP preciated。

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 

更新：
它看起来像没有办法首先将它转换成一维字符串数组之前跳过数据掷入变量数组的步骤。一种耻辱，如果这是真的（即使它并不需要很多的努力，我喜欢超优化，所以我希望有一种方式来跳过这一步）。如果无解presents本身我将关闭在几天的问题。感谢您的有益的意见，伙计们！

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：
答案去西门放谁在努力（所以没有其他人），并utlimately指出，这是不可能确实从范围到字符串数组中的一个镜头。谢谢大家。

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：范围为String数组中的1步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！