CODEWITHSUNDEEP
arraysvbaexcel
decemberrobot
decemberrobot

Reputation: 531

Subscript out of range when appending items to array in excel vba

I have an array in excel vba and I would like to append 3 items to the end of the array but i got a subscript out of range error whenever i run my code.

First, I split the "rtv" array and I want to append "class", "age" and "address to the end of the "rtv" array but i receive an error.

My code looks like this:

rtv = Split(Application.WorksheetFunction.VLookup(Name,Sheets("Properties").Range("B1:C7"), 2, False), ",") 
    rtv(UBound(rtv)) = "Class"
    rtv(UBound(rtv) + 1) = "Age"
    rtv(UBound(rtv) + 2) = "Address"

    For i = LBound(rtv) To UBound(rtv)
        rtv(i) = Chr(34) & rtv(i) & Chr(34) & ":" & Chr(34) & Chr(34) & ";"
    Next i

And this is how I declare my array:

Dim rtv() As String
Dim i As Long

Anyone knows where the error lies ?

Upvotes: 2

Views: 445

Answers (1)

Axel Richter
Axel Richter

Reputation: 61890

In VBA the size of an array cannot be increased simply by adding elements to it. The array must be ReDimed. If the contents needs to pe preserved while ReDim, then ReDim Preservemust be used.

Example (simplified Application.WorksheetFunction.VLookup(Name,Sheets("Properties").Range("B1:C7"), 2, False) by using a string)

Sub test()

 Dim rtv() As String

 sVLookupResult = "a,b,c"

 rtv = Split(sVLookupResult, ",")

 lrtvLength = UBound(rtv)

 ReDim Preserve rtv(lrtvLength + 3)

 rtv(lrtvLength + 1) = "Class"
 rtv(lrtvLength + 2) = "Age"
 rtv(lrtvLength + 3) = "Address"

 For i = LBound(rtv) To UBound(rtv)
  rtv(i) = Chr(34) & rtv(i) & Chr(34) & ":" & Chr(34) & Chr(34) & ";"
 Next i

 MsgBox Join(rtv)

End Sub

Upvotes: 1

Related Questions