Menu
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!
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.
808
Steps to Add a Range into an Array in VBA First, you need to declare a dynamic array using the variant data type. Next, you need to declare one more variable to store the count of the cells from the range and use that counter for the loop as well. After that, assign the range where you have value to the array.
First, declare an array without the element count that you want to store in there. Next, you need to use VBA's ARRAY function to define the value that you want to specify. After that, specify all the strings using a comma into the function. In the end, you can access all the strings using the element number.
Declare a dynamic array By declaring a dynamic array, you can size the array while the code is running. Use a Static, Dim, Private, or Public statement to declare an array, leaving the parentheses empty, as shown in the following example. Use the ReDim statement to declare an array implicitly within a procedure.
You actually can go directly from a range to an array using the functions Split, Join and a delimiter not in the text.
Assuming you have already assigned a 1D range of values as SrcRange
Dim Array() As String: Array = Split(Join(Application.Transpose(SrcRange), "#"), "#") 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
