Debugging VBA code - appending values to array

Question

I am trying to use code of the following form to populate an array of x rows:

Dim myarray() As Variant
Dim string1 As String

Dim myarray_ubound As Integer
myarray_ubound = 0

For i = 1 to x

  myarray_ubound = myarray_ubound + 1
  ReDim Preserve myarray(1 To myarray_ubound, 1 To 2)

  myarray(myarray_ubound,1) = i
  myarray(myarray_ubound,2) = string1

Next i

However, when I run it it gets stuck after the first loop, telling me that a subscript is out of range. Debugging takes me to the ReDim command.

I used myarray_ubound as an alternative to calling the UBound function many times, but I have tried using this as well and I get the same error.

Can anyone spot what's gone wrong?

decide if you want a one dimensional array or two

user2140173
– user2140173

2014-08-04 09:23:24 +00:00
Commented Aug 4, 2014 at 9:23 — user2140173
– user2140173, Commented Aug 4, 2014 at 9:23
I want two ... is this not?

ajor
– ajor

2014-08-04 09:23:51 +00:00
Commented Aug 4, 2014 at 9:23 — ajor
– ajor, Commented Aug 4, 2014 at 9:23
this can help.

Ioannis
– Ioannis

2014-08-04 09:26:52 +00:00
Commented Aug 4, 2014 at 9:26 — Ioannis
– Ioannis, Commented Aug 4, 2014 at 9:26

Peter Krassoi · Accepted Answer · 2014-08-04 09:35:30Z

3

See: http://msdn.microsoft.com/en-us/library/aa266231.aspx "If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all."

Try

ReDim Preserve myarray(1 To 2, 1 To myarray_ubound)

instead.

answered Aug 4, 2014 at 9:35

Peter Krassoi

5673 silver badges11 bronze badges

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

Comments

InContext · Accepted Answer · 2014-08-04 09:26:25Z

3

you can only redim the last element of the array (not the first), see below. As you know x I would suggest to redim your array with this value from the beginning.

Dim myarray() As Variant
Dim string1 As String

Dim myarray_ubound As Integer
myarray_ubound = 0

For i = 1 To 10

  myarray_ubound = myarray_ubound + 1
  ReDim Preserve myarray(1 To 2, 1 To myarray_ubound)

  myarray(1, myarray_ubound) = i
  myarray(2, myarray_ubound) = string1

Next i

answered Aug 4, 2014 at 9:26

InContext

2,52116 silver badges25 bronze badges

Comments

Olle · Accepted Answer · 2014-08-04 10:41:58Z

1

Since you are declaring the variable as a Variant i don't see why you need to re-dim. (maybe related to memory issues, feel free to fill in).

I would suggest:

For i = 1 to 10

myArray(1, i-1) = i
myArray(2, i-1) = string1

next i

answered Aug 4, 2014 at 10:41

Olle

291 silver badge7 bronze badges

1 Comment

ajor Over a year ago

That's a fair comment: the reason is that this is part of a loop so x varies. Thanks for your help.

Collectives™ on Stack Overflow

Debugging VBA code - appending values to array

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related