Saturday, 1 December 2012

How to use Redim Preserve keyword in VBA Arrarys

How to Declare a Dynamic Array with Redim Preserve in VBA Macros

The Redim Statement is used to resize an array. When we resize an Array it will erase the elements already stored in it. In the Example-I, all the values assigned prior to resize the array are erased. Only the value assigned to the array after resize remains. 


To keep/preserve already stored values we need to use the 'Redim Preserve' keyword as explained in Example-II.
---------------------------------------------------------------------------------------
Example-I :Resizing an Arrary with 'Redim ' keyword :
---------------------------------------------------------------------------------------
Option Base 1         ------ ' Allows the Arrary index starts from '1' otherwise by default from '0'
Sub Redim_Array( )
Redim Arr(5)

Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Redim Arr(6)

Arr(6) = “Jun”

Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)
End Sub


---------------------------------------------------------------------------------------
Example-II : Resizing an Arrary with 'Redim Preserve' keyword :
--------------------------------------------------------------------------------------- By replace the Redim Arr(6) with Redim Preserve Arr(6), all values will remain. 

Option Base 1
Sub Redim_Preserve_Array( )
Redim Arr(5)

Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Redim Preserve Arr(6)

Arr(6) = “Jun”

Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)
End Sub





To Clear or Erase an Array :
You can clear or erase an array with Erase keyword, which will clears all index values in an Array.

Erase Arr()


Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts