CODEWITHSUNDEEP
vbaexcel
Gerald Gonzales
Gerald Gonzales

Reputation: 533

Excel VBA loop and get value from an array

I have the following code to retrieve a selection then making it a array of string.

Dim strArgument As Variant
Dim irange As Range
Dim ricosString As Variant

Set irange = Selection
ricosString = RangeToStringArray(irange)

Dim vArray As Variant
For i = LBound(ricosString) To UBound(ricosString)
    Set vArray = ricosString(i)

My problem here is on the ricosString(i). It is throwing an error Subscript out of range. Any ideas why?

Here is the code for RangeToStringArray

Public Function RangeToStringArray(theRange As Excel.Range) As String()

Dim variantValues As Variant
variantValues = theRange.Value

Dim stringValues() As String
ReDim stringValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2))

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

RangeToStringArray = stringValues

End Function

Upvotes: 1

Views: 13013

Answers (3)

gizlmo
gizlmo

Reputation: 1922

Define your ricosString as a proper String Array:

Dim ricosString() As String

Replace your RangeToStringArray Function with a proper one:

Public Function RangeToStringArray(theRange As Excel.Range) As String()

Dim cell As Range
Dim values() As String
Dim i As Integer

i = 0

ReDim values(theRange.Cells.Count)

For Each cell In theRange

    values(i) = cell.Value
    i = i + 1
Next cell

RangeToStringArray = values
End Function

Then you can refer to the values in the array like this:

vArray = ricosString(i) 'without Set

Upvotes: 0

iDevlop
iDevlop

Reputation: 25272

Regarding that RangeToStringArray function, I don't really see its interest: why don't you just use ricosString = irange, which would be simpler and faster?

Upvotes: 0

Charles Williams
Charles Williams

Reputation: 23550

RangeToStringArray is 2 dimensional but you reference it as 1 dimensional Set vArray = ricosString(i)

Also Ricostring is not an object so you should not use Set

Upvotes: 1

Related Questions