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.......

Software Process Automation Solution

Individual, small firm  or a corporate tries to increase revenue by various ways as well as they also try to minimize their expenses here automation solution come into the picture.When we speak about automation solution is not just to adopt new technology. We can have following benefits from automation:

• Cost Saving 
• Accuracy in work
• No space for human error
• Save lot of valuable time

We had automated entire data entry process which saved 40 FTE (Full Time Employees) cost for an SEO firm.

Automation was related to web search, data integration into MS-Excel and MS-Word and finish data into database.

Most of the firms, individual are not aware of that the process they are following/working can be automated in such a way that they would save lot of money and valuable time with 100% accuracy. Some of firm don't go for automation solution due to lack of awareness about automation providing firms.

Small scale firms can't afford cost of software solution firms in such scenario they can go for freelancer from whom they get work done in short period of time with affordable prices. Even some firm do not trust providing projects online they can surf internet and they will find many websites where they can meet people face to face and get their work done.

We have automated  many different process, reports and lengthy work using appropriate softwares. In most of the cases I have observe the client was not aware the process can be automated we identified automation scope and provided full scale automated process.

Firm or individual who are not sure if they can have automated process to their work can speak with Software solution provider team. 

You can refer below some links/contacts as per your automation needs:

Group of service providers (Freelancer):- 

Name:- Ultimate automation solution Team

Description:- We provide process automation, We can visit your location base out of Mumbai.

Contact :- 9619923989- Rajesh

Email : - ultimateAutomationServices@gmail.com

Name : - Freelancer Web Portal

Freelancing Website:-

http://www.freelancer.com/u/rajkavikumar.html

Other:

Other Links

I would like to hear from you reader please provide your comments: