How to use dynamic array in VBA / macro

Sorry Pravin to keeping your query waiting. You asked me a query about how to use dynamic array in VBA but in my last post I explained about basic use of array but my blog is for all the readers ;-).

So here we go, We get into such scenario where we actually doesn't know what length of array we would need in our program and here dynamic array helps you. Lets check a example program:

Ex1:

Sub MyDynArray ()

Dim MyArr() as integer, iArrLenghth as Integer, iCntr as Integer

iArrLenghth =  Sheet1.Range("A1").End(xlDown).Row

Redim Preserve MyArr( iArrLenghth-1)

For iCntr = 0 To iArrLenghth
   MyArr(iCntr) = Sheet1.Range("A" & iCntr+1)
Next iCntr

For iCntr = 0 To Ubound(MyArr)-1
   If MyArr(iCntr) = 5 Then
        MsgBox "Number 5 found"
        Exit For
   End If
Next iCntr

End Sub

In example code is storing all numbers in column A of sheet1 to an Array variable and then searches in array for number 5 and throws message if number 5 found and ends.

In example procedure at first line I have declared dynamic array with other variables

Dim MyArr() as integer, iArrLenghth as Integer, iCntr as Integer

while declaring dynamic array we doesn't specify its length.

Next line I get count of rows having data in column A of sheet1 into variable.

iArrLenghth =  Sheet1.Range("A1").End(xlDown).Row

At next line we set length of array variable at runtime so if column A has 5 rows of data then Array's length would be 0 to 4.

Redim Preserve MyArr( iArrLenghth-1)

Here we can also use only Redim no need to use Preserve keyword.

Preserve is required only if we have data stored in array and we do not want to loose it.
If our array is having some data stored and if we do not use Preserve keyword as shown below then all stored data will wipe out.

Redim MyArr( iArrLenghth-1)

Next I have use a for loop to store value from column A of sheet1 to array variable

For iCntr = 0 To iArrLenghth
   MyArr(iCntr) = Sheet1.Range("A" & iCntr+1)
Next iCntr

After that I have used another fir loop which I am using to loop through all elements of array variable and to find if any element is having value as 5 and if found then exit loop.

For iCntr = 0 To Ubound(MyArr)-1
   If MyArr(iCntr) = 5 Then
        MsgBox "Number 5 found"
        Exit For
   End If
Next iCntr

Here I have used inbuilt function UBound() which gives length if array. and I have used Exit For which get use to terminate For loop.

So here are some basics which will help you to manipulate  data in array and access to flexible array length.

Let me know in case of any queries.

Thanks

.

How to use array in VBA (macro)

Thanks to my friend Pravin who asked me about his array query and lead me to write this post. So basically, an array is bunch of a same kind of variables stored under one variable. You should know what is variable and their types before getting into array.

So an array can have any store number of depends on RAM size of your machine.
So enough theory just check an example below:

Ex1 :

Sub MyFirstArray ()

Dim MyArray (2) as Integer

MyArray (0) = 21
MyArray (1) = 30

Msgbox " First array value = " & MyArray (0)
Msgbox " Second array value = " & MyArray (1)

End Sub

You can see first line in MyFirstArray procedure is declaration of array means you told program about the length of array you would gonna use. Array always start with 0 by default, thus in the second line I have use.

MyArray (0) = 21

Meaning first place of my array which is having an index of 0,  will store value 21.

similarly second place of array that is last place, which is having an index of 1 will a store value 30.

This is how we can assign a value to an array.

Now how to get or access value from array so in 4th and 5th line of procedure we have show stored values in message box.

So this is about basic you should know to start with array.
So please try with other types of variables and get back to me in case of and query.

But my friend Pravin's actual query was how to use dynamic array is in my next post.......