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
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
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.
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
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
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
.
Hi Rajesh,
ReplyDeleteThis is exellent, i got the Array concept now.
Will contact you again in case of any issue.
Thanks so much
Hi Friend
ReplyDelete=IF(sheet2!$D88=COLUMN(F:F),sheet2!$E88,0)
in above formula what if the meaning of COLUMN(F:F) reference.
Concider sheet2!$D88 has value 6
Formula COLUMN(F:F) will return value 6 as its 6th column in excel, this is a indirect way to compare whether sheet2!D88 has value 6.
DeleteThis formula will show value of sheet2!D88 if it is 6 else 0.