Passing values from dynamic range to dynamic array variable

In Microsoft Excel VBA, often we want to pass the range to array. Mentioned below code is simple example. The post will help you in understanding how to declare dynamic array as well. The dynamic array is one where you provide end user to input any number of values. 

Function CheckArray(Target As Range) As String

‘The code will will declare dynamic array

Dim FirstArray() As Variant

Dim cnt As Integer

Dim str As String

cnt = Target.Count

‘The code will fix the range of array based on number

‘of items available in target which is range

ReDim FirstArray(1 To cnt)

i = 1

‘The code below will move the range into array.

‘Also, it combines all the values in range and return them

‘seperated by space like concatenate

Do While i <= cnt

FirstArray(i) = Target.Item(i)

    str = str & " " & FirstArray(i)

    i = i + 1

Loop

CheckArray = str

End Function

I am sure this post has given you clear idea passing range into array variable. Also, you can visit the Microsoft page below which describe array in details. Our aim is to help the blog visitors with practical use. 

http://msdn.microsoft.com/en-us/library/aa164778(office.10).aspx

So far I am attempting to give you basics of VBA. Kindly post your comments.

Function CheckArray(Target As Range) As String

‘The code will will declare dynamic array

Dim FirstArray() As Variant

Dim cnt As Integer

Dim str As String

cnt = Target.Count

‘The code will fix the range of array based on number

‘of items available in target which is range

ReDim FirstArray(1 To cnt)

i = 1

‘The code below will move the range into array.

‘Also, it combines all the values in range and return them

‘seperated by space like concatenate

Do While i <= cnt

FirstArray(i) = Target.Item(i)

    str = str & " " & FirstArray(i)

    i = i + 1

Loop

CheckArray = str

End Function

I am sure this post has given you clear idea passing range into array variable. Also, you can visit the Microsoft page below which describe array in details. Our aim is to help the blog visitors with practical use. 

http://msdn.microsoft.com/en-us/library/aa164778(office.10).aspx

So far I am attempting to give you basics of VBA. Kindly post your comments.