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 - stackoverflow.com/questions/2916009/what-does-redim-preserve-do — Vityata
– Vityata, Commented May 9, 2017 at 9: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
– Tom, Commented May 9, 2017 at 9:50

YowE3K · Accepted Answer · 2017-05-09 09:04:06Z

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

Community · Accepted Answer · 2017-05-23 12:02:43Z

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, 2017 at 12:02

CommunityBot

11 silver badge

answered May 9, 2017 at 9:12

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

1 Comment

Michal Over a year ago

My integer is value from 1 to max 2500

Collectives™ on Stack Overflow

How to set MyArray dynamic in Excel VBA?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related