Convert Variant Array to String Array

[ramankumar]

Hi
I have variant array. IS there any way how to convert this to String array.

 

[Mark O'Brien]

The best (only?) way is going to be to create a separate string array and populate it with the values in the variant array e.g.:

<pre>
Public Sub ArrayExample()

Dim vMyArray() As Variant
Dim sMyArray() As String
Dim i As Integer

ReDim vMyArray(5) As Variant
vMyArray(0) = "Mark"
vMyArray(1) = "1"
vMyArray(2) = "two"
vMyArray(3) = "Blargh"
vMyArray(4) = "4"

ReDim sMyArray(UBound(vMyArray)) As String

For i = 1 To UBound(vMyArray)
sMyArray(i) = CStr(vMyArray(i))
Next

MsgBox vMyArray(1)
End Sub</pre>

HTH

 

[ramankumar]

Thank you sir.

I am looking for a function which will do this for me. Is there any function in VBA?

 

[Andrew Poulsom]

Why do you need to do it? You can coerce eg a value to a string using the CStr function. There are lots more - search Help for "Type Conversion Functions".

 

[tusharm]

On 2002-12-18 14:16, ramankumar wrote:
Thank you sir.

I am looking for a function which will do this for me. Is there any function in VBA?

Take Mark's sub and convert it into a function! Should take about 30 seconds.

 

[grays]

I tired to apply this concept/code but am getting a subscript out of range error.

Sub formula()
Dim fArray() As Variant
Dim sArray() As String
Dim lr As Long
Dim LRow As Long
With Sheet1
    LRow = .Cells(4, 2).End(xlDown).Row
    fArray = .Range(.Cells(4, 1), .Cells(LRow, 2))
    ReDim sArray(UBound(fArray)) As String
    For lr = 1 To UBound(fArray)
        sArray(lr) = CStr(fArray(lr))
    Next lr
Sheet2.Range(Cells(LRow + 7, 2), Cells(LRow + LRow, 4)) = sArray
End With
End Sub

not sure if I should submit this question to this old thread or start a new one?

thanks

 

[Rick Rothstein]

I tired to apply this concept/code but am getting a subscript out of range error.

Sub formula()
Dim fArray() As Variant
Dim sArray() As String
Dim lr As Long
Dim LRow As Long
With Sheet1
    LRow = .Cells(4, 2).End(xlDown).Row
    fArray = .Range(.Cells(4, 1), .Cells(LRow, 2))
    ReDim sArray(UBound(fArray)) As String
    For lr = 1 To UBound(fArray)
        sArray(lr) = CStr(fArray(lr))
    Next lr
Sheet2.Range(Cells(LRow + 7, 2), Cells(LRow + LRow, 4)) = sArray
End With
End Sub

not sure if I should submit this question to this old thread or start a new one?

thanks

It looks like you are copying a range into a Variant (which makes it an array), then copying those values into a String array only to put that String array into another range somewhere else... why? Forget the array stuff and just tell us what you are trying to do... what does your original data look like and what do you want the "converted" data to look like in the other range? Show us examples of each (using the same underlying data for both).

 

[grays]

yes. that is what I'm trying to do. the range on sheet1 is data that is input by the user. I use this range of data for various reasons across multiple sheets.
for this particular purpose, I need the 2 columns in the sheet1.range to concatenate with say "abc" for one sheet and "jkl" on another sheet. I was first trying to figure out how to best concatenate the original range first and was going to worry about the other portion of the concatenate later. this particular workbook has large ranges of data (over 500k lines on say sheet8) so I use arrays as I thought they processed quicker in VBA. Also the array size determines the range size on other sheets.

sheet1
column B Column C
1 a
2 b
3 c

sheet2 column A (at the appropriate row) to read
abc1a
abc2b
abc3c

sheet3 column A (at the appropriate row) to read
jkl1a
jkl2b
jkl3c

thanks