redim preserve problem

[MrFancyteeth]

I'm having problems with ReDim Preserve

If I run the following code without 'preserve' it runs fine
(it's only a test program at the moment - as i'm learning VBA)

Option Base 1

Private Sub UserForm_Click()
Dim nCount As Integer
Dim arrTest() As Integer

For nCount = 1 To 20

ReDim arrTest(nCount, 2)
arrTest(nCount, 1) = nCount
arrTest(nCount, 2) = nCount * nCount

Next nCount

End Sub

Obviously as the array redims - all the previous data is lost, but when i add ReDim PRESERVE arrTest(nCount, 2) - I get the subscript out of range error.

I could get around the problem by initially Dimensioning the array to the maximum size but i'm keen to know how to redim while maintaining the contents.

mrF

 

[TonyJollans]

Hi MrFancyteeth,

If you use Preserve you can
(a) not change the number of dimensions, and ..
(b) only Redim the last array dimension

In your simple case you must reverse your dimension order and ensure your array has two dimensions to start with, the first being the size you want ..

 Redim arrTest(2,0)

 For nCount = 1 To 20

  ReDim arrTest(2, nCount)
  arrTest(1, nCount) = nCount
  arrTest(2, nCount) = nCount * nCount

 Next nCount

Enjoy,
Tony

 

[MrFancyteeth]

Thanks -

Understanding it now

mrF