Out of Range error in Excel VBA when trying to access array element that doesn't exist

Question

I have a very basic problem.

Context: 1) I have a column where each cell may or may not contain a list of comma separated entries. 2) I loop through each row and split each cell by a comma and feed this into an array. 3) I then write the value for each element into the array into a new cell in a different spreadsheet.

When I add array(0), my formula works because there is always one entry (although it's not always comma separated with additional entries).

When I add array(1), my formula conditionally works because there are some instances where it would have a value from splitting the cell by a comma and accessing the second entry.

However, many times array(1) doesn't have a value and I get an subscript out of range error.

How can I conditionally check when the array element I'm accessing doesn't have a value to avoid this?

I currently have something like:

Workbook.Worksheets("name").Cells(x,y).Value = array(1)

I want to do something like:

If (IsEmpty(array(1))) Then
    Workbook.Worksheets("name").Cells(x,y).Value = ""
Else 
    Workbook.Worksheets("name").Cells(x,y).Value = array(1)

But this also threw a subscript error. Any advice?

If the array is empty, then array(1) will (expectedly) throw this error. You need to check whether the array has elements and/or has been initialized. In most cases, Ubound(array) will give you give you the maximum index. There are some edge cases IIRC that require some extraordinary workaround functions to handle. — David Zemens
– David Zemens, Commented Oct 17, 2019 at 14:58
Looking at the Ubound worked!! Thank you!! :) Unrelated question: Can I promote a note to an answer...? — Kamui
– Kamui, Commented Oct 17, 2019 at 15:04
No, but you can self-answer if you prefer or prompt someone to write an answer :) — BigBen
– BigBen, Commented Oct 17, 2019 at 15:05

BigBen · Accepted Answer · 2019-10-17 15:10:08Z

2

To check the number of elements in an array, use UBound, e.g.

If UBound(array) > 0 Then
    ... access array(1)
Else
    ... write a blank string.
End If

answered Oct 17, 2019 at 15:10

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

Out of Range error in Excel VBA when trying to access array element that doesn't exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related