Excel VBA loop and get value from an array

Question

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

Charles Williams · Accepted Answer · 2016-05-09 09:55:36Z

1

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

answered May 9, 2016 at 9:55

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patrick Honorez · Accepted Answer · 2016-05-09 10:00:26Z

0

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?

answered May 9, 2016 at 10:00

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Comments

gizlmo · Accepted Answer · 2016-05-09 10:22:27Z

0

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

edited May 9, 2016 at 10:22

answered May 9, 2016 at 10:05

gizlmo

1,9221 gold badge16 silver badges14 bronze badges

4 Comments

Gerald Gonzales Over a year ago

When I changed the RangeToStringArray function, it is erroring on the line For i = LBound(ricosString) To UBound(ricosString)

Gerald Gonzales Over a year ago

Subscript out of range.

Gerald Gonzales Over a year ago

How are you looping on the array ricosString?

gizlmo Over a year ago

For i = LBound(ricosString) To UBound(ricosString) Step 1 | i is defined as an Integer

Collectives™ on Stack Overflow

Excel VBA loop and get value from an array

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related