Saturday, 1 December 2012

How to Resize an Array With Redim Statement in VBA Arrarys

VBA Arrarys : Resize an Array With Redim Statement
The ReDim statement is used to size or resize a dynamic array that has already been formally declared.

For example, if you have already declared an array with an Index value of 5 and decided to change the number of Index to 6, you can do as shown in below example Macro :
Sub Redim_My_Array( )
Dim Arr(5) 

As you declared an array with index size 5 , you can store the values as follows :
Arr(0) = "Months"
Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Msgbox Arr(0) & "-" Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5)

At any point of time if you want to resize your , that means if you want to increase index size , you can use Redim statement as follows.

Redim Arr(6)

Arr(0) = "Months"
Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”
Arr(6) = “Jun”

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

Note :
Please note that when you use Redim() statement in between of the Macro , already stored values in Array will be lost , you need to restore again.
To avoid this problem , you need use 'Redim Preserve' Keyword.

Please go through the below link for better understanding :


Thanks,
TAMATAM



No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts