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 the 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.
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 the 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
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
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.