How to set MyArray dynamic in Excel VBA?

Question

I am trying to find a way to get this like

Dim MyArray(1 To 1893) As Integer

work dynamic currently I am unable to do so and always need to type number which in fact is max(TAS_ID) Any help will be appreciated, I can not find a way to define array from 1 to n.. or find other method to achieve same effect.

Sub Moving_Data()

Dim i, j, LastRow, tempID As Integer
Dim TAS_ID As Integer
Dim k As Boolean

LastRow = Cells(Rows.Count, 4).End(xlUp).Row 'last row
   For i = 1 To LastRow
       Cells(i, 1) = i
    Next i

TAS_ID = 1
i = 2
k = True

Dim MyArray(1 To 1893) As Integer ' add max zone number!
'Dim MyArray(1 To max(TAS_ID)) As Integer ??????

Do While k = True
      Do While Cells(i + 1, 2) = ""
         If i > LastRow Then
            Exit Do
         End If
         Cells(i, 2) = TAS_ID
         i = i + 1
     Loop
    j = i
    MyArray(TAS_ID) = j - 1
    Cells(2, 14) = j
    TAS_ID = Cells(i + 1, 2)
    If i > LastRow Then
        k = False
        Exit Do
    End If
    j = i + 2
    i = j

Loop

For i = 1 To UBound(MyArray)
    Cells(1 + i, 11).Value = MyArray(i)
    Cells(1 + i, "J") = i
Next i

End Sub

Or `ReDim Preserve` - http://stackoverflow.com/questions/2916009/what-does-redim-preserve-do — Vityata, May 09 '17 at 09:04
`ReDim Preserve` can be quite slow. If you can `Dim` your array in one go and if needs be use `ReDim Preserve` to resize it after your loop so that you only need to do it once. — Tom, May 09 '17 at 09:50

score 1 · Accepted Answer · answered May 09 '17 at 09:04

You need to ReDim your array as necessary:

Sub Moving_Data()

Dim i, j, LastRow, tempID As Integer
Dim TAS_ID As Integer
Dim k As Boolean

LastRow = Cells(Rows.Count, 4).End(xlUp).Row 'last row
   For i = 1 To LastRow
       Cells(i, 1) = i
    Next i

TAS_ID = 1
i = 2
k = True

Dim MyArray() As Integer    
ReDim MyArray(1 To 1)

Do While k = True
      Do While Cells(i + 1, 2) = ""
         If i > LastRow Then
            Exit Do
         End If
         Cells(i, 2) = TAS_ID
         i = i + 1
     Loop
    j = i
    'ReDim the array if necessary
    If TAS_ID > UBound(MyArray) Then
        ReDim Preserve MyArray(1 To TAS_ID)
    End If
    MyArray(TAS_ID) = j - 1
    Cells(2, 14) = j
    TAS_ID = Cells(i + 1, 2)
    If i > LastRow Then
        k = False
        Exit Do
    End If
    j = i + 2
    i = j

Loop

For i = 1 To UBound(MyArray)
    Cells(1 + i, 11).Value = MyArray(i)
    Cells(1 + i, "J") = i
Next i

End Sub

score 0 · Answer 2 · edited May 23 '17 at 12:02

0

Concerning the fact, that you work with Integer, simply initiate the array to the Max integer and do not care to make it larger or smaller - you are not gaining anything:

Option Explicit

Public Sub TestMe()

    Dim myArray(1 To 2 ^ 15 - 1)        As Integer
    Dim lngCounter                      As Long

    For lngCounter = UBound(myArray) To LBound(myArray) Step -100
        Debug.Print lngCounter
    Next lngCounter

End Sub

(Hope you get my sense of humor here --^ ) Long story short, do not use integers at all in VBA - Why Use Integer Instead of Long?

edited May 23 '17 at 12:02

Community

1
1

answered May 09 '17 at 09:12

Vityata

42,633
8
55
100

My integer is value from 1 to max 2500 – Michal May 13 '17 at 19:54

How to set MyArray dynamic in Excel VBA?

2 Answers2