Saturday, December 1, 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 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

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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog